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]

Hi. I run the code again.
The page number is working properly without error.
However, the page break and formatting doesn't seem to be working because when I view them as the page break view, they are not like the "Form" sheet page break view.

 

[jc.021286]

Good good,
The print area cannot simply be copied as a format and thus we would need to insert an extra line to set the print area if you understand what I mean.

The code would look like this,
ActiveSheet.PageSetup.PrintArea = Cell(1,1).Address(False, False) & ":" & Cell().End(xlDown).Address(False, False)

I need to know how the print set up is done to make that change, otherwise you got me stumped as to how to import the specific page breaks.

jc

 

[bubbles2010]

The printing style is in landscape. Right now without maually formatting, the page break are set for Column M and upto Row 34. However, I need all the "master template" to print upto column o and row 36.

I don't know if this is what you were asking.

Also, how can I format the size of different column in "mater template" to reflect the same column size with the "form"?
Thank you.

 

[jc.021286]

Hi there,
Selection.PasteSpecial Paste:=xlPasteAll, Paste:=xlPasteColumnWidths, Paste:=xlPasteFormats
Application.CutCopyMode = False

The xlPasteColumnWidth should have taken care of that...

As for the fit to a page, that took me a while to fix, but I got it below.
Hopefully this does everything now.

Sub Allocate_to_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).Activate
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
    ActiveSheet.PageSetup.PrintArea = "$A$1:$O$36"
    With ActiveSheet.PageSetup
        .Zoom = 83
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
Next a
End Sub

</pre>

 

[bubbles2010]

Hi again! Thanks for helping me. The printing is working, but the format for column is not working right. I don't know if I have to do something special with the sheet "Form" so the master template would come out looking like the "form" format. It is kind of important for this job because there are going to be a lot of data input, and we are going to produce at least 20 "master templates" per file. So, it would be really great if I don't have to go back and change it. Thank you.

 

[jc.021286]

here we go,
try this, I broke out the function to try and do it 3 times,
it will paste all
then paste columnwidths
then paste formats

let me know if this works,
if not I'll have to try and reproduce the error,
jc

Sub Allocate_to_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).Activate
Sheets("Master Template" & a).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Selection.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
Sheets("Master Template" & a).Range("O4") = "Page " & a & " of " & mastercount
    ActiveSheet.PageSetup.PrintArea = "$A$1:$O$36"
    With ActiveSheet.PageSetup
        .Zoom = 83
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
Next a
End Sub

 

[bubbles2010]

Thank you so much. It works perfectly. You are amazing. Thanks.