VBA TO COPY DATA TO NEXT SHEET!

[SouthernGent0327]

Good Afternoon,

I have a workbook which opens to a userform for the user to enter name and year. Once entered the submit button copies a master sheet for 12 months with the entered year and hides the master sheet. My problem is I need a macro which can copy data from a cell on the ActiveSheet (which ever month it may be to the very next month) in a different cell. Since the worksheet tab names always change I have tried to reference the sheet code name in the following manner:

Sub TransferData()

If ActiveSheet = Sheet3 Then
    Sheet3.Range("J16").Copy
    Sheet4.Activate
    Sheet4.Range("J15").xlPasteValues
End If

End Sub

This code generates Object Doesn't Support Error and I am not sure why. My thought was I would just create this same code for each sheet with a button to transfer data to the next sheet, but I can't figure out how to accomplish this task. Please help!! Thanks in advance.

 

[Fluff]

ok, try it like

If ActiveSheet.Name = Sheet3.Name Then

 

[SouthernGent0327]

ok, try it like

If ActiveSheet.Name = Sheet3.Name Then

Thank you so much that worked perfect! I really appreciate all the help.

 

[Fluff]

Glad we could help & thanks for the feedback.