• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

creating multiple worksheets with macro

V

VDS

Member
Dear All,

Is anybody can tell how to create multiple worksheets with macro ? Sample file attached.

The format is same. Here 13 sheets are to be added in a single file.



VDS
 

Attachments

  • GWD - WCT - 2013-14 - FOR CHANDOO.xls
    16.5 KB · Views: 8
Try this..
Code: 
Sub Add_MultipleSheets()
Dim i As Long
    For i = 1 To 13
        Sheets("MG HOUSING").Copy After:=Sheets(i)
    Next
End Sub
 
Hai Deepak,

This is brilliant. The names of 13 worksheets are fixed and I want to add the name of worksheets in a single shot instead of renaming every time. So, how to do this.



VDS
 
@Deepak,

Here, you can define from 'A to R". Later on, it can be changed as per requirement. How to change the program


VDS
 
Check with it.
Code: 
Sub Add_MultipleSheets()
Dim i As Long, ws As Worksheet

Set ws = Sheets("MG HOUSING")

    For i = 1 To 13
        ws.Copy After:=Sheets(i)
        '//Choose as per need
      
        'method 1
        'ActiveSheet.Name = "ABCD" & i
      
        'method 2 / Sheet name taken form o column on Sheets -MG HOUSING
        'ActiveSheet.Name = ws.Cells(i, 15).Value
      
        'method 3
        ActiveSheet.Name = i
        
         'method 4
        'ActiveSheet.Name = ws.Name & i
    Next
End Sub
 
Or with a fixed list of array.
Code: 
Sub Add_MultipleSheets_A()
Dim i As Long, ws As Worksheet, list As Variant

list = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M","N","P","Q","R")

Set ws = Sheets("MG HOUSING")
    For i = 0 To UBound(list)
        ws.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
        ActiveSheet.Name = list(i)
    Next
End Sub
 
Hi VDS!

here another approach..

* Two rarely used feature of Excel..
Code: 
Sub test()
    Dim shanme() As String
    Const shCount = 13
    ShName = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M")
   
    Set ws = Sheets.Add(, Sheets("MG HOUSING"), shCount)
    Sheets.FillAcrossSheets Range:=Sheets("MG HOUSING").UsedRange, Type:=xlAll
   
    For i = 2 To shCount + 1
        Sheets(i).Name = ShName(i - 2)
    Next i
End Sub

* You can add as many as you want with Sheets.add(,,Count) feature
* You can fill across all the selected sheet, Only Format / Only Content / Or both with just Fill Across Worksheet Command
 
@Debraj,

The function is so good. Works like a rocket. Here I will give name of worksheets.

1 RITES Ltd. - JNU - II
2 RITES Ltd. - JNU - III
3 HSCL, Janpath
4 HSCL, Chandigarh
5 JP Noida I
6 JP Noida II
7 JP Noida III
8 GPL - Kshipra Bridge
9 Parsvnath Ghaziabad
10 Today Homes Gurgaon
11 Manor One, Gurgaon
12 Mulberry County
Faridabad
13 S S Group Pvt Ltd, Gurgaon

Suppose, format is changed, just like addition or deletion of columns, how changes will be effected in entire worksheets ? I run the macro again. But the no of worksheets are increasing apart from the existing number. How to rectify this?


VDS
 
Yes.. it was not bullet proof..

In case of validation of sheet exist & increase / decrease of sheets.. You need to follow @Deepak 's method of For loop ..
list = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M","N","P","Q","R")
Click to expand...

make changes here to reflect sheet names..

BTW.. are you from Delhi.. :)
 
You must log in or register to reply here.
Back
Top