StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think this is what you're looking for:</p> <pre><code>Sub GeneratePriceBook() Dim wsDaily As Worksheet Dim wsTemp As Worksheet Dim lVisibility As XlSheetVisibility Dim strSheetName As String Dim rIndex As Long Dim i As Long Set wsDaily = Sheets("Daily Order") Set wsTemp = Sheets("Template") lVisibility = wsTemp.Visible 'In case template sheet is hidden wsTemp.Visible = xlSheetVisible For rIndex = 2 To wsDaily.Cells(Rows.Count, "A").End(xlUp).Row 'Ensure valid sheet name strSheetName = wsDaily.Cells(rIndex, "A").Text For i = 1 To 7 strSheetName = Replace(strSheetName, Mid(":\/?*[]", i, 1), " ") Next i strSheetName = Trim(Left(WorksheetFunction.Trim(strSheetName), 31)) 'Make sure the sheet name doesn't already exist If Not Evaluate("IsRef('" & strSheetName & "'!A1)") Then wsTemp.Copy After:=Sheets(Sheets.Count) With Sheets(Sheets.Count) .Name = strSheetName .Range("A6").Formula = "='" & wsDaily.Name & "'!B" & rIndex .Range("B6").Formula = "='" & wsDaily.Name & "'!B" & rIndex 'You are referencing the same cell as in A6? .Range("A3").Formula = "='" & wsDaily.Name & "'!Q" & rIndex .Range("E36").Formula = "='" & wsDaily.Name & "'!M" & rIndex .Range("E36").Formula = "='" & wsDaily.Name & "'!Y" & rIndex 'You are putting a second formula in E36? .Range("E37").Formula = "='" & wsDaily.Name & "'!L" & rIndex End With End If Next rIndex wsTemp.Visible = lVisibility 'Set template sheet to its original visible state Set wsDaily = Nothing Set wsTemp = Nothing End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload