Macro-indirect cell

[puiuluipui]

Hi, i have this code:

Sub copy1()
Dim lastRow As Long
lastRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count,
"A").End(xlUp).Row
Worksheets("Sheet1").UsedRange.Copy
Worksheets("Sheet2").Activate
Range("A" & (lastRow + 1)).Select
ActiveSheet.Paste
End Sub

Can this code be made to copy rows to a specific sheet?
In "M1" i will write october, so when i run macro, the code to copy rows to
"october" sheet. If i write november, then the code to copy rows to
"november" sheet.
Can this be done?
Thanks!

 

[Mike H]

Hi,

Like this

Sub copy1()
Dim lastRow As Long
Dim MySheet As String
MySheet = Sheets("Sheet1").Range("M1").Value
Worksheets("Sheet1").UsedRange.Copy
lastRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2") _
..Rows.Count, "A").End(xlUp).Row
Worksheets(MySheet).Range("A" & (lastRow + 1)).PasteSpecial
End Sub

Mike

 

[Per Jessen]

Hi

You can use same technique as in my reply to your last post.

This one-liner code does what you need;

Sub copy1()
Worksheets("Sheet1").UsedRange.Copy Worksheets(Range("M1").Value).Range("A"
& Rows.Count).End(xlUp).Offset(1, 0)
End Sub

Regards,
Per

 

[Don Guillett]

You could and should use a data validation dropdown list in cell m1 so you
don't have to type it

Sub copySAS()
Dim LR As Long
With Sheets(Range("m1").Value)
lr = .Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").UsedRange.Copy .cells(lr,1)
End With
end sub

 

[puiuluipui]

Hi, it's working, but the macro replace the content in "october" sheet. I
need to add content.
And another thing is that in macro, is a line with Sheet2 adn if i rename
sheet2, i receive an error. I must have an sheet2 in my workbook? O the code
can be remade so, the sheet2 line to dissapear?
But the real problem is with adding rows instead of replacing.
Can this be done?
Thanks!