Macro that concatenates two cells, references a Date and then copy pastes value into

[rj1600]

Would love some help writing this macro
i tried attaching a file with more explanations within the file if it helps make it clearer. (in yellow highlights & red text)


I have two sheets. One titled 'Money', the second titled 'Data'
In the 'Money' tab, there's a section which currently begins from row 41 where the macro would be populating the data it's pulling. For clarity's sake, i'll refer to this section as the Expense section.

Essentially, the macro is pulling data from the 'Data' tab based on certain criteria (the date). If the criteria is met, it will copy the data of its respective row from the 'Data' tab; will go to the 'Money' tab, look for the last row that contains data in the first portion of the Expense section, creates a new row and pastes values in this new row; making sure not to be pasting over any previous data

written out as steps:

if the concatenation of N & O of 'Data' sheet, viewed as Date format, matches the month of today's date; then copy and paste values of the concatenation into the first available row in column B of 'Money' sheet (beginning from B42 onwards)
then still referencing that same row in 'Data' sheet, copy columns P thru S and paste values into columns C thru F of 'Money' sheet in the same row that was previously referenced.
then still referencing that same row in 'Data' sheet, copy columns T and U and paste values into columns H & I of 'Money' sheet in the same row that was previously referenced.
then still referencing that same row in 'Data' sheet, copy column V and paste values into column G of 'Money' sheet in the same row that was previously referenced



i hope i explained that decently. please let me know if the Excel file isn't any clearer and i'll add more clarity.

[Pecoflyer]

Hi and welcome
Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question
As you are new I will add it for you this once https://www.ozgrid.com/forum/index.p...i/#post1243461

[Bob Phillips]

Try this

Public Sub GetData()
Dim target As Worksheet
Dim testdate As Date
Dim nextrow As Long
Dim lastrow As Long
Dim i  As Long
    Application.ScreenUpdating = False
    
    Set target = Worksheets("Data")
    With target
    
        lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
    End With
    
    
    With Worksheets("Money")
    
        If .Cells(42, "B").Value = vbNullString Then
        
            nextrow = 42
        ElseIf .Cells(43, "B").Value = vbNullString Then
        
            nextrow = 43
        Else
        
            nextrow = Cells(42, "B").End(xlDown).Row + 1
        End If
        
        For i = 2 To lastrow
        
            testdate = 0
            On Error Resume Next
            testdate = DateSerial(Year(Date), target.Cells(i, "N").Value, target.Cells(i, "O").Value)
            On Error GoTo 0
            
            If testdate = Date Then
            
                .Cells(nextrow, "B").Value = Date
                target.Cells(i, "P").Resize(1, 4).Copy .Cells(nextrow, "C")
                target.Cells(i, "T").Resize(1, 2).Copy .Cells(nextrow, "H")
                target.Cells(i, "V").Copy .Cells(nextrow, "G")
                
                nextrow = nextrow + 1
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

[rj1600]

thanks so much for the help Bob.

Just tried running it, getting this error: Run-time error '13' Type mismatch.
when i click Debug, it stops at this point:

If .Cells(42, "B").Value = vbNullString Then


any ideas why? i'm going to do some googling and try to understand what the code is doing

[Pecoflyer]

Please add code tags around code ( the #button). Thanks

Please confirm you have read and understood post #2

[rj1600]

yes i understand post 2

[Pecoflyer]

Please add code tags as requested in post #4

[rj1600]

i didn't see the option to edit my post


are you also able to help with my question or just policing?

[Pecoflyer]

As an admin, one of my jobs is keeping the forum tidy

[Bob Phillips]

Just tried running it, getting this error: Run-time error '13' Type mismatch.
when i click Debug, it stops at this point:

If .Cells(42, "B").Value = vbNullString Then


any ideas why? i'm going to do some googling and try to understand what the code is doing

No, I ran it on the workbook you provided and it worked for me.

But, I have just tried it again, and I forgot to mention you need to clear out all of those #N/As in A42:F52.

I also noticed an omission in my code, one unqualified cell reference. this is the corrected version

Public Sub GetData()
Dim target As Worksheet
Dim testdate As Date
Dim nextrow As Long
Dim lastrow As Long
Dim i  As Long
    Application.ScreenUpdating = False
    
    Set target = Worksheets("Data")
    With target
    
        lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
    End With
    
    
    With Worksheets("Money")
    
        If .Cells(42, "B").Value = vbNullString Then
        
            nextrow = 42
        ElseIf .Cells(43, "B").Value = vbNullString Then
        
            nextrow = 43
        Else
        
            nextrow = .Cells(42, "B").End(xlDown).Row + 1
        End If
        
        For i = 2 To lastrow
        
            testdate = 0
            On Error Resume Next
            testdate = DateSerial(Year(Date), target.Cells(i, "N").Value, target.Cells(i, "O").Value)
            On Error GoTo 0
            
            If testdate = Date Then
            
                .Cells(nextrow, "B").Value = Date
                target.Cells(i, "P").Resize(1, 4).Copy .Cells(nextrow, "C")
                target.Cells(i, "T").Resize(1, 2).Copy .Cells(nextrow, "H")
                target.Cells(i, "V").Copy .Cells(nextrow, "G")
                
                nextrow = nextrow + 1
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub