Subscript out of range

[Abdul]

I have the following before save macro saved, but when I try to run the macro
I get the following error:
Run-time error '9':

Subscript out of range

and my macro looks like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("B1:B20").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\User\Desktop\Book1"
Windows("Book1").Activate
Sheets("Sheet1").Select
Application.Goto Reference:="R1C256"
Selection.End(xlToLeft).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveSheet.Paste
End Sub

when I click on Debug
the line: Windows("Book1").Activate
becomes highlighted with flash yellow with an arrow on the left is visible.

when I try to ignore that step like this:
' Windows("Book1").Activate

the debugger highlights the next line..

if you know what's wrong with it or what should I do to correct it, please
assist.

this macro is supposed to copy the contents highlighted in the second line
and opens the file "book1" from the desktop and paste it at the next unused
cell starting from the end of the sheet [Range("VI1")]

if you know a better way to avoid those steps and use better ones please
provide me with assistance.

 

[RickC]

I have the following before save macro saved, but when I try to run the macro
I get the following error:
Run-time error '9':

Subscript out of range

and my macro looks like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("B1:B20").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\User\Desktop\Book1"
Windows("Book1").Activate
Sheets("Sheet1").Select
Application.Goto Reference:="R1C256"
Selection.End(xlToLeft).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveSheet.Paste
End Sub

when I click on Debug
the line: Windows("Book1").Activate
becomes highlighted with flash yellow with an arrow on the left is visible.

when I try to ignore that step like this:
' Windows("Book1").Activate

the debugger highlights the next line..

if you know what's wrong with it or what should I do to correct it, please
assist.

this macro is supposed to copy the contents highlighted in the second line
and opens the file "book1" from the desktop and paste it at the next unused
cell starting from the end of the sheet [Range("VI1")]

if you know a better way to avoid those steps and use better ones please
provide me with assistance.

Hi Abdul

Change the line "Windows("Book1").Activate" to
"Windows("Book1.xls").Activate"

.... the Windows collection holds all open windows (not just Excel ones) and
as such, needs the full name. Also, when you commented out this line, the
next line - "Sheets("Sheet1").Select" had the same error. I'm guessing this
is because both open sheets have a "Sheet1" and the debugger has no idea
which one to use ... throwing up a rather cryptic error message which means
"Not sure where to look in the Sheets collection array".

BTW - It's better practice to assign your workbooks/sheets etc to the
correct object variable. There's plenty of good info on this in the Excel VBA
help files.

 

[Abdul]

thanks for the reply, however your suggestion didn't solve the problem. and
currently I edited few more of the lines and removed some unnecessary ones,
but I'm still unable to paste the copied data, the code now looks like the
following:

Range("B1:B20").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\User\Desktop\Book1.xls"
ActiveWorkbook.Windows("Book1.xls").Activate
ActiveWorkbook.Sheets("Sheet1").Activate
Application.Goto Reference:="R1C256"
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
Selection.Paste
End sub

the debugger shows there is an error on the line before the
last,[selection.paste] do you have any suggestions?

 

[BrianB]

If you had recorded a macro you would have found that the correct
final line is
ActiveSheet.Paste

 

[Abdul]

ActiveSheet.Paste

I've been using "Record Macro" function to come up with most of the lines
up there, but it seems that even using exact copy & paste doesn't work.

I tried the following:
ActiveSheet.paste
ActiveCell.paste
Activeworkbook.paste
active... etc, none of them worked.

in the end I just removed the header for "Before save" kind of auto macro,
into a normal user triggered macro, and it finally worked, but sometimes it
fails when the contents of the target paste cells contain a different format
pattern than the source copy format of the cells. in my case it was the way
the Date was formatted on source was different from destination, adjusting
them manually to be the same format fixed the issue. but i don't know if
it'll happen again.