Skip to content

Instantly share code, notes, and snippets.

@filipkral
Created October 16, 2014 16:56
Show Gist options
  • Save filipkral/3f6ad40257d0422efcf2 to your computer and use it in GitHub Desktop.
Save filipkral/3f6ad40257d0422efcf2 to your computer and use it in GitHub Desktop.
Insert Excel Worksheet for each selected cell
Sub InsertSheetForEachSelectedCell()
'Insert a sheet for each cell in a selected range
'Each new sheet will be called prefix + cell value + suffix
'User is prompted to enter prefix and suffix when this macro is run
'Will fail miserably if sheet with that name already exists!
'Typical use case: select A1:A10 with values 1,2,3,...,10 then run this macro.
Dim prefix As String
Dim suffix As String
prefix = InputBox("Enter Sheet name prefix", "SheetPrefix", "")
suffix = InputBox("Enter Sheet name suffix", "SheetPrefix", "")
Dim MyCell As range, MyRange As range
Set MyRange = Selection
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = prefix & MyCell.Value & suffix
Next MyCell
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment