Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro copy from range
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro copy from range
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 "puiuluipui" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro copy from range
It's working great!
Thanks! "JLatham" wrote: 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 "puiuluipui" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to copy only range with data | Excel Discussion (Misc queries) | |||
macro to copy range | Excel Worksheet Functions | |||
Need help - Macro to copy a specific range | Excel Worksheet Functions | |||
Need help - Macro to copy a specific range | Excel Worksheet Functions | |||
MACRO TO COPY TO A RANGE | Excel Discussion (Misc queries) |