VBA Macro Lastrow Copy/Paste

Mitchx

New Member
Joined
Oct 20, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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 :

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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

RobP

Active Member
Joined
May 8, 2008
Messages
427
Office Version
  1. 365
Platform
  1. Windows
Hi,
if I've understood well, you want to copy your source data into the space between the blank row you found above, and the rows of old data below.

If you amend the second part of your code to thus, does it do what you needed ?
VBA Code:
With wsSource

  lr = .Cells(Rows.Count, "A").End(xlUp).Row
  For r = lr To 2
        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" & Last_Row).EntireRow.Insert 'insert a new blank row where you found your blank row above
                
                wsDest.Range("A" & Last_Row) = wsSource.Range("A" & r).Value  'copy data to that blank row
                Last_Row = Last_Row + 1
            End If
        End If
    Next r

End With
 
Upvote 0

Mitchx

New Member
Joined
Oct 20, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hi, Robp somehow it doesn't do anything for me, but I forgot some things (my bad)

Basically:

- I have a source sheet where every month new data gets added (so a whole new month) - this source sheet is not a table!
- The Date in the source sheet is located in column "A". I want to iterate through column "A" till it finds the current month + year of the day today. (so it will not matter if it is the 1st of August or 18th, I want the whole month).
- The range is dynamic so I needed the last-row statement.
- I want to copy the whole range of current month + year to the destination sheet (so only the information corresponding to the if statement)
- My destination sheet (I forgot to mention) is a table, I want to append the data from the source sheet (with if statement) to the last row of the table. and thus resizing the table with the new information
.The old information, currently residing in the table must not be overwritten, but the new data must be appended.


Somehow, I cant seem to fix it.

This is my new code as of 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
Dim tbl As ListObject
'Quit looping at the encounter of a blank cell with a forloop and finding the last row that contains data



Set tbl = wsDest.ListObjects("Tabel3")

Last_Row = wsDest.ListObjects("Tabel3").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

MsgBox Last_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" & Last_Row) = wsSource.Range("A" & r).value
End If
End If
Next r

End With






End Sub

Help is appreciated, and thanks for the input Rob!
 
Upvote 0

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,144
Office Version
  1. 365
Platform
  1. Windows
You don't actually say what it is doing or not doing.
One thing that seems to be missing is a line incrementing the output row.

See below:
Rich (BB code):
    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
                Last_Row = Last_Row + 1
                wsDest.Range("A" & Last_Row) = wsSource.Range("A" & r).Value
            End If
        End If
    Next r
 
Upvote 0

Mitchx

New Member
Joined
Oct 20, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
You don't actually say what it is doing or not doing.
One thing that seems to be missing is a line incrementing the output row.

See below:
Rich (BB code):
    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
                Last_Row = Last_Row + 1
                wsDest.Range("A" & Last_Row) = wsSource.Range("A" & r).Value
            End If
        End If
    Next r

Yes, Alex this seems to work. The last_row statement was placed at the wrong line before.

One more question. Below the table I have data that is not part of the table. However, this macro seems to overwrite that data not part of the table

Is there a way to also move the data not in the table, so it will not be overwritten?
 
Upvote 0

RobP

Active Member
Joined
May 8, 2008
Messages
427
Office Version
  1. 365
Platform
  1. Windows
I can't see that you've copied what I was doing at all really. My original code inserted an extra line every time if copied it down. Thus not overwriting your data.
VBA Code:
 wsDest.Range("A" & Last_Row).EntireRow.Insert 'insert a new blank row where you found your blank row above
                
                wsDest.Range("A" & Last_Row) = wsSource.Range("A" & r).Value  'copy data to that blank row
                Last_Row = Last_Row + 1

you might want to re-look at what I did again.
 
Upvote 0

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,144
Office Version
  1. 365
Platform
  1. Windows
@Mitchx - your code in post #3 is different to what it was when @RobP made his suggestion.
So Rob's code of inserting an EntireRow should work for you but since you are now finding the Last non-empty row in the table, you will need to advance the Last_Row counter by 1 "before" you do the insert.
 
Upvote 0

Forum statistics

Threads
1,186,431
Messages
5,957,797
Members
438,320
Latest member
Thatisfree

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top