Copy a range with variable row amount into a closed workbook

[chr1sj]

Hi all,

I've seen many similar requests for help to this but I cannot put it all together to get the desired result and would greatly appreciate some help.

I have assigned a macro to a button on a sheet and would like the macro to do the following:
Copy a range from U to AW. The 1st row is 4 and the last would be variable to the last used row.
Paste this data to a closed workbook that originates in the same file. I'd need to select "sheet2" to paste to and to paste from the 1st blank row. The range of columns to paste to is A to AC.

Thanks all and apologies this is so similar to a number of other questions.

Chris

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, lRow As Long
    Set srcWS = ActiveSheet
    lRow = srcWS.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    Workbooks.Open ("C:\users\cjohnson\desktop\Fulfilled_orders.xlsx")
    srcWS.Range("U4:AW" & lRow).Copy
    Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

If this doesn't work, I wouldn't need to see all your data (de-sensitized if necessary), a dozen rows or so along with the formulas would be enough.

 

[chr1sj]

The above didn't work but I've used:

Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues

replacing:

Sheets("Sheet2").Range("A" & lRow + 1).PasteSpecial xlPasteValues

in the previous solution:

Sub CopyRange() Application.ScreenUpdating = False Dim srcWS As Worksheet, lRow As Long Set srcWS = ActiveSheet lRow = srcWS.Range("W" & srcWS.Rows.Count).End(xlUp).Row Workbooks.Open ("C:\users\cjohnson\desktop\Fulfilled_orders.xlsx") srcWS.Range("U4:AW" & lRow).Copy Sheets("Sheet2").Range("A" & lRow + 1).PasteSpecial xlPasteValues Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

This now works perfectly.

Thanks very much for your help!

Kind regards, Chris

 

[mumps]

You are very welcome.