Create the sheets with cells range then after paste the "Template" sheet in New created Sheets

santoshloka

Board Regular
Joined
Aug 31, 2017
Messages
120
Hello Evreyone

I need little help to create macro.

Create sheets with cells range
copy the template sheet
paste in cells range

"Please see the Highlighted Red Color"


'Name macro
Sub CreateSheets()

'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range

'Enable error handling
On Error GoTo Errorhandling

'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)

'Iterate through cells in selected cell range
For Each cell In rng

'Check if cell is not empty
If cell <> "" Then

'Insert worksheet and name the worksheet based on cell value
Sheets.Add.Name = cell
Sheets("Template").Copy After:=Sheets("cell").Paste
End If

'Continue with next cell in cell range
Next cell


'Go here if an error occurs
Errorhandling:

'Stop macro
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RobVos

Board Regular
Joined
Feb 17, 2006
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub CreateSheets()

'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range

'Enable error handling
'On Error GoTo Errorhandling

'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)

'Iterate through cells in selected cell range
For Each cell In rng

'Check if cell is not empty
If cell <> "" Then

'Insert worksheet and name the worksheet based on cell value
'Sheets.Add.Name = cell
Sheets("Template").Copy After:=Sheets("Template")
Sheets("Template (2)").Name = cell

End If

'Continue with next cell in cell range
Next cell


'Go here if an error occurs
Errorhandling:

'Stop macro
End Sub
 
Solution

RobVos

Board Regular
Joined
Feb 17, 2006
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
I have your workbook. Just so I am clear you want to be able to do the following:
  • Select a range in column D (Chainage)
  • Run the macro
  • For each selected cell in column D, create a copy of the template
  • Rename the copy of the template tab with whatever is in the cell in column D
  • The data in the current template will be in each new sheet
  • You did not want to populate/overwrite the new sheets with any other data from the Box Dim tab
 

Forum statistics

Threads
1,141,301
Messages
5,705,580
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top