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.

 

[jc.021286]

Hi there and welcome to the board,

I quickly created this loop that does what you want, however the methodology of mastercount is incomplete.

would it be best to simply count how many instances of data there are in column A on sheet1 and divide this by 32 to determine how many times you want to run this?

Or is there another way to determine how many of these Masters we are creating?

The Sub is pretty easy, I try to determine how many templates I am to create with the mastercount here.
then simply do a loop where I'm going to add a sheet called Master Template and whatever the loop # is.
Copy the 32 row range to the destination sheet.

You may want to ensure that there is only the 1 sheet in here. So probably making it read only to force people to save the result as a new file.

Let me know how to tweak this to your needs,
jc

Sub Allocate_Master()

mastercount = Application.WorksheetFunction.CountA(Sheets(1).Columns(1))
For a = 1 To mastercount
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master Template" & a
Range(Sheets(1).Rows(a + (31 * (a - 1))), Sheets(1).Rows(32 * a)).Copy Destination:=Sheets("Master Template" & a).Range("A1")
Next a

End Sub

 

[bubbles2010]

Thank you so much for your reply. I was about to add some more information.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The data in Sheet1 would start from row23 to however many rows. And this data from sheet1 need to be inserting into "master template" from row20 to row31.
<o></o>
<o></o>
For example: if Sheet1 data is from row23 to row50, I need to have three "Master template." <o></o>
<o></o>
<o></o>
Therefore, row23 to row34 from sheet1 would be inserted into “master template1” row20 to row31. <o></o>
Row34 to row 45 from sheet1 would be inserted into “master template2” row20 to row31. <o></o>
So forth and so on, depending on the data range from sheet1.<o></o>
<o></o>
<o></o>
If I create “master template1,2,3 and etc…” in advance, it would be easier, but I was wondering if there is a way to automatically create those master template sheet depending on the data from sheet1 and insert the right data at the same time.
<o></o>
<o></o>
Answering to your question: I don't know how many rows the user would input because it is depending on the orders and such.
Thanks.
<o></o>

 

[jc.021286]

okay, so we need to change some items up....

Sub Allocate_Master()

mastercount = (Sheets(1).Range("A65536").End(xlUp).Row - 23) / 11
mastercount = Application.WorksheetFunction.RoundUp(mastercount, 0)
For A = 1 To mastercount
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master Template" & A
Range(Sheets(1).Rows(12 + 11 * A), Sheets(1).Rows(23 + 11 * A)).Copy Destination:=Sheets("Master Template" & A).Range("A20")
Next A

End Sub
</pre>

 

[bubbles2010]

Thank you so much! It worked so well. I just have one more question. How can have the form that I created to be automatically generated instead of empty worksheet? When master templates were generated with the above code, the data were inserted into generated empty work sheets. How can I insert those data automatically into certain form or template? Thanks.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[jc.021286]

2 ways,
we either manually set up the template inside the loop to input the fields, if not too many.

however if you are talking formats and everything, it would probably be best to have some form of template and we would automatically set it to copy rows 1:19 of the template and insert onto the newest Master Template(a) to have formats, headers, etc.

Let me know what sounds best?

Are the headers and formats all on the first sheet Rows(1:22)?

jc

 

[bubbles2010]

I already have the set up template because there was a lot of formatting. It is just need to be inserted into the master templates. The template would in the same workbook with everything else because users also need to input some information in the template as well as in the data worksheet. Basically, the master template is the combination of the template plus the data worksheet information.

The master template1, master template2, master template 3 and etc. Rows (1:22) will have the same header and format as the template that I created.
Thank you so much for all of your help.

 

[jc.021286]

Okay,
though if I am to incorporate that in as code, I need to know the origin of the information and the destination.

Origin:...
what is the sheet name and range that I need to pull? is it a sheet called "Template"? or is it the Master (ie: Sheet 1) range of Rows 1:22?

Destination: Sheets("Master Template" & a).Cells("A1")

Thanks,
jc

 

[bubbles2010]

Yes. Origin: the name of the sheet is "Form" and it would start from A(1:22).
Destination: The sheet that are automatically created. In this case, "Master Template."

Also, when I run the code, I could only run it for one time. When I try to run the second time it keeps on giving me error. Is there a way to fix it? Because what if I want to add more data after I already run Macro once. I don't want to delete the sheet that I already created it. I just want to update the information. Is there a way?
Thank you so much for everything.

 

[jc.021286]

okays....

Adjusted to check if a sheet already exists and if so it will skip creating the
sheet.

Give this code a whirl,
jc

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 Destination:=Sheets("Master Template" & a).Range("A1")

Next a
End Sub

</pre>