VBA Copy range from another workbook if cell is not blan

[jakel27]

Hi all,

I want to copy values from Workbook B to Workbook A but only the rows in the range without a blank cell in column U in workbook B (S2:U5) in my code.

I would like the range to not be defined, but rather to the last row in column U. Then paste these values to Workbook A starting from cell L5.

------------------------------------------------------------------------------------------------ Code 1 (works without dynamic range)

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U5").Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

----------------------------------------------------------------------------------------------- Code 2 (my attempt but doesn't work)
Sub Timestamp()

Workbooks.Open ("Workbook B") 'This is my file location for Workbook B

Dim erow As Long, lastrow As Long, i As Long

lastrow = Timestamp.Cells(Rows.Count, 21).End(xlUp).Row

For i = 2 To lastrow
If Timestamp.Cells(i, 21) <> "" Then
Sheets("Timestamp").Range(Cells(i, 19), Cells(i, 21)).Copy
ThisWorkbook.Sheets("Toyota").Activate
ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues
End If

Next i

End Sub

 

[Joe4]

Can you show us an example of what your data looks like, along with your expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jakel27]

Hi Joe,

Workbook B - I want to copy range S:U for rows where U is not blank.

I want to paste these range values into Workbook A starting from cell L5. ie. In this scenerio I only wanted to see VP591 and VP598

 

[Joe4]

If this original code really works:

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U5").Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

and if the there are no blanks in column U in the middle of your data (they are all at the bottom like you showed in your example), then try this:

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U" & Cells(Rows.Count, "U").End(xlUp).Row).Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

 

[jakel27]

If this original code really works:

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U5").Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

and if the there are no blanks in column U in the middle of your data (they are all at the bottom like you showed in your example), then try this:

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U" & Cells(Rows.Count, "U").End(xlUp).Row).Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

Perfect thank you.

I don't believe I will have any gaps in my rows. I'll keep testing for it, but if there is a gap in rows, is it hard to adapt this code to accommodate this?

I won't require it atm but will ask forum for help probably if I do.

Thanks again for your help

 

[jakel27]

If this original code really works:

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U5").Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

and if the there are no blanks in column U in the middle of your data (they are all at the bottom like you showed in your example), then try this:

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U" & Cells(Rows.Count, "U").End(xlUp).Row).Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

Turns out I do have blank rows in my U column. Is there a way to copy the rows that have values only?

 

[Joe4]

Let's copy it all over, and then delete the blank rows afterwards, something like this (this avoids having to use loops, which are usually slower and less efficient):

Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B

Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U" & Cells(Rows.Count, "U").End(xlUp).Row).Copy 'This is my file location for Workbook B

ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues

Dim lastrow As Long
    
'Find last row with data in column N
lastrow = ThisWorkbook.Sheets("Toyota").Cells(Rows.Count, "N").End(xlUp).Row

'Delete rows that have blanks in column N
ThisWorkbook.Sheets("Toyota").Range("N5:N" & lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete