Stop macro at empty cell

[swarren]

I need help with a macro in vba to copy a cell range from one sheet into another but I want it to stop when it comes to an empty cell. 1st sheet I copy b2:b? and d2:I? and paste into 2nd sheet.

 

[Andrew Poulsom]

Is there any data below the first empty cell?

 

[steve the fish]

Something like this?

Sub Macro1()

Dim sh As Worksheet
Set sh = Sheets("Sheet1")

If sh.Range("B3").Value <> "" Then

    sh.Range(sh.Range("B2"), sh.Range("B2").End(xlDown)).Copy _
    Destination:=Worksheets("Sheet2").Range("A1")
    
    sh.Range(sh.Range("D2:I2"), sh.Range("D2:I2").End(xlDown)).Copy _
    Destination:=Worksheets("Sheet2").Range("B1")
    
Else
    sh.Range("B2").Copy Destination:=Worksheets("Sheet2").Range("A1")
    sh.Range("D2:I2").Copy Destination:=Worksheets("Sheet2").Range("B1")
    
End If

End Sub

 

[swarren]

I actually have 4 worksheets. The first 1 one is input, 2nd document, 3rd transaction and upload. Yes, there are different amounts each time I copy my data from the input sheet. There is data in columns a thru m but I only need to copy certain columns from each sheet The amount of data could be from a2:a200 or a2:a30. I need the macro to stop at the end of the last column when it comes to a blank cell. I am not sure how to write it. I am new at this and trying to learn.

Thanks

 

[Andrew Poulsom]

You haven't answered my question.

 

[swarren]

No,

Sorry

 

[Andrew Poulsom]

In that case try eg:

With Worksheets("Sheet1")
    .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Copy Worksheets("Sheet2").Range("B2")
End With

 

[swarren]

Would I put that for each sheet or would that apply to all of them? What would go after End With? Sorry for so many questions but I have my macro recorded and I am trying to add this in.

Thanks

 

[Andrew Poulsom]

If you post your recorded code someone may amend it for you.