CODEWITHSUNDEEP
vbaexcel
Batteredburrito
Batteredburrito

Reputation: 589

Excel VBA Create a new sheet with data from the last sheet

Right, this is a big one for me. Im looking at a button that creates a new sheet based on the current sheets month. I currently have the code to create the new sheet and give it the name of next month, however, I now need to copy the formatting, Tickboxes, and buttons from the last sheet into the new sheet. Im sure i can then resolve copying data from specific cell ranges myself which wont be a problem.

I have the following code for the month naming and it works perfectly as intended, would anyone know how to implement copying into this:

Private Sub CreateNewMonth_Click()
Dim wSheet As Worksheet
Dim strName As String
Dim lMonth As Long
Dim lIndex As Long
Dim WrkShtSrc As Worksheet
Dim WrkShtTgt As Worksheet

    For Each wSheet In Worksheets
        Select Case wSheet.Name
            Case "Jan", "January"
                lMonth = 2
                lIndex = wSheet.Index
            Case "Feb", "Febuary"
                If lMonth < 3 Then lMonth = 3
                lIndex = wSheet.Index
            Case "Mar", "March"
                If lMonth < 4 Then lMonth = 4
                lIndex = wSheet.Index
            Case "Apr", "April"
                If lMonth < 5 Then lMonth = 5
                lIndex = wSheet.Index
            Case "May"
                If lMonth < 6 Then lMonth = 6
                lIndex = wSheet.Index
            Case "Jun", "June"
                If lMonth < 7 Then lMonth = 7
                lIndex = wSheet.Index
            Case "Jul", "July"
                If lMonth < 8 Then lMonth = 8
                lIndex = wSheet.Index
            Case "August", "August"
                If lMonth < 9 Then lMonth = 9
                lIndex = wSheet.Index
            Case "September", "Septemeber"
                If lMonth < 10 Then lMonth = 10
                lIndex = wSheet.Index
            Case "October", "October"
                If lMonth < 11 Then lMonth = 11
                lIndex = wSheet.Index
            Case "November", "November"
                If lMonth < 12 Then lMonth = 12
                lIndex = wSheet.Index
            Case "December", "December"
                If lMonth < 12 Then lMonth = 12
                lIndex = wSheet.Index
            Case Else
                If lMonth = 0 Then
                  lMonth = 1
                  lIndex = wSheet.Index
                End If
            End Select
        Next wSheet

If lMonth <> 0 And lMonth < 13 Then
On Error Resume Next
Worksheets.Add After:=Worksheets(lIndex)
ActiveSheet.Name = Format(DateSerial(Year(Date), lMonth, 1), "mmmm")
On Error GoTo 0
End If

End Sub

Thank you for the help in advance

Upvotes: 0

Views: 145

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

Use

worksheets(lIndex).Copy After:=Worksheets(lIndex)

to replicate worksheets(lIndex) next to worksheets(lIndex)

Upvotes: 1

Related Questions