Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 21st, 2004, 03:40 PM
Registered User
 
Join Date: Dec 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cells.select

I wrote a VBA macro for Excel, and in a preliminary step I want to copy today's worksheet ("Schedule") into a "Old Data" worksheet and then bring new data into the "Schedule" worksheet. In order to copy the entire worksheet I have:

 Sheets("Schedule").Select
    Cells.Select
    Cells.Copy
    Sheets("Old Data").Select
    Cells.Select
    ActiveSheet.Paste


This used to work. Now I am getting a pop-up window when I step through to the first Cells.Select line. The pop-up simply reads "400", with "OK" and "Help" buttons. "Help" brings up a blank screen.

Can someone explain to me what is happening?
Can someone explain how I can accomplish what I want?
----------------------------
Also, is it possible to select an entire worksheet and then paste it over another worksheet, skipping row 1 (where I have buttons, which can't be overwritten), when the size of the worksheets changes daily?

Much thanks!



mif
 
Old December 21st, 2004, 04:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I dont know why you get the pop-up, your example works for me.

But you dont need the 2 extra lines with Cells.Select as you've already made the selection of the sheets.

If you select an entire sheet, you MUST paste it to an entire sheet.

However, if you only select the range in your Schedule sheet which contains data, you may select the cell A2 in your Old Data sheet and paste from there.
 
Old December 21st, 2004, 04:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can get most of your code if you use the macroeditor.
 
Old December 21st, 2004, 05:43 PM
Registered User
 
Join Date: Dec 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your feedback. I'll try that. In the meantime, I had a 99% working macro that is now failing every which way. Now I am getting a subscript out of range when I try to switch worksheets using:

Sheets("Schedule").Select

Since I am just trying to switch worksheets, what could be going on here?

Thanks!
mif


mif
 
Old December 22nd, 2004, 02:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sounds odd. It could be a corrupt sheet or workbook.
Try to export your code to a new workbook and see if it works. If you haven't made any changes or use another Excel version, it could be the reason.
I haven't had that kind of errors before but I've seen questions about odd behaviour before so try search in that direction.
See if someone else comes up with a solution. I'll look around to see if I can find anything.
 
Old February 2nd, 2005, 01:30 AM
Authorized User
 
Join Date: Jan 2005
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Please let me see your file, if possible

my email address is amjad.s.mahmood@gsk.com

 
Old February 2nd, 2005, 02:50 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mif

Where do you have your code?
It must be in a module, not in the worksheet.
Change Cells.Select -> Range("A1").Select

Sub Archive()
    Sheets("Schedule").Select
    Cells.Select
    Cells.Copy
    Sheets("Old Data").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

-vemaju






Similar Threads
Thread Thread Starter Forum Replies Last Post
Select row in GridView WITHOUT using Select button rao965 ASP.NET 2.0 Professional 1 February 15th, 2008 10:44 AM
adding cells lcan4 Excel VBA 2 September 6th, 2006 06:08 AM
Fill select box and select recordset value markd Classic ASP Databases 1 February 20th, 2006 06:41 PM
Protecting Cells jacks Excel VBA 1 February 20th, 2004 11:55 AM
Cells MattLeek Excel VBA 2 December 12th, 2003 11:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.