Macro error - Copy paste data from column's range when there is actually no data on column

[limah82]

Hi there,


First of all, thank you in advance for your help and time

I am trying to compile in one sheet columns from different other sheets in one workbook.

The aim of the code below is to copy the content of the column "J" (excluding the 1 line) and copy on the B of the Master sheet. I assume this part of

Sheets("Securities").Select
With Range("J2")
.Resize(Cells(Rows.Count, "J").End(xlUp).Row - (.Row - 1), "1").copy
End With

Sheets("Master").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 2).Range("A1").Select
ActiveSheet.Paste

I assumethis part of code follows data that was already pasted (from other sheets) and also that column Ahas data on all its used lines. Reasonwhy I (xlDown) and then offset.

The formula works pretty well as long as there is data oncolumn J to copy but not if the column is empty

I am sure there is a workaround, but I am pretty newbiewith VBA, so I am very limited.

Thank you again

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub limah82()
With Sheets("Securities")
   With .Range("J2", .Range("J" & Rows.Count).End(xlUp))
      .Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
   End With
End With
End Sub

 

[limah82]

Dear Fluff,

Thanks for your help.

I stillencounter an error when copying a column where there is an header, but no dataon the below cells.
The codeprovided copies the header since there is no other data.
I believe Ishould include a condition that excludes copy paste if there is no data to becopied (i.e only one cell with data which is the Header… but that still allowsthe copy of the next sheet to be at the correct position (so below the numberof lines that could have data in there.)
In theexample provided, the formula works, as long as there is data on J2 or below,but if I have no data, the code copies J1.
Thanks again

 

[Fluff]

OK, how about

Sub limah82()
   Dim UsdRws As Long
   With Sheets("Securities")
      UsdRws = .Range("J" & Rows.Count).End(xlUp).Row
      If UsdRws > 1 Then
         .Range("J2:J" & UsdRws).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   End With
End Sub

 

[limah82]

it works thanks a lot !

 

[Fluff]

You're welcome & thanks for the feedback