Solved: Copy Selected Rows to Chosen Sheet

[Shaolin1976]

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:
[vba]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 Sub[/vba]This 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.
[vba]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[/vba]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

[OBP]

It works fine for me, except that it pastes the copied data right across the row repeating it until it fills the entire row.
That suggests that the entry in C1 is not the name of a valid Sheet as it worked with both Sheet3 and a as the entry for me.

[ALe]

[VBA]
Destinationsheet=Worksheets("MASTER")
[/VBA]

[ALe]

Sorry.

[ALe]

Try this:
[VBA]
Private Sub CopyRows()
Dim DestinationSheet As String

Worksheets("MASTER").Select
Selection.Rows.Copy
DestinationSheet = Worksheets("MASTER").Range("C1").Value
NextBlankRow = Sheets(DestinationSheet).Range("A65536").End(xlUp).Row + 1
Sheets(DestinationSheet).Rows(NextBlankRow).PasteSpecial
End Sub
[/VBA]

[lucas]

Try this if Ale's code doesn't work out:
[vba]
Option Explicit
Sub CopyRows_Click()
Dim NextBlankRow As Long
Worksheets("MASTER").Select
Selection.Rows.Copy
NextBlankRow = Sheets("DestinationSheet").Range("A65536").End(xlUp).Row + 1
Sheets("DestinationSheet").Rows(NextBlankRow).PasteSpecial
End Sub
[/vba]

Edit:
Remember that the sheet your copying to must be named DestinationSheet
Exactly...or you will get a subscript out of range error....

ps you can't designate a cell to copy to if your going to look for the next blank row to copy to (per your first post...) a little confusion here I think. this will copy from master to destinationsheet, change the names to suit your need....

[ALe]

I suppose DestinationSheet is the content of the cell C1 as Shaolin wrote "I have a cell in the MASTER sheet (C1) in which I designate the destination sheet to copy the rows to."

[johnske]

[VBA]Option Explicit

Sub CopyRows_Click()
Worksheets("MASTER").Select
Selection.EntireRow.Copy
Sheets(Sheets("MASTER").[C1].Value) _
.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) _
.PasteSpecial Paste:=xlFormats '< or whatever
End Sub[/VBA]

[lucas]

Yes Ale, I am admittedly a little confused as to what is trying to be accomplished....Hey John, good to see you....

[Shaolin1976]

Thanks for all the helpful replies and suggestions everyone, ALe's solution worked perfectly. I could have sworn I had previously tried declaring DestinationSheet as a String but no matter, I must have done something wrong in that attempt.

Now to try and tackle the ListBox instead of having it read the destination from Cell C1.

Shaolin

PS Dont worry I refuse to post my problems unless I'm utterly at a loss so you wont see any posts regading the next stage of my little macro!

PPS You were correct in suggesting that DestinationSheet does not refer to the name of a sheet but merely the contents of C1

[ALe]

remember to mark this thread as solved