Hi all
I'm trying to copy a selection from one worksheet and paste it to another. The range varies depending on the time of the month, and so I only need to copy data up until the first blank row. I have a simple code to do this, however I think its seeing a blank drop-down cell as live cell and so is copying all these blanks rather than stopping at that point. Can anyone suggest a way around this?
My simple code is:
I'm trying to copy a selection from one worksheet and paste it to another. The range varies depending on the time of the month, and so I only need to copy data up until the first blank row. I have a simple code to do this, however I think its seeing a blank drop-down cell as live cell and so is copying all these blanks rather than stopping at that point. Can anyone suggest a way around this?
My simple code is:
Code:
Private Sub Gather_data()
Dim All As Long
All = Cells(1, 1).End(xlDown).Row
'Copy the data
Sheets("March 2017").Range("D1:Z" & All).Copy
'Activate the destination worksheet
Sheets("Pivot2").Activate
'Select the target range
Range("D1").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("Pivot2").Columns("D:Z").AutoFit
End Sub
