VBA to list page numbers across workbook

[Jriley_11]

I have a workbook that will contain a different number of sheets and in different order depending on the scenario that is auto populated using VBA.
The format the company uses for numbering the sheets is "Page 1 Of 10" (for example) with "page 1 of" in one cell and "10" in the cell next to it. I need code to auto populate "page 1 of", "page 2 of", page 3 of", etc. in each visible sheet. The cell range is the same of every sheet (A9 & A10). I was thinking I can use a list and write code to copy each line in the list to each subsequent sheet. Does anyone know how I could write this, or even have a better way of doing so? The issue I am having is having consolidated information in cell A9. Thanks in advance!

 

[Jeffrey Mahoney]

What is the information in A9 and A10? This is not hard. Are you trying to use values in cells for each sheet to determine the page number?

 

[Jriley_11]

A9 is "page 1 of", "page 2 of", etc for each page. I just need code to auto write this for me. In some cases ill have 50 sheets and dont want to take time to number them.

 

[Jeffrey Mahoney]

Please try this

Sub NumberSheets()
  Dim WB As Workbook
  Dim Sht As Worksheet
  Dim Cnt As Long
  Dim CntStr As String
  Dim A As String
 
  Set WB = ThisWorkbook
  Cnt = WB.Sheets.Count
  CntStr = Format(Cnt, "0")
   
  For Each Sht In WB.Worksheets
    
    If Sht.Visible = True Then
      A = Sht.Range("A9").Value
      If UCase(Left(A, 4)) = "PAGE" Then
        If Sht.Name <> A & " " & CntStr Then Sht.Name = A & " " & CntStr
      End If
    End If
  Next Sht
 
End Sub

 

[Jriley_11]

This is changing the sheet names. Basically what I need is code to take each subsequent cell value that's in a column and copy over to the visible sheets in order. For example, the column of values is on sheet1 (say A1:A10). I need to copy Sheet1A1 on sheet2A9, Sheet1A2 on sheet3A9, Sheet1A3 on sheet4A9, etc. Does this make sense?

 

[Jeffrey Mahoney]

Visuals work better for me. Can you post using XL2BB?

How would the macro know which sheet to copy to?

 

[Jriley_11]

the macro needs to loop through each visible sheet and copy value.

 

[Jeffrey Mahoney]

Are the sheets actually named Sheet1, Sheet2, Sheet3, etc?

Is the range on Sheet1 A1:A10 constant, or will there be more rows and less rows sometimes?

Do we have to create the sheets as they get copied?

 

[Jriley_11]

Thanks for your help bud, turns out I figured out how to do this using concatenate function along with a loop to number visible sheets. I have another line of code I need help with to finish my workbook. Maybe you can help. I need to add a certain number of rows based on a cell value. So if the cell value is 10, I want to add 10 rows into my table on a separate sheet with same formatting as above and below. The cell will be on Sheet1 in cell P20. and the rows I want to add are on sheet2 below row 12.