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.

 

[KOKOSEK]

try:

If ActiveSheet.CodeName = Sheet3 Then

 

[SouthernGent0327]

try:

If ActiveSheet.CodeName = Sheet3 Then

Thank you so much for the help, I changed it to:

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

But I am still getting the Object Doesn't Support Error.

 

[Fluff]

How about

If ActiveSheet.CodeName = Sheet3 Then
    Sheet4.Range("J15").Value = Sheet3.Range("J16").Value
End If

 

[SouthernGent0327]

How about

If ActiveSheet.CodeName = Sheet3 Then
    Sheet4.Range("J15").Value = Sheet3.Range("J16").Value
End If

I tried this as well and still get the Object Support Error. Is it perhaps because of the way the sheet copies pages? Here is the code for copying the pages:

 Dim J As Integer
    Dim K As Integer
    Dim sMo(12) As String
    Dim D As Integer
    Dim ws As Worksheet
    Dim S As String
    Dim sh As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("MASTER")
    
    S = DTForm.tbSite.Value
    
    ws.Range("A1") = S
    
    D = DTForm.tbYear.Value

    sMo(1) = "October" & Space(1) & D
    sMo(2) = "November" & Space(1) & D
    sMo(3) = "December" & Space(1) & D
    sMo(4) = "January" & Space(1) & D + 1
    sMo(5) = "February" & Space(1) & D + 1
    sMo(6) = "March" & Space(1) & D + 1
    sMo(7) = "April" & Space(1) & D + 1
    sMo(8) = "May" & Space(1) & D + 1
    sMo(9) = "June" & Space(1) & D + 1
    sMo(10) = "July" & Space(1) & D + 1
    sMo(11) = "August" & Space(1) & D + 1
    sMo(12) = "September" & Space(1) & D + 1

    For J = 1 To 12
        If J <= Sheets.Count Then
            If Left(Sheets(J).Name, 5) = "Sheet" Then
                Sheets(J).Name = sMo(J)
                Sheets(J).Visible = True
            Else
                ws.Copy After:=Sheets(Sheets.Count)
                ActiveSheet.Name = sMo(J)
            End If
        Else
            ws.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = sMo(J)
        End If
    Next J

    For J = 1 To 12
        If Sheets(J).Name <> sMo(J) Then
            For K = J + 1 To Sheets.Count
                If Sheets(K).Name = sMo(J) Then
                    Sheets(K).Move Before:=Sheets(J)
                End If
            Next K
        End If
    Next J
    Sheets(1).Activate
           
ThisWorkbook.Worksheets("MASTER").Visible = False

Here is the most recent code I tried:

I have attached a picture of the error message, the document only opens to the master page, could this have something to do with it not working properly. Thanks again for the help.

 

[Fluff]

Which line of your code gives the error?

 

[SouthernGent0327]

I tried this as well and still get the Object Support Error. Is it perhaps because of the way the sheet copies pages? Here is the code for copying the pages:

Dim J As Integer
    Dim K As Integer
    Dim sMo(12) As String
    Dim D As Integer
    Dim ws As Worksheet
    Dim S As String
    Dim sh As Worksheet
  
    Set ws = ThisWorkbook.Worksheets("MASTER")
  
    S = DTForm.tbSite.Value
  
    ws.Range("A1") = S
  
    D = DTForm.tbYear.Value

    sMo(1) = "October" & Space(1) & D
    sMo(2) = "November" & Space(1) & D
    sMo(3) = "December" & Space(1) & D
    sMo(4) = "January" & Space(1) & D + 1
    sMo(5) = "February" & Space(1) & D + 1
    sMo(6) = "March" & Space(1) & D + 1
    sMo(7) = "April" & Space(1) & D + 1
    sMo(8) = "May" & Space(1) & D + 1
    sMo(9) = "June" & Space(1) & D + 1
    sMo(10) = "July" & Space(1) & D + 1
    sMo(11) = "August" & Space(1) & D + 1
    sMo(12) = "September" & Space(1) & D + 1

    For J = 1 To 12
        If J <= Sheets.Count Then
            If Left(Sheets(J).Name, 5) = "Sheet" Then
                Sheets(J).Name = sMo(J)
                Sheets(J).Visible = True
            Else
                ws.Copy After:=Sheets(Sheets.Count)
                ActiveSheet.Name = sMo(J)
            End If
        Else
            ws.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = sMo(J)
        End If
    Next J

    For J = 1 To 12
        If Sheets(J).Name <> sMo(J) Then
            For K = J + 1 To Sheets.Count
                If Sheets(K).Name = sMo(J) Then
                    Sheets(K).Move Before:=Sheets(J)
                End If
            Next K
        End If
    Next J
    Sheets(1).Activate
         
ThisWorkbook.Worksheets("MASTER").Visible = False

Here is the most recent code I tried:

I have attached a picture of the error message, the document only opens to the master page, could this have something to do with it not working properly. Thanks again for the help.

Which line of your code gives the error?

The larger block of code works well, I only get the error when I use the following:

If ActiveSheet.CodeName = Sheet3 Then
Sheet4.Activate
Sheet4.Range("J15").Value = Sheet3.Range("J16").Value
End If

 

[Fluff]

Yes but which line is highlighted if you click debug?

 

[SouthernGent0327]

Yes but which line is highlighted if you click debug?

Sorry, understood, the following line is highlighted:

[B][U]If ActiveSheet.CodeName = Sheet3 Then[/U][/B]
    Sheet4.Activate
    Sheet4.Range("J15").Value = Sheet3.Range("J16").Value
End If

 

[SouthernGent0327]

Sorry, understood, the following line is highlighted:

If ActiveSheet.CodeName = Sheet3 Then
    Sheet4.Activate
    Sheet4.Range("J15").Value = Sheet3.Range("J16").Value
End If

The first line!