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]

What is the name of the sheet that contains the data to be copied? What is the name of the closed workbook including extension (xlsx, xlsm)? What is the full path to the folder where the closed workbook is saved?

 

[chr1sj]

Hi mumps, thanks for looking at this.
The sheet that will be copied has a variable name but is always the active sheet and is where the button is located.
Closed workbook is called Fulfilled_orders.
Path to both folders is c:\users\cjohnson\desktop

Chris

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet
    Set srcWS = ActiveSheet
    Workbooks.Open ("C:\users\cjohnson\desktop\Fulfilled_orders.xlsx")
    srcWS.Range("U4", srcWS.Range("AW" & srcWS.Rows.Count).End(xlUp)).Copy Sheets("Sheet2").Cells(Sheets("Sheets2").Rows.Count, "A").End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

 

[chr1sj]

Hi Mumps, what you've written does exactly as I asked, thanks. However, I need this to paste the values, currently it's copying any formulae that are present. How could I fix this? Apologies for the delayed response by the way.

Chris

 

[chr1sj]

Also (sorry), I've realised it's copying down further than necessary as the formulae are entered into rows that aren't necessarily used but the macro sees these. If it could look at column W to find the last row that would solve this.

Thanks again.

 

[mumps]

Try:

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

 

[chr1sj]

Sadly this no longer works Mumps. It copies the data but pastes it to row 13 every time, overwriting anything that might already be there and leaves rows 2 - 12 blank. It is however pasting the data correctly.

 

[mumps]

It would be easier to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[chr1sj]

It would be easier to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

I cannot really do that as there is too much data there. It's something that has been done manually previously.
What I've noticed is that the paste element does not always start from row 13, this was a mistake. The paste starts from the row number after the last line used in the source sheet. So if the copied data is from rows 4 to 12 the paste will start in row 13. I need it to use the 1st blank row on the destination sheet to begin the paste, not the row number from the source worksheet. I've tried to re-use the Copy Sheets("Sheet2").Cells(Sheets("Sheets2").Rows.Count, "A").End(xlUp).Offset(1) from your earlier answer to replace the use of the row1 statement in Sheets("Sheet2").Range("A" & lRow + 1).PasteSpecial xlPasteValues as I think that's all it needs but I cannot get it to work. I hope that makes sense and you don't mind quickly looking at that. If that doesn't sort it I shall have to leave it. Either way, thanks very much for your help.