Hi Guys,
I have an issue and I can't seem to resolve it. I have a source sheet where I search for respective current month and year and where I want to copy row A to the destination sheet (so all "A" rows with currenth month+year)
So in this case my source range is from "A8003:A8133" and I want to copy everything to the destination sheet. What my code currently does however, is literally copying A8003:A8133 to destination A8003:A8133 - data is already present here since they are not equal length (I want this to be the last row, since it's dynamic).
In my destination sheet I also have a blank cell where I want it to stop, after the blank cell older data is present (but this is not part of the table).
Thats where the code below is for - to stop at the blank cell :
This is my code as it is now:
Any help is appreciated, and if you have further questions please do not hestiate to ask them.
I have an issue and I can't seem to resolve it. I have a source sheet where I search for respective current month and year and where I want to copy row A to the destination sheet (so all "A" rows with currenth month+year)
So in this case my source range is from "A8003:A8133" and I want to copy everything to the destination sheet. What my code currently does however, is literally copying A8003:A8133 to destination A8003:A8133 - data is already present here since they are not equal length (I want this to be the last row, since it's dynamic).
In my destination sheet I also have a blank cell where I want it to stop, after the blank cell older data is present (but this is not part of the table).
Thats where the code below is for - to stop at the blank cell :
VBA Code:
Last_Row = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
For i = Last_Row To 1 Step -1
If IsEmpty(wsDest.Cells(i, 1)) Then
Last_Row = i
Exit For
End If
Next
This is my code as it is now:
VBA Code:
Sub Example()
Dim Last_Col As Long
Dim FoundDate As Range
Dim Last_Row As Long
Set wsDest = ThisWorkbook.Worksheets("ATP combi")
Set wsSource = ThisWorkbook.Worksheets("ATP-waarde bij inzet")
Dim lr As Long, r As Long
'Quit looping at the encounter of a blank cell with a forloop and finding the last row that contains data
'
Last_Row = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
For i = Last_Row To 1 Step -1
If IsEmpty(wsDest.Cells(i, 1)) Then
Last_Row = i
Exit For
End If
Next
With wsSource
lr = .Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If IsDate(.Range("A" & r).value) Then
If Month(.Range("A" & r).value) = Month(Now()) And Year(.Range("A" & r).value) = Year(Now()) Then
wsDest.Range("A" & r) = wsSource.Range("A" & r).value
End If
End If
Next r
End With
End Sub
Any help is appreciated, and if you have further questions please do not hestiate to ask them.