Copy and Paste into 300 tabs in a workbook

[VBA learner ITG]

I hope everyone is having a nice day.

I was wondering if i could get your advice i have a tab called template which i want to copy into all 300 tabs in the workbook.

Each of the 300 tabs is called something different.

My Q. is there a VBA code that would do this.

1. Copy the tab data from Template straight into each tabs.

For your reference:

I have the below code which is being used to create the 300 odd tabs.

I hope this can be done as part of this code or as a seperate VBA code to be run.

Sub Create_Tabs()


Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("For Curve Analysis").Range("B2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet


Sheets("Template ").Select
Cells.Select
Selection.Copy
ActiveSheet.Paste




Next MyCell
End Sub

 

[JLGWhiz]

Help me to understand what you want. The code you have posted does what your narrative indicates that you want to do. So, I need to understand the difference between what you indicate you want and what the code you have is doing.

 

[jkpieterse]

I'm not clear on what you need precisely. I assume the 300 tabs already exist, so what does copy a tab into all 300 tabs mean?

 

[VBA learner ITG]

Apologies for clouding my issue.

I need a piece of VBA that can copy and paste a worksheet called template from cell a1 to j100 into every worksheet in the workbook.

 

[JLGWhiz]

It did not remove all the clouds, but see if you can work with this. Assuming that your Template worksheet is named "Template"
and your other sheet names are listed in a range in a sheet named "For Curve Analysis".

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("For Curve Analysis")
Set rng = sh2.Range("B2", sh2.Cells(Rows.Count, 2).End(xlUp))
    For Each c In rng
        sh1.Range("A1:J100").Copy Sheets(c.Value).Range("A1")
        Application.CutCopyMode = False 'try to avoid memory overload
    Next
End Sub

 

[Norie]

Do you have a 'template' worksheet that you want to copy multiple times and name the new worksheets from a list on the 'For Curve Analysis' sheet?

 

[My Aswer Is This]

You said in post #1:
I was wondering if i could get your advice i have a tab called template which i want to copy into all 300 tabs in the workbook.


Why not just make 300 copies of the template sheet and give them the names in MyRange

 

[My Aswer Is This]

If you like my suggestion. This script will make Copies of sheet named "Template" and give them the names in the range Sheets("For Curve Analysis") Range("B2") to last filled cell in Column "B"

Sub Copy_Template()
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("For Curve Analysis").Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To Lastrow
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheets("For Curve Analysis").Cells(i, 2).Value
    Next
Sheets("For Curve Analysis").Activate
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "That sheet name may already exist or be a improper sheet name"
Application.ScreenUpdating = True
End Sub

 

[VBA learner ITG]

Thank you for your input some amazing options and advice by my peers on here.

 

[My Aswer Is This]

Glad we were able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you for your input some amazing options and advice by my peers on here.