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

santoshloka

Board Regular
Joined
Aug 31, 2017
Messages
125
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

RobVos

Board Regular
Joined
Feb 17, 2006
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
Try this:

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

santoshloka

Board Regular
Joined
Aug 31, 2017
Messages
125

ADVERTISEMENT

Try this:

VBA Code:
'Insert worksheet and name the worksheet based on cell value
Sheets("Template").Copy After:=Sheets("Template")
Sheets("Template (2)").Name = cell
Thanks for your reply

it is creating new "Template" sheet instead of range sheets

rng=Tab1,Tab2
i want Template sheet to paste in Tab1 and Tab2

VBA Code:
'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("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
 

RobVos

Board Regular
Joined
Feb 17, 2006
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
I guess I misunderstood - or perhaps I was unclear. I thought you wanted to make copies of the template sheet and name each based on the select range.

Comment out the following line and try again and I think it will do what you want:

Sheets.Add.Name = cell
 

RobVos

Board Regular
Joined
Feb 17, 2006
Messages
64
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

1627489313903.png


1627489246810.png
 

santoshloka

Board Regular
Joined
Aug 31, 2017
Messages
125
Template sheet have some data.(better to copy whole Template sheet in Tab1,Tb2...etc)

The data should copy in all tabs like you shown in picture
 

RobVos

Board Regular
Joined
Feb 17, 2006
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
It does that. In my example, I just have "My Template" in A1 as the data. It is in each new tab.
 

santoshloka

Board Regular
Joined
Aug 31, 2017
Messages
125
can you please try this

iam not getting it


in Box Dim sheet have Chainages.Those are around 85 cells..85 sheets to create with copy of template sheet
 

Forum statistics

Threads
1,148,257
Messages
5,745,700
Members
423,969
Latest member
CHHeights

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