Shaolin1976
05-03-2006, 03:57 AM
Hi,
I'm new to VBA and have run into a slight problem that I was hoping someone might help me out with. I am trying to copy the selected rows from one sheet (MASTER) to the next available blank row in a choice of other sheets a,b,c etc. I have a cell in the MASTER sheet (C1) in which I designate the destination sheet to copy the rows to. My code is as follows:
Private Sub CopyRows_Click()
Worksheets("MASTER").Select
Selection.Rows.Copy
DestinationSheet = Worksheets("MASTER").Range("C1")
NextBlankRow = Sheets(DestinationSheet).Range("A65536").End(xlUp).Row + 1
Sheets(DestinationSheet).Rows(NextBlankRow).PasteSpecial
End SubThis throws up an error:
Run-time error '9': Subscript out of range
The Code works fine when I remove the variable DestinationSheet and simply replace it with "a" i.e.
Private Sub CopyRows_Click()
Worksheets("MASTER").Select
Selection.Rows.Copy
NextBlankRow = Sheets("a").Range("A65536").End(xlUp).Row + 1
Sheets("a").Rows(NextBlankRow).PasteSpecial
End Sub
I'm sure it is a simple thing to correct but I can't seem to find the answer in the excel help files. If I had managed to get this working easily enough then my next stage was to try and substitute Cell C1 with a List Box of available sheets to copy to but this is problem enough I think! Any help with this would be greatly appreciated.
Shaolin
I'm new to VBA and have run into a slight problem that I was hoping someone might help me out with. I am trying to copy the selected rows from one sheet (MASTER) to the next available blank row in a choice of other sheets a,b,c etc. I have a cell in the MASTER sheet (C1) in which I designate the destination sheet to copy the rows to. My code is as follows:
Private Sub CopyRows_Click()
Worksheets("MASTER").Select
Selection.Rows.Copy
DestinationSheet = Worksheets("MASTER").Range("C1")
NextBlankRow = Sheets(DestinationSheet).Range("A65536").End(xlUp).Row + 1
Sheets(DestinationSheet).Rows(NextBlankRow).PasteSpecial
End SubThis throws up an error:
Run-time error '9': Subscript out of range
The Code works fine when I remove the variable DestinationSheet and simply replace it with "a" i.e.
Private Sub CopyRows_Click()
Worksheets("MASTER").Select
Selection.Rows.Copy
NextBlankRow = Sheets("a").Range("A65536").End(xlUp).Row + 1
Sheets("a").Rows(NextBlankRow).PasteSpecial
End Sub
I'm sure it is a simple thing to correct but I can't seem to find the answer in the excel help files. If I had managed to get this working easily enough then my next stage was to try and substitute Cell C1 with a List Box of available sheets to copy to but this is problem enough I think! Any help with this would be greatly appreciated.
Shaolin