Need help with Macro Code!

[bubbles2010]

Hi I don’t really know anything about Macro or VB. I just started learning recently because I started working on this project, and it requires the use of VB.

I have sheet 1 where users will input data, such as selecting from drop down list, inputting some other information, and etc. This sheet1 would have columns up to “O” and rows will depend on the data.


My problem is I need to create other work sheets from Sheet 1, and I need to split up the data from sheet1 into many sheets because I need to input the data from Sheet1 into the “master template” sheet that I created. “Master Template” Sheet can only run from rows 1 to 32 because it was required that way.


So, is there a Macro code that would generate Sheet2 (or “master template1”), Sheet3 (or “master template2”), and etc. automatically depending on the rows range from Sheet1? Also, inputting correct data range into different Master Template Sheets. For example: inserting rows 1 – 32 data from Sheet1 into “master template1” and inserting rows 32- 64 data from Sheet1 into “master template2.”

I hope my explanation is not confusing. Thank you very much. I really appreciate all of your help.

 

[bubbles2010]

Thank you. I will give it a try.

 

[bubbles2010]

Hi. I tired the new code that you posted. It is giving me run time error the first time I run it and create one "master template" even though I have data for at least three "master templates." Also, in the master template only the data shows up. Nothing from the form sheet show up in the master template. When I run it second time, it is giving me error "400," and create the same type of master template (data, but no information from the form sheet.) I don't know what it means. Can you please help me again? Thank you in advance.

 

[jc.021286]

Certainly,

For debugging,
While the code is open, I need you to run through it with the F8 key. What line is it that it is having problems with?
Your first sheet is the data page and you have a sheet called "Form" from which the template is being pulled.

thank you for your reply, I will assist you as best I can,
jc

 

[bubbles2010]

Hi. It worked now. Thank you so much.

 

[jc.021286]

Okay....
But why was it not working? It can be a pain if something is working and then is not working at times...

jc

 

[bubbles2010]

I think it was because of the range and naming. I am not sure. But now, it is running smoothly without a problem.
Thank you so much for your help. it was very helpful!

 

[bubbles2010]

Everything is working fine now, but I have one more problem. It is the formatting. When the "master template" are created, their page format such as column width, page break, and etc. are not the same as the form. I have to do it manually.

Also, I found out yesterday that I need to put page number "page # of #" in the Cell O4. They need to change automatically as well. Is that possible?

Thank you very much

 

[jc.021286]

updated per your request,

For a = 1 To mastercount
exists = False
For Each wkSheet In ThisWorkbook.Worksheets
    If wkSheet.Name = "Master Template" & a Then
       exists = True
       End If
    Next
If exists = True Then GoTo skipcreate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master Template" & a
'
skipcreate:
Range(Sheets(1).Rows(12 + 11 * a), Sheets(1).Rows(23 + 11 * a)).Copy Destination:=Sheets("Master Template" & a).Range("A20")
Range(Sheets("Form").Rows(1), Sheets("Form").Rows(19)).Copy Sheets("Master Template" & a).Range("A1").select
Selection.PasteSpecial Paste:=xlPasteAll, Paste:=xlPasteColumnWidths, Paste:=xlPasteFormats
    Application.CutCopyMode = False
Sheets("Master Template" & a).Range("O4") = "Page " & a & " of " & mastercount
Next a
End Sub

 

[bubbles2010]

Thank you so much for replying to me.

I tried the code that you posted. However, it doesn't seem to be working because it only creates the first sheet and stops there. Also, I want the format to be from "the form" sheet, not from sheet1.
Also, the "page # of #" didn't show up at all.
I am sorry to bother you again. Thanks for your help.

 

[jc.021286]

nope, my fault,

looks like when pasting the script in, the lines shifted

Sub Allocate_Master()

mastercount = (Sheets(1).Range("A65536").End(xlUp).Row - 23) / 11
mastercount = Application.WorksheetFunction.RoundUp(mastercount, 0)
For a = 1 To mastercount
exists = False
For Each wkSheet In ThisWorkbook.Worksheets
    If wkSheet.Name = "Master Template" & a Then
       exists = True
       End If
    Next
If exists = True Then GoTo skipcreate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master Template" & a
'
skipcreate:
Range(Sheets(1).Rows(12 + 11 * a), Sheets(1).Rows(23 + 11 * a)).Copy Destination:=Sheets("Master Template" & a).Range("A20")
Range(Sheets("Form").Rows(1), Sheets("Form").Rows(19)).Copy
Sheets("Master Template" & a).Range("A1").select
Selection.PasteSpecial Paste:=xlPasteAll, Paste:=xlPasteColumnWidths, Paste:=xlPasteFormats
    Application.CutCopyMode = False
Sheets("Master Template" & a).Range("O4") = "Page " & a & " of " & mastercount
Next a
End Sub