recorded macro stops working when data set is smaller than when originally recorded

[ncjefffl]

I've got a macro that I recorded (because I don't know how to write one) that just copies certain columns from one tab into another. It's been working until today, where I get a" subscript out of range" error and it goes to the highlighted section of my code (below). I think the issue is that it's trying to copy data from blank cells. When I recorded it, I just highlighted the columns I wanted copied. I don't know how to tell it to only copy the cells with data, which is think is what I need to do to avoid this. Or it's something else entirely. Can anyone help me with this?

Here is the bit of code that it goes to when I get the error:

ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 1
    [SIZE=4][COLOR=#0000ff]Sheets("Page").Select
[/COLOR][/SIZE]   ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Columns("S:V").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B1").Select
    ActiveSheet.Paste
    Sheets("Page").Select

 

[Norie]

That error wouldn't be caused by blank cells.

Do you have a worksheet called 'Page' in the active workbook when the code is run?

 

[Sal Paradise]

Here are some pointers:

ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 1
    Sheets("Page").Select
   ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Columns("S:V").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B1").Select
    ActiveSheet.Paste
    Sheets("Page").Select

All of these red parts are just scrolling the window, you can delete them. That leaves us with:

    Sheets("Page").Select
    Columns("S:V").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B1").Select
    ActiveSheet.Paste
    Sheets("Page").Select

The first line is selecting the sheet called "Page", if it is giving an error, perhaps it is because you changed the sheet name or deleted it?

The second line is selecting all of columns S:V

The next is explaining how to copy

The fourth copies

The fifth Selects Sheet1

Then it selects B1 in Sheet1

Then it pastes to the active sheet (Sheet1, just selected)

And goes back to sheet page.

You can greatly simplify this.

    Sheets("Page").Columns("S:V").Copy
    Sheets("Sheet1").Range("B1").Paste

This should do the same. All the copying is done without selecting, all the pasting is also done without selecting. Just make sure the sheet names exist.

 

[Michael M]

I've modified the code, BUT, usually "Subscript Out of Range" means you don't have a sheet by that name...
Check to see if thre are extra spaces before / in / after the sheet name.
Also check the sheet name spelling and / or upper lower case.

Sheets("Page").Columns("S:V").Copy Destination:=Sheets("Sheet1").Columns("B:B")
Sheets("Page").Select

 

[ncjefffl]

Thanks, guys. Apparently I'm a bigger moron than I realized. I had inserted a sheet before running the macro (not that I remember doing that!). I just tried it again after reading your posts, from the original untouched workbook, and it works. I am going to take a look at using your code suggestions to tidy it up, though. I appreciate your help!

 

[Michael M]

Welcome to our world...we all have "those" moments...