Code to create temporary Sheets, Print them, the Delete the Sheets

J

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
Hi,

I'd like to add the necessary code to an already existing CommandButton (called CommandButton_Choose_These_Students) that does the following five (5) things ...

1. Creates a new worksheet (which is to be a copy of a worksheet called ... Student Profile Template) for each ID number found in the ranges ...

* PrintTemplate!W49:W400
* PrintTemplate!Z49:Z400
* PrintTemplate!AC49:AC400
* PrintTemplate!AC49:AC400

Most likely, out of all those cells, there might only be a handful that actually have a student ID in them,
sometimes perhaps 60 or so, but I have to cover the possibility of more.

2. Each of the new sheets created should be named the ID Number that was found in the four ranges mentioned above .... so, if there were just three ID numbers found in those four ranges (the ID numbers being ... 32765, 34985, 23660), then three new sheets should be created and named 32765, 34985, and 23660.

3. Print just Page 1 (Range A1:Y39) of each of those new Sheets made.

4. Delete those very same sheets, once the printing has been done.

5. Clear the contents of those four ranges mentioned ...

* PrintTemplate!W49:W400
* PrintTemplate!Z49:Z400
* PrintTemplate!AC49:AC400
* PrintTemplate!AC49:AC400

I should ask ... the sheets created will each have a large table full of cells that will be filled dynamically, and depending on the computer the user is using, might take a few moments to complete filling, so should we add another instruction after task 2. to pause for 20 seconds before proceeding with task 3. which is the printing ? I fear the printed page might come out with only part of the table filled if we print to soon after task 2 is instructed, but if that's not likely to happen, then don't worry about it.

I don't know if you need to know the name of the File, but it is ... Fancy Print Request Practice

Any help with the coding of this would be hugely appreciated.

Very kindest regards,

Chris
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hello

Am unsure of how you distinguish a Student ID number to any other value in the cell ranges. I've just checked for cells >0.

Code: 
Sub STUDENT_ID()
    For MY_COLS = 23 To 29 Step 3
        For MY_ROWS = 49 To 400
            Sheets("PrintTemplate").Activate
            MY_ID = Cells(MY_ROWS, MY_COLS).Value
            If MY_ID > 0 Then
                Sheets("Student Template Profile").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
                ActiveSheet.Name = MY_ID
                'print code here
                Application.DisplayAlerts = False
                ActiveSheet.Delete
                Application.DisplayAlerts = True
            End If
        Next MY_ROWS
    Next MY_COLS
    Sheets("PrintTemplate").Range("W49:W400, Z49:Z400, AC49:AC400").ClearContents
End Sub

you will need to add your own print code. It can vary.

Does this meet your requirements?
 
Last edited:
Upvote 0
Thankyou, so much, for that code, however, because the code is deliberately deleting any newly created sheets after they've been printed, so without the print code, I can't see the result of the code.

I am noticing though that the contents of those four ranges are not clearing at the end.

Is someone able to provide me a print code to insert where indicated, or for anyone seeing this query for the first time, I've actually revised it a little .... it's a streamlined version of my original query .....

I'd like to add the necessary code to a CommandButton (called CommandButton_Choose_These_Students) that does the following five (5) things ...

1. Creates a new worksheet (which is to be a copy of a worksheet called ... Student Profile Template) for each ID number found in the range ... PrintTemplate!AH49:AH400 .... most likely, out of all those cells, there might only be a handful that actually have a student ID in them,
sometimes perhaps 60 or so, but I have to cover the possibility of more.

2. Each of the new sheets created should be named the ID Number that was found in the range mentioned above .... so, if there were just three ID numbers found in that range (the ID numbers being ... 32765, 34985, 23660), then three new sheets should be created and named 32765, 34985, and 23660.

3. Print just Page 1 (Range A1:Y39) of each of those new Sheets made.

4. Delete those very same sheets, once the printing has been done. There will be other sheets that exist in the workbook that I do not want deleted, so just delete the sheets that were created in step 1

5. Clear the contents of the following four ranges ...

* PrintTemplate!W49:W400
* PrintTemplate!Z49:Z400
* PrintTemplate!AC49:AC400
* PrintTemplate!AC49:AC400

Out of interest, is there a way to code it so each is printed as a PDF ? If not, absolutely not a problem ... just interested.

Any help with the coding of this would be hugely appreciated.

Very kindest regards,

Chris
 
Upvote 0
Is someone able to provide me a print code to insert where indicated
Click to expand...
The only person that truly knows what you desire in the printout (ie: headers, footers, with or without date, sheet name, page #s, center on paper, fit to sheet, etc.) is you.
Record a macro of manually doing a printout the way you want it and we can help 'tune it up' for inclusion in the procedure.
 
Upvote 0
OK, I recorded a macro of me printing range A1:Y39 of the template sheet from which the sheets I need should be copied, but of course, the code needs to allow for the fact that I don't want that sheet to be printed, but the newly created sheets that are copies of this template.

These newly created sheets need to be each named the names that will be found in the range ... PrintTemplate!AH49:AH400


Here is the code formed from the recording I made ...

Code: 
Sub Print_Student_Profile()
'
' Print_Student_Profile Macro
'


'
    Range("A1:Y39").Select
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$Y$39"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Kind regards,

Chris
 
Upvote 0
With your recorded macro doing everything with the 'activesheet', and added sheets automatically being active,
I'd omit the Range("A1:Y39").Select line and just drop in what was recorded.
I've added that 20 second delay but have commented it out.
Code: 
Private Sub CommandButton_Choose_These_Students_Click()

    Dim rng As Range, cel As Range
    
Set rng = Sheets("PrintTemplate").Range("AH49:AH400")

For Each cel In rng
    If cel.Value = "whatever determines an ID" Then   'don't know what
        'add sheet for this student
        Sheets("Student Profile Template").Copy
        ActiveSheet.Name = cel.Value
        
        'wait :xx seconds
        'Application.Wait (Now + TimeValue("0:00:20"))
            
'your print requirements go here
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = "$A$1:$Y$39"
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
'end of your print requirements
        
        'delete the sheet
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        
    End If
Next cel

'clear contents of rng
rng.ClearContents

End Sub
 
Upvote 0
OK,

First query ...

these lines are causing a problem ...

Code: 
For Each cel In rng    If cel.Value = "whatever determines an ID" Then   'don't know what
        'add sheet for this student
        Sheets("Student Profile Template").Copy
        ActiveSheet.Name = cel.Value

this should refer to any cell in the range AH49:AH400 that has an entry in it .... if there is an entry, it'll be a 5-digit number, but I'm not sure that's important, is it ? Shouldn't the code simply look to find which cells in that range are not empty ?

Next query ...

this part of the code ...

Code: 
 'delete the sheet
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True

will this delete each sheet that was just created using the rest of the code, or only the most recent sheet to be 'active' ... for example, if three sheets were created using the rest of the code, would all three sheets be deleted, or just the most recent one ?

Next query ...

this part of the code at the end ...

Code: 
'clear contents of rng
rng.ClearContents

is supposed to be clearing the range PrintTemplate!V49:AF400, but it seems to be clearing the range PrintTemplate!AH49:AH400

Next query ...

I don't know if it's because these other lines are causing a problem, but when I action the code, I don't get prompted to confirm a print or anything.

If/when the code is finally working correctly, what should I expect to see pop up on the screen to confirm a print job needs to be done ... surely it wouldn't be austomatic, because wouldn't my printer operate differently from other printers ?

Kind regards,

Chris
 
Upvote 0
First query ...
now I understand, changed to
Code: 
For Each cel In rng
    If cel.Value <> "" Then

Next query ...
that was individually

Next query ...
Is that what this meant? Aren't those last two ranges the same?
5. Clear the contents of the following four ranges ...

* PrintTemplate!W49:W400
* PrintTemplate!Z49:Z400
* PrintTemplate!AC49:AC400
* PrintTemplate!AC49:AC400
Click to expand...

Next query ...
guess I missed the part about multiple printers



Maybe something along the lines of this is more to your desire. Don't know what happens at the print stage
Code: 
Private Sub CommandButton_Choose_These_Students_Click()

    Dim rng As Range, cel As Range
    Dim prntshts As String, prntarr As Variant
    
Set rng = Sheets("PrintTemplate").Range("AH49:AH400")

For Each cel In rng
    If cel.Value <> "" Then
        prntshts = prntshts & "," & cel.Value
        Sheets("Student Profile Template").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = cel.Value
    End If
Next cel

Application.Wait (Now + TimeValue("0:00:20"))

prntarr = Split(Mid(prntshts, 2), ",")
Sheets(prntarr).Select

Application.Dialogs(xlDialogPrint).Show

Sheets(prntarr).Delete

Sheets("PrintTemplate").Range("V49:AF400").ClearContents

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
315
cokeoverpepsi123
C
A
  • Question Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
2K
akshay27
A
J
  • Locked
  • Question Question
Code to print 1st page of multiple sheets
Replies
1
Views
621
Joe4
Joe4
H
  • Solved
Split all sheets into separate workbooks, but always include "sheet2" with them
Replies
5
Views
864
Fluff
Fluff
P
  • Solved
VBA Creat a copy and Auto Delete not working
Replies
7
Views
652
PaulOPTC
P

Forum statistics

Threads
1,224,847
Messages
6,181,330
Members
453,032
Latest member
Pauh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top