Automatically Create Worksheets Based On Row Data

ssil2000 · Mar 5th 2008

Hi there,
what i am trying to do basically is set up a workbook with one page used as data entry data entry is inputted row by row, now what i would like to do once all the data is entered is have a macro create a worksheet for each row based on a template i have created.

is this possible? or maybe print out what would populate the worksheets?

any assistance would be greatly appreciated!

thanks,

ssil2000

Simon Lloyd · Mar 5th 2008

Re: Automatically Create Worksheets Based On Row Data

something like:

Code

Sub Create_Wbooks()
Dim Rng As Range, MyCell As Range
Set Rng = Sheets("Sheet1").Range("A2", Range("A65536").End(xlUp))
For Each MyCell In Rng
Workbooks.Open ("C:\YOUR WORKBOOK OR PATH.xls")
Sheets.Add
ActiveSheet.Name = MyCell.Value
Next MyCell
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Display More

ssil2000 · Mar 5th 2008

Re: Automatically Create Worksheets Based On Row Data

Hi Simon,
thanks for the quick response, however i am looking to do something a bit different, basically i have a form which i have created and what i am looking to do is use that form as a template say locked on "sheet 2" and have the row data inputted on "sheet 1" populate the the form but each row will have different information and the form would be differnt in each scenario. i guess i thought it may be easier for it to create worksheets based on the template (within the same workbook) but i wonder if it would be easier for it to send to print each form for example

read row1 data --> populate template --> print form --> read row2 data --> populate template --> print form etc

i would probalby prefer to have the it create worksheets so it could be proofed before printing but its not crucial. (keep in mind this would be a read only workbook and those extra worksheets would be temporary)

thanks again, i hope i havent made this more complicated then it needs to be.

ssil2000

Simon Lloyd · Mar 5th 2008

Re: Automatically Create Worksheets Based On Row Data

Considering both explanations you gave were different i suggest that you upload a test workbook here with the before and after manipulation with a short note, that way you will get the response you need!

Bill Rockenbach · Mar 5th 2008

Re: Automatically Create Worksheets Based On Row Data

Try this (not tested)

Simon, I think I hae the idea of what you want.

Code

Option Explicit


Sub CopyWorksheets()
    Dim c As Range
  
    For Each c In Sheets("Sheet1").Range("A2", Range("A65536").End(xlUp))
        Workbooks.Open ("C:\MyPath\" & c & ".xls") 'or .xlt if a true template file


        ActiveSheet.Sheets("MyFormName").Copy Before:=Workbooks("NewForms").Sheets(1)
        
        'add your code to copy data to the added form
        '
        '
    Next c


End Sub

Display More

ssil2000 · Mar 7th 2008

Re: Automatically Create Worksheets Based On Row Data

hi guys,
sorry for the delay, its been a bit busy

i have attached a sample workbook, basically the sample data page has the information that needs to passed on to the forms (highlighted in green) and the form is currently set to what it would look like according to the first row of data (row2) and the database sheets are what populate the forms.

the form currently has drop down menus but if it were to macro to new sheets they would not be necessary.

any help would be appreciated.

cheers,

ssil2000

Automatically Create Worksheets Based On Row Data

Participate now!

Share