Forum Discussion

Jn12345's avatar
Jn12345
Copper Contributor
Oct 03, 2024

VBA LOOP to copy a hidden sheet n number of times and naming them based on rows

Hello All,

 

Back here again with something that seems like it should be simple but I cannot get my head around it.

 

I have a workbook where there is a table (unfortunately not a real table, and it uses merged cells due to needing to comply with formatting of the template) and the table will eventually contain different "Sleeve ID" down the first column. Depending on the day, that column might have 1 "Sleeve" or 30 and the naming might not be in order (i.e. SLV-001, SLV-003, SLV-004, SLV-008). Once this first table has been populated - at least for the first sleeve ID column - I would like to have a macro that can be selected that will then unhide a hidden sheet and copy it the amount of times that there are rows filled with sleeve ID's in the first table and then name each sheet as the Sleeve ID for each of the filled in rows in that first table.

 

SUMMARY: when macro is run I would like to:

-Unhide the hidden template sheet (sheet1 in my attached report)

-Create a copy of "sheet1" for each SLEEVE ID filled in on the main page in the table

-Name each created copy as each of the sleeve ID names written in the first table.

 

Currently I can create blank worksheets based on number of rows used in the first page, and name them correctly but I cannot figure out how to use my created template that I would like to use instead of creating blank sheets.

 

Please see attached

excel
Macros and VBA

4 Replies

Sort By
    • Jn12345's avatar
      Jn12345
      Copper Contributor
      Oct 03, 2024

      HansVogelaar 

      Hey Hans,

      Thanks for the quick reply. Please see attached. The button I will use will obviously be smaller but for now its just a huge button that converts rows to sheets. One thing i notice is it you click it right away it does an alright job of adding the sheets but puts them in the wrong spot. (would be nice to have them appear before the photos page but thats me being a little picky) I do notice that it spells/ formats the name of the sheet wrong. slv-001 instead of SLV-001 and then i obviously dont know how to make the sheets incorporate the info on sheet1. Ideally i think it would work something like. for how many rows doing an xl down count counts, unhide sheet1 then copy it count-1 times and then rename. I might be way off the mark but i am new to this stuff so bare with me! thanks!!

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 03, 2024

        Jn12345 

        Thanks. Try this version of the macro:

        Sub Rows_to_New_Sheet()
    Dim A As Range
    Dim W_C As Worksheet
    Dim W_S As Worksheet
    Dim W_T As Worksheet
    Dim LastRow As Long

    Application.ScreenUpdating = False

    ' Template sheet
    Set W_T = Worksheets("Sheet1")
    W_T.Visible = xlSheetVisible

    ' Active sheet
    Set W_C = ActiveSheet
    LastRow = W_C.Cells.SpecialCells(xlCellTypeLastCell).Row

    ' Loop
    For Each A In W_C.Range("D23:D" & LastRow)
        If A.Value <> "" Then
            ' Essential: reset W_S to nothing
            Set W_S = Nothing
            On Error Resume Next
            ' Check whether sheet for Sleeve ID already exists
            Set W_S = Worksheets(A.Value)
            On Error GoTo 0
            If W_S Is Nothing Then
                ' If not, copy the template sheet.
                W_T.Copy Before:=Sheets("Photos")
                Set W_S = Sheets("Photos").Previous
                W_S.Name = A.Value
            End If
        End If
    Next A

    ' Optional: hide the template sheet
    W_T.Visible = xlSheetHidden

    Application.ScreenUpdating = True
End Sub

Resources