This code doesn't work

[dpaton05]

Please help as I am still learning vba and I don't know how to debug code yet. This is the code I have written:

Sub cmdCopy_Click()


Application.ScreenUpdating = False
'Modified  10/25/2018  10:15:34 PM  EDT

Dim Lastrow As Long
Dim Combo As String

    Combo = Worksheets("Home").Range("Q5")
    Lastrow = Sheets("Combo").Cells(Rows.Count, "B").End(xlUp).Row + 1
    Worksheets("Home").Range("a5:c5").copy


    With Worksheets("Combo").Cells(Lastrow, 1)
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With
        
    Lastrow = Sheets("Combo").Cells(Rows.Count, "B").End(xlUp).Row
    Worksheets("Home").Range("j5").copy
    Worksheets("Combo").Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I have a spreadsheet to record yearly costings that has a home tabe with a table with one row and once the values are entered for that costing, it performs some calculations to arrive at a figure after referencing a heap of information in the background. Then a button is clicked and it copies the data to the bottom of the appropriate month worksheet.

There is a worksheet for every month of the year and the format for the name of the worksheet is for instance, July2018, August2018 etc.

A5:C5 and J5 contains the info I have to copy.
The date is in A5. Q5 has this formula: =CONCATENATE(O5,P5) P5 formula: =TEXT(A5, "yyyy") O5 formula: =TEXT(A5, "mmmm").
Theoretically this should work I thought but I don't know how to reference the combo variable in a sheet reference, or if you even can. Any help would be greatly appreciated.

Thanks,
Dave

 

[MARK858]

Try removing the quotes in

Sheets("Combo")

and

Worksheets("Combo")

 

[dpaton05]

I removed the quotes and added worksheet in front and now my code looks like this

Sub cmdCopy_Click()


Application.ScreenUpdating = False
'Modified  10/25/2018  10:15:34 PM  EDT

Dim Lastrow As Long
Dim Combo As String

    Combo = Worksheets("Home").Range("Q5")
    Lastrow = Worksheets(Combo).Cells(Rows.Count, "B").End(xlUp).Row + 1
    Worksheets("Home").Range("a5:c5").copy


    With Worksheets(Combo).Cells(Lastrow, 1)
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With
        
    Lastrow = Sheets(Combo).Cells(Rows.Count, "B").End(xlUp).Row
    Worksheets("Home").Range("j5").copy
    Worksheets("July2018").Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

It appears to work but it only copies A5:C5 and doesn't copy J5. How should I change it to copy J5 into the cell alongside of where C5 is copied to?

 

[MARK858]

Are you sure that

    Lastrow = Sheets(Combo).Cells(Rows.Count, "B").End(xlUp).Row
    Worksheets("Home").Range("j5").copy
    Worksheets("July2018").Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues

shouldn't be

    Lastrow = [COLOR="#FF0000"]Worksheets("July2018")[/COLOR].Cells(Rows.Count, "B").End(xlUp).Row
    Worksheets("Home").Range("j5").copy
    Worksheets("July2018").Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues

 

[MARK858]

Please ignore this post as thinking about it possibly not.

 

[dpaton05]

I found it, I changed the "July2018" in the 3rd last line of code to combo.

 

[dpaton05]

I have just been told that the reporting month goes from between the 26^th of the previous month to the 25^th of the current month. I am not sure what to do to differentiate what month the costing must go in. Could someone help me please?