Forum Discussion

kvhpkh's avatar
kvhpkh
Copper Contributor
Jan 12, 2023
Solved

How can I copy and paste multiple sheets at once while keeping my row/column formatting?

I have tried many different things and nothing is working.  I want to copy the first sheet and pasted it into the other 25 sheets, while keeping the row/column formatting (as well as all the formatti...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Jan 14, 2023

    kvhpkh 

    Try this version:

    Sub CreateSheets()
    Dim y As Long
    Dim w As Long
    Dim d As Date
    Dim i As Long
    Dim s As Worksheet
    y = InputBox(Prompt:="Enter the year", Default:=Year(Date) + 1)
    w = Weekday(DateSerial(y, 1, 1), vbSaturday)
    d = DateSerial(y, 1, 1) + 14 - w
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 2 Step -1
        Worksheets(i).Delete
    Next i
    Set s = Worksheets(1)
    For i = 1 To 25
        s.Copy After:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = Format(d + 14 * i, "mmm d")
    Next i
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
kvhpkh's avatar
kvhpkh
Copper Contributor
Jan 13, 2023
I have already created the sheets and they are dated 2 weeks apart. Now, I want to copy the data in the 1st sheet - as shown in the attached file. And, keep the row/column formatting.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 13, 2023

    kvhpkh I'd delete the additional sheets, then run the macro to create new copies. That's much easier than trying to get everything just right if the sheets exist already.

    • kvhpkh's avatar
      kvhpkh
      Copper Contributor
      Jan 14, 2023
      I tried that and it asked me "what year?" When I entered 2023 it gave me a run time error 1004 and said "that name has been taken, try another".
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 14, 2023

        kvhpkh 

        Try this version:

        Sub CreateSheets()
    Dim y As Long
    Dim w As Long
    Dim d As Date
    Dim i As Long
    Dim s As Worksheet
    y = InputBox(Prompt:="Enter the year", Default:=Year(Date) + 1)
    w = Weekday(DateSerial(y, 1, 1), vbSaturday)
    d = DateSerial(y, 1, 1) + 14 - w
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 2 Step -1
        Worksheets(i).Delete
    Next i
    Set s = Worksheets(1)
    For i = 1 To 25
        s.Copy After:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = Format(d + 14 * i, "mmm d")
    Next i
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
    • kvhpkh's avatar
      kvhpkh
      Copper Contributor
      Jan 14, 2023
      Ok. Thank you!

Resources