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

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.

You can get most of your code if you use the macroeditor.

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

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.

Please let me see your file, if possible

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

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