Macro copy from range


P

puiuluipui

Hi, i need the below code to copy rows but only from A to J.

In M1 i have "Sheet3". The below code is saving rows to "Sheet3". If i write
"Sheet4" to cell "M1", then the code is saving rows to "Sheet4".
The code copy and add rows to the specified sheet. I need the code to do
exactly the same thing, but to copy only from range "A:J"


Sub copy3()
Dim LastRow As Long
Dim MySheet As String
MySheet = Sheets("Sheet1").Range("M1").Value
LastRow = Worksheets(MySheet).Cells(Worksheets(MySheet).Rows.Count,
"A").End(xlUp).Row
Worksheets("Sheet1").UsedRange.Copy
Worksheets(MySheet).Activate
Range("A" & (LastRow + 1)).Select
ActiveSheet.Paste
Worksheets(MySheet).Range("A" & (LastRow + 1)).PasteSpecial
Application.CutCopyMode = False
Sheets("Sheet1").Activate
Range("A1").Select

End Sub

Can this be done?
Thanks!
 
Ad

Advertisements

D

Don Guillett

Look in the reply to your Earlier post on the same subject. You are NOT
making friends here
 
J

JLatham

I believe that this will do what you want. It could be neater, but since
you're already used to it working the way it does, I left well enough alone.
As always, first test it in a copy of your workbook so that if I got
something wrong, I haven't messed you up too badly.

Sub copy_AtoJ()

Dim LastRow As Long
Dim MySheet As String
MySheet = Sheets("Sheet1").Range("M1").Value
LastRow = worksheets("Sheet1").Range("A" & rows.Count). _
end(xlup).Row
Worksheets("Sheet1").Range("A1:J" & lastrow).Copy
LastRow = Worksheets(MySheet).Cells(Worksheets(MySheet). _
Rows.Count, "A").End(xlUp).Row
Worksheets(MySheet).Activate
Range("A" & (LastRow + 1)).Select
ActiveSheet.Paste
Worksheets(MySheet).Range("A" & (LastRow + 1)).PasteSpecial
Application.CutCopyMode = False
Sheets("Sheet1").Activate
Range("A1").Select

End Sub
 
Ad

Advertisements

P

puiuluipui

It's working great!
Thanks!

JLatham said:
I believe that this will do what you want. It could be neater, but since
you're already used to it working the way it does, I left well enough alone.
As always, first test it in a copy of your workbook so that if I got
something wrong, I haven't messed you up too badly.

Sub copy_AtoJ()

Dim LastRow As Long
Dim MySheet As String
MySheet = Sheets("Sheet1").Range("M1").Value
LastRow = worksheets("Sheet1").Range("A" & rows.Count). _
end(xlup).Row
Worksheets("Sheet1").Range("A1:J" & lastrow).Copy
LastRow = Worksheets(MySheet).Cells(Worksheets(MySheet). _
Rows.Count, "A").End(xlUp).Row
Worksheets(MySheet).Activate
Range("A" & (LastRow + 1)).Select
ActiveSheet.Paste
Worksheets(MySheet).Range("A" & (LastRow + 1)).PasteSpecial
Application.CutCopyMode = False
Sheets("Sheet1").Activate
Range("A1").Select

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top