Need Excel print macro to exclude few worksheets from printing

Topic

New Reply

Hello group,
I am trying to program a macro that will exclude certain worksheets when an entire excel workbook is printed. I got the following code after an internet search, and it works well for a single sheet to be excluded (let’s call the worksheet ‘aaa’).

Sub don’t_pirnt_sheet()
Dim sht
Application.ScreenUpdating = False
For Each sht In Sheets
If sht.Visible And sht.Name “aaa” Then
With sht
.PrintOut copies:=1

End With
End If

Next

Sheets(“aaa”).Select
Application.ScreenUpdating = True
End Sub

Now I am trying to add a couple of other worksheets (say ‘bbb’ and ‘ccc’) to the ones not to be printed, but don’t know how to add those to the code. Could someone suggest the correct language? Thanks so much in advance.

-Satnair

Reply | Quote

Replies

Satnair,

This code will cycle through the all the sheets in the workbook and will print all but those listed in the specified line of code.

HTH,
Maud

Place in a standard module:

Code:

Sub don’t_pirnt_sheet()
    Dim sht
    Dim ShtName
    ShtName = Array(“aaa”, “bbb”, “ccc”) [COLOR=”#008000″]’ADD SHEET NAMES HERE NOT TO PRINT[/COLOR]
For I = 1 To Worksheets.Count
    For J = 0 To UBound(ShtName)
        If Worksheets(I).Name = ShtName(J) Then Count = 1
    Next J
    If Count = 0 Then Worksheets(I).PrintOut copies:=1
    Count = 0
Next I
End Sub

Reply | Quote

Hi

Another way is to print worksheets that have a particular tab colour.
Right-click on worksheet tab to change it’s colour.

Or, you could print sheets that don’t have any tab colour, as in this example:

Code:

Sub printSheets()
Application.ScreenUpdating = False
For Each z In ThisWorkbook.Worksheets
If z.Visible And z.Tab.ColorIndex = xlColorIndexNone Then
z.PrintOut copies:=1
End If
Next

Sheets(1).Select

End Sub

zeddy

Reply | Quote

Thanks, that method worked perfectly!

I have a follow-up request: I’d like to be able to create a PDF of the file, with the same stipulations (selected worksheets excluded); is that possible, and what would the code be?

Thanks again in advance.

Reply | Quote

Hi

I adapted my code to print chosen sheets to a pdf file:

Code:

'NOTE:
'Excel processes sheets in order of appearance, 
'i.e. left-to-right tab order
'(order as you see the tab names at the bottom).

Sub printToPDF()

Dim zArray() As Variant 'declares a dynamic array

'worksheet names will be placed into a dynamic array for later use
zMax = ThisWorkbook.Worksheets.Count
ReDim zArray(1 To zMax) 'declares the array variable with the appropriate size

Application.ScreenUpdating = False
For Each z In ThisWorkbook.Worksheets
If z.Visible And z.Tab.ColorIndex = xlColorIndexNone Then
i = i + 1
zArray(i) = z.Name
End If
Next

ReDim Preserve zArray(1 To i) 'update the array with just the required sheets

'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
zPath = ThisWorkbook.Path & ""
zFile = "myfile.pdf"
zSaveAs = zPath & zFile

'EXPORT SELECTED SHEETS TO PDF FILE..
Sheets(zArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=zSaveAs, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Sheets(1).Select

End Sub

zeddy

Reply | Quote

Hi

Another way to check sheetnames is to make use of the Like operator (one of my favourites)
For example (using code in previous post):

‘check if sheetname has 3 chars..
If z.Name Like “???” Then ‘e.g “Jan”,”Feb”,..,”Dec”
‘do this
End If

‘check if sheetname doesn’t have 4 chars..
If z.Name Not Like “????” Then ‘e.g “Apples”,”Bananas”,..,”Pears”

if z.Name Like “xx*” ‘sheet names that begin with “xx”

zeddy

Reply | Quote

Hi Zeddy-
Thanks so much for sharing your expertise; most of this is way over my head. I was able to successfully implement your first code and Maudibe’s codes to print sheets I need; but for some reason, your PDF example did not work. Moreover, the tabs on my spreadsheets are already variously colored, which creates conflicts with this approach.

I do have working method that creates PDF files out of a workbook where I specify which sheets to select (shown below). Problem is, some workbooks have more than 30 sheets, so I’d rather have a method where I ask it NOT to include certain sheets in the PDF file. Would you be able to tweak the code below to accomplish the task? In the example below, I would need sheets aaa, bbb, and ccc to be excluded form the PDF file.

Satnair.

Sub Pdf_selsheet()
‘
‘ Save selected sheets in PDF Macro
‘
Dim X As String
Dim BeamWidth As Integer
BeamWidth = Range(“G38”).Value

Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String

ThisWorkbook.Sheets(Array(“aaa”, “bbb”, “ccc”)).Select
Sheets(“aaa”).Activate

‘ // ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=”C:temptest.pdf”, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
‘ IgnorePrintAreas:=False, OpenAfterPublish:=False

Set FSO = CreateObject(“Scripting.FileSystemObject”)
s(0) = ThisWorkbook.FullName

If FSO.FileExists(s(0)) Then
‘//Change Excel Extension to PDF extension in FilePath
s(1) = FSO.GetExtensionName(s(0))
If s(1) “” Then
s(1) = “.” & s(1)
sNewFilePath = Replace(s(0), s(1), “.pdf”)

‘//Export to PDF with new File Path
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=sNewFilePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
Else
‘//Error: file path not found
MsgBox “Error: this workbook may be unsaved. Please save and try again.”
End If

Set FSO = Nothing

‘
End Sub

Reply | Quote

Hi

Just to let you know, I tested my printToPDF routine as in post#5.
I copied the code into a test file, using Excel2007 for this test.
(it is always helpful to let us know the Excel version you are using).
I created 6 sheets in the test file.
I added dummy test data on each sheet to identify each sheet.
I then changed the tab colour on two of the test sheets.
I saved the test file into a test folder.
Running the code (as posted) generated the single PDF file OK, with data only from each of the sheets that did NOT have a tab colour.
The PDF file was created and saved in the same folder location as the file containing the code.
The PDF file was named “myfile.pdf” as per the vba code posted.
Did you get a pdf file??

Anyway, as you have sheets that already have a tab colour, and you want these to be included in the output pdf file, we must use another method to get the required sheets for the pdf file.
You have two choices:
1. Have a defined list of the sheets you want for the pdf file, or
2. Have a list of sheets to be excluded from the pdf file.

If the excluded list is ‘shorter’, than perhaps that is the easiest way to do it.
Try this code, which uses a Case statement to define ‘excluded’ sheets:

Code:

'NOTE:
'Excel processes sheets in order of appearance,
'i.e. left-to-right tab order
'..so pdf file will be in same order as you
'see the tab names at the bottom.
'Move sheets to required order if required.

Sub printToPDF()
Dim z
Dim zArray() As Variant 'declares a dynamic array

'worksheet names will be placed into a dynamic array for later use
zMax = ThisWorkbook.Worksheets.Count
ReDim zArray(1 To zMax) 'first, make room for ALL sheets in array

'freeze display for speedup..
Application.ScreenUpdating = False

'Loop through all worksheets..
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each z In ThisWorkbook.Worksheets
    Select Case z.Name
    'List the excluded sheets here..
    Case "aaa", "dddd", "fffff", "ggg"      '<<add unwanted sheets here
    'ignore the specified sheets above
    Case Else
    'check if sheet is visible; i.e. excludes hidden sheets
    If z.Visible Then   'add sheet to printout array
    i = i + 1           'next array position
    zArray(i) = z.Name  'add sheetname to array
    End If              'end of test for visible sheet
    End Select
Next
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ReDim Preserve zArray(1 To i) 'update the array with just the required sheets

'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
'(change this to suit)..
zPath = ThisWorkbook.Path & "" 'use this folder for the saved pdf file
zFile = Dir(ThisWorkbook.Name)  'excel filename without path
zDot = InStrRev(zFile, ".")     'look backwards for file extension
If zDot = 0 Then                'this file doesn't have a file extension..
zPDF = zFile & ".pdf"           '..so just add one
Else                            'otherwise..
zPDF = Left(zFile, zDot) & "pdf"    'use same Excel name, but with .pdf
End If                          'end of test for file extension

zSaveAs = zPath & zPDF          'filename and folder for pdf file

'EXPORT SELECTED SHEETS TO PDF FILE..
Sheets(zArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=zSaveAs, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Sheets(1).Select

End Sub

I have attached a test file with this code. This test also shows the mix of portrait and landscape modes in the output pdf file.
Please save the file to a folder of your choice.

RG and Maud have also shown other ways of doing this.
Please let us know if this works.

zeddy

Reply | Quote

Hi Satnair

In my post#9 sample file, you could modify the code to allow ‘hidden’ sheets to be printed by removing the test for z.Visible

Also, the pdf file can be named whatever you like by adapting the code.

zeddy

Reply | Quote

Hi Zeddy-
That worked perfectly! I only had to make on change: last line, open-after-publish, changed form false to true. That solves my problem. Many Thanks again.

-Satnair

Reply | Quote

is there a way to program a excel workbook that will only print

Code:

certain tabs when the number of certain tabs changes?

[QUOTE=zeddy;999134]Hi

I adapted my code to print chosen sheets to a pdf file:

Code:

'NOTE:
'Excel processes sheets in order of appearance, 
'i.e. left-to-right tab order
'(order as you see the tab names at the bottom).

Sub printToPDF()

Dim zArray() As Variant 'declares a dynamic array

'worksheet names will be placed into a dynamic array for later use
zMax = ThisWorkbook.Worksheets.Count
ReDim zArray(1 To zMax) 'declares the array variable with the appropriate size

Application.ScreenUpdating = False
For Each z In ThisWorkbook.Worksheets
If z.Visible And z.Tab.ColorIndex = xlColorIndexNone Then
i = i + 1
zArray(i) = z.Name
End If
Next

ReDim Preserve zArray(1 To i) 'update the array with just the required sheets

'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
zPath = ThisWorkbook.Path & ""
zFile = "myfile.pdf"
zSaveAs = zPath & zFile

'EXPORT SELECTED SHEETS TO PDF FILE..
Sheets(zArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=zSaveAs, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Sheets(1).Select

End Sub

zeddy[/QUOTE]

Reply | Quote

Hi

Welcome to the Lounge as a new poster!

Re: is there a way to program a excel workbook that will only print certain tabs when the number of certain tabs changes?
Yes, this can be done. But you need to explain more.
What do you want to do?

zeddy

Reply | Quote

Hi. I have a spreadsheet that has multiple tabs that I have tied together. This is also used by farm fields. Some farmers have 2 fields and others have upwards towards 30 fields. There is certain tabs that we need to print(not whole spreadsheet but only some tabs). Some tabs are the same in every spreadsheet but others just need to have more field tabs. Also, the way it is built, some fields could be corn and others soybeans. In addition to printing tabs based on fields, it will have 2 sheets per tab but would only want to print the sheet that has info in the field description. How can that be programmed as well as the original question?
Thanks for the help!

Hi

Welcome to the Lounge as a new poster!

Re: is there a way to program a excel workbook that will only print certain tabs when the number of certain tabs changes?
Yes, this can be done. But you need to explain more.
What do you want to do?

zeddy

Reply | Quote

Hi

Suppose you change the tab colour according to type of crop e.g. use tab colour yellow for Corn, tab colour green for Spinach, tab colour orange for Beans etc etc.
Then, you could use the vba method of printing those sheets which have a particular tab colour.
Depending on how your worksheet tab names are defined, you could also print only those sheets that match a particular rule e.g. sheets that have the word ‘total’ anywhere in the sheetname.
Or, you could print only those sheets that have a particular value in a specified cell on the sheet.
For example, if cell [Z1] contains “Y”, then include the sheet in the printout, otherwise don’t print it.
So, what are your sheetnames like???

zeddy

Reply | Quote

Hi

I adapted my code to print chosen sheets to a pdf file:

Code:

'NOTE:
'Excel processes sheets in order of appearance, 
'i.e. left-to-right tab order
'(order as you see the tab names at the bottom).

Sub printToPDF()

Dim zArray() As Variant 'declares a dynamic array

'worksheet names will be placed into a dynamic array for later use
zMax = ThisWorkbook.Worksheets.Count
ReDim zArray(1 To zMax) 'declares the array variable with the appropriate size

Application.ScreenUpdating = False
For Each z In ThisWorkbook.Worksheets
If z.Visible And z.Tab.ColorIndex = xlColorIndexNone Then
i = i + 1
zArray(i) = z.Name
End If
Next

ReDim Preserve zArray(1 To i) 'update the array with just the required sheets

'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
zPath = ThisWorkbook.Path & ""
zFile = "myfile.pdf"
zSaveAs = zPath & zFile

'EXPORT SELECTED SHEETS TO PDF FILE..
Sheets(zArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=zSaveAs, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Sheets(1).Select

End Sub

zeddy

How do you update if you have a sheets names are coming from a list box ?

Private Sub UserForm_Click()
Private Sub chk_Select_all_Click()
Dim iloop As Integer
For iloop = 1 To Lst_sheets.ListCount
Lst_sheets.Selected(iloop – 1) = chk_Select_all.Value
Next
End Sub
Private Sub btn_Cancel_Click()
Unload Me
End Sub
Private Sub btn_Print_Click()
Application.EnableEvents = False
Dim iloop As Integer
Dim lss() As Variant
Dim c As Integer
c = 0
For iloop = 1 To Lst_sheets.ListCount
If Lst_sheets.Selected(iloop – 1) = True Then
ReDim Preserve lss(c)
lss(c) = Lst_sheets.List(iloop – 1)
Application.ScreenUpdating = False
ThisWorkbook.Sheets(Lst_sheets.Selected(iloop – 1)).ExportAsFixedFormat Type:=xlTypePDF, Filename:=”Test”, Quality:=xlQulityHigh, includeDocproperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
c = c + 1
Lst_sheets.Selected(iloop – 1) = False
End If
Next iloop

Unload Me
Call Main
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
Dim sSheet

For Each sSheet In Sheets
If sSheet.Visible xlSheetVisible Then GoTo Next_sheet
If sSheet.Type = 3 Then ‘Chart sheet
Lst_sheets.AddItem sSheet.Name
ElseIf WorksheetFunction.CountA(sSheet.Cells) > 0 Then
Lst_sheets.AddItem sSheet.Name
End If
Next_sheet:
Next sSheet
Application.EnableEvents = True
End Sub

Reply | Quote

Hi gvfos

Welcome to the Lounge as a new poster!

How do you update if you have a sheets names are coming from a list box ?

If the sheetnames are specified in a multi-select listbox, the code would be something like this..

Code:

Sub printToPDF()                            'v1c
Dim z
Dim zArray() As Variant 'declares a dynamic array

'worksheet names will be placed into a dynamic array for later use
zMax = ThisWorkbook.Worksheets.Count
ReDim zArray(1 To zMax) 'first, make room for ALL sheets in array

'freeze display for speedup..
'Application.ScreenUpdating = False

'Loop through all items in Userform listbox..
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set zSource = UserForm1.Lst_sheets  '<< define shortcut to listbox here
For j = 0 To zSource.ListCount - 1
If zSource.Selected(j) Then         'item in listbox is selected
zSht = zSource.List(j)              'fetch sheetname from listbox
Sheets(zSht).Visible = True         'make sure sheet is not hidden
i = i + 1                           'next array position
zArray(i) = zSht                    'add sheetname to array
End If                              'end of test for selected item in listbox
Next
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ReDim Preserve zArray(1 To i) 'update the array with just the required sheets

'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
'(change this to suit)..
zPath = ThisWorkbook.Path & "" 'use this folder for the saved pdf file
zFile = Dir(ThisWorkbook.FullName)  '<< v1a:excel filename without path
zDot = InStrRev(zFile, ".")     'look backwards for file extension
If zDot = 0 Then                'this file doesn't have a file extension..
zPDF = zFile & ".pdf"           '..so just add one
Else                            'otherwise..
zPDF = Left(zFile, zDot) & "pdf"    'use same Excel name, but with .pdf
End If                          'end of test for file extension

zSaveAs = zPath & zPDF          'filename and folder for pdf file

'EXPORT SELECTED SHEETS TO PDF FILE..
Sheets(zArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=zSaveAs, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Sheets(1).Select

End Sub

It would be easier for us if you could attach a workbook with your Userform.
Then we could adapt your code directly.

zeddy

Reply | Quote

Hi Zeddy,

Thanks a lot and pardon me for the delay in response.

This works great.

Reply | Quote

Hi gvfos

You are pardoned.
And thanked, for letting us know that it worked OK
It is always good to hear back!

zeddy

Reply | Quote

Hey Y’all,

This thread got me thinking wouldn’t it be handy to have a general purpose routine for selecting sheets? So I thought I’d write one.

So you run the code and get a multi-select list box.
40461-MultiSelect-ListBox

When you exit you get an array that contains only the selected sheet names which can then be passed to any other routine to operate on here’s the output from the test routine I wrote:
40460-SelectedSheets

Or if the user doesn’t make a selection.
40462-NoSelection

Form Code:

Code:

Option Explicit

'*** This UserForm requires a Public Variable to be ***
'*** declared in the main module as follows:        ***
'*** Public zSelectedSheets() As String             ***

Private Sub UserForm_Initialize()

    Dim sht        As Object
    Dim iShtCntr   As Integer
    
    ReDim z(1 To ActiveWorkbook.Sheets.Count, 1 To 2)
    
    For Each sht In ActiveWorkbook.Sheets
       lBoxShtNames.AddItem sht.Name
    Next sht

End Sub   'UserForm_Initialize


Private Sub cmdExit_Click()

   Dim lShtCnt  As Long
   Dim lCurItem As Long
   Dim lSelCnt  As Long
   
   lSelCnt = 0   '*** If zero at end NO Items Selected! ***
   lShtCnt = lBoxShtNames.ListCount
   ReDim zSelectedSheets(lShtCnt)
   
   For lCurItem = 0 To lShtCnt - 1
      If lBoxShtNames.Selected(lCurItem) Then
        lSelCnt = lSelCnt + 1
        zSelectedSheets(lSelCnt) = lBoxShtNames.List(lCurItem)
      End If
   Next lCurItem
   
   If lSelCnt = 0 Then
     MsgBox "No Sheets were selected by the user!", _
            vbOKOnly + vbInformation, _
            "No User Selection"
   End If
   
   ReDim Preserve zSelectedSheets(lSelCnt)
   Unload Me
   
End Sub    'cmdExit_Click

Test Code:

Code:

Option Explicit

Public zSelectedSheets() As String

Sub TestufSelectedSheets()

    Dim iShtCntr   As Integer
    Dim iCntr      As Integer
    Dim zMsgString As String
    
    ufSelectSheets.Show
    
    iShtCntr = UBound(zSelectedSheets)
     
    If iShtCntr = 0 Then Exit Sub
    
    For iCntr = 1 To iShtCntr
       zMsgString = zMsgString & _
                    "Selected Sheet #" & Format(iCntr) & ": " & _
                    zSelectedSheets(iCntr) & vbCrLf
    Next iCntr
    
    MsgBox zMsgString, vbOKOnly + vbInformation, _
           "Selected Sheets:"

End Sub      'TestufSelectedSheets

Test File: 40463-VBA-Excel-Function-Select-Sheets

Note: You’ll need to Export the Form out of the test file above. Then Import it into any workbook you want to use it in. It will bring both the form & the code but NOT the test code module!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

Nice Form!
Useful – added it to my box of tricks.

Try this:
1.Click your [Select Sheets] button, choose 4 sheets, press the form [Exit] button.
Result: message box shows the four selected sheets.
2.Now Click your [Select Sheets] button, choose 6 sheets, click the form close red button [x] in top-right corner.
Result: message box shows the previous four selected sheets.

To fix this, add this to the code sheet for the Form:

Code:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then  'red button [x] clicked
Unload Me       'unload the form
End             'discontinue all further processing
End If
End Sub

zeddy

Reply | Quote

Zeddy,

Nice catch! I often use similar code to prevent a user from exiting Excel that way but didn’t think about here.

I actually prefer:

Code:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

   If CloseMode = vbFormControlMenu Then  '*** Red button [x] clicked.   ***
     Cancel = True                        '*** Force use of Exit Button! ***
   End If
   
End Sub

Forcing the user to use my form button(s)…probably should add a Red Cancel button though.

Thanks again! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

If you disable the red [x] close button, then, yes, you should probably have a [Cancel] button.
But I like the red [x] cancel method! Otherwise I would hide it on the Form!
Your method is a neat way to disable the [x]

Now, about your choice of Green for your exit button – do you have ancestors from the Emerald isle??

zeddy

Reply | Quote

Crop removal, field plan, crop nutrient inventory, splits, seed, fert prices and total tons, balance, split tons, bookkeeper, Chem bookings, landlord 1, 2,3,4,5,6,7, field 1, etc all the way to field 30. If person fills out a cell in crop removal, it fills out a cell on field plan. There are multiple cells on field plan tab to fill out which fills cells in splits tab and I in fert prices tab automatically. Need to fill out landlord names on splits tab which fills out other cells in other tabs. Would it help for me to send you the spreadsheet so you can see it?

Reply | Quote

Hi

It would be better if you could attach a sample file here in the Lounge.
That way, others could contribute to helping you.
You can remove any sensitive data from the sample file.

zeddy

Reply | Quote

Hi. I tried to upload it but it says it is too big to attach. is there another option to do that?

Reply | Quote

Hi

How big is the file?

zeddy

Reply | Quote

Hi

If your filesize is less than 8Mb, we can post it to the Lounge by cheating.
Just make a copy of the file and change the filename to sample.pdf
Ignore any warning message you will get about changing file extensions.
We are changing the file extension deliberately, to camouflage the Excel file as a pdf file
NOTE:
Max size for posting a zip file here is 5.75MB
Max size for a pdf file is 8.11MB

zeddy

Reply | Quote

Hi. It is less than 8mb as it about 2.34. I saved it as a PDF but it only has 1 page that it will include and not the rest of the tabs. How do I include all the tabs?

Reply | Quote

Open the Excel file then save with the same name except save it as an .xlsb (Excel binary file). Now, rename the file with the same name but type in an extension of .xlsx and ignore warnings. zip it then upload it.

HTH,
Maud

Reply | Quote

thanks for the help. i attached the zip file.
46008-Sample-Farmer-with-30-fieldsvs2

Open the Excel file then save with the same name except save it as an .xlsb (Excel binary file). Now, rename the file with the same name but type in an extension of .xlsx and ignore warnings. zip it then upload it.

HTH,
Maud

Reply | Quote

Hi

The attached file is really [Sample Farmer with 30 fields-zeddy1a.xlsb]
But we can’t attach .xlsb files here.
So we trick the system by pretending it’s a pdf file.

..so save it, and then rename it by changing the filename from
Sample Farmer with 30 fields-zeddy1a.pdf
to
Sample Farmer with 30 fields-zeddy1a.xlsb

In this file, I’ve added a vba routine to print the Crop sheets.
see sheet [Field Plan]

This uses two named cells on the sheet [countCrop1] and [countCrop2]

see if this does what you need.

zeddy

Reply | Quote

Hi. Thanks for working on this. It would not let me open it as it said “failed to load pdf” and gave an option to reload. so i clicked that a couple of times and still did not work. what am i doing wrong?

Hi

The attached file is really [Sample Farmer with 30 fields-zeddy1a.xlsb]
But we can’t attach .xlsb files here.
So we trick the system by pretending it’s a pdf file.

..so save it, and then rename it by changing the filename from
Sample Farmer with 30 fields-zeddy1a.pdf
to
Sample Farmer with 30 fields-zeddy1a.xlsb

In this file, I’ve added a vba routine to print the Crop sheets.
see sheet [Field Plan]

This uses two named cells on the sheet [countCrop1] and [countCrop2]

see if this does what you need.

zeddy

Reply | Quote

Hi

Read my post again.
It is NOT a pdf file.
So you cannot open it as if it was a pdf file.
It is really an Excel .xlsb file.
So, save the file first. And then change the file extension from .pdf to .xlsb (ignore any warnings about changing file extensions)

zeddy

Reply | Quote

i don’t know how i did it but i did get it to xls. I will look at it tonite to see if it prints correctly which it probably does. Thank you very much for the help as i am a rookie at this!

Reply | Quote

Hi. I noticed on the field plan tab that it has print 1 and print 2 and to the right it says 13 crop 1 count and 13 crop 2 count. Which is correct.
When I hit the print button for print crop 1 – it says printing 13 of these. also, when I hit print button for print crop 2 – it also says the same. But it only prints the first 2 of each. how can that be fixed?

Reply | Quote

Hi

I’ll have another look – it worked OK on my system, so it may be that my setup is different from yours.
What Excel version are you using?
What Printer are you using?

zeddy

Reply | Quote

Hi tex..

We can include other ‘standard’ sheets to be printed, if required.
Let us know what you need.

zeddy

Reply | Quote

Hi.
I am using Excel 2016 and print with a HP OfficeJet 4630.

Reply | Quote

Hi

I haven’t worked out why it only printed the first 2 of each as you reported.

I tried this on another PC with Excel2016 and, to save paper, changed the vba routine to ‘print-preview’ rather than print the sheets. Surprisingly (to me) it allowed me to preview two sheets and then gave me the ‘finished’ message! Weird!

So I thought it might be something to do with the sheet print settings being different on some of the sheets.
I selected all of the Field sheets (as a group), and then set the Print-Page Setup to a value of ‘adjust to 60% normal size’ i.e. made the scaling the same for all of these sheets.

To simplify things, I have posted an updated sample file here which is in .xlsm format.
I managed to get the file size under the 1MB limit for this Lounge, by deleting rows and columns on the Field sheets.
I added a sheet named [B]Product[/B] which has a named list of products that can be used for data validation dropdowns.

I then modified the routine to show a ‘print-preview’ for the [Print Crop …] routines.
Have a try with this sample file.

zeddy

Reply | Quote

Hi Zeddy. i went to the attached spreadsheet, clicked print for the 1 crop, it did let me preview on 13 fields(which it did not let me do before), then clicked print, it printed the first 2 fields again(printed field A then field B(which is strange since B is a crop 2)). Also, not sure if this helps but i had to click the X each time to close all 13 fields.
I then clicked print for the 2 crop and it print previewed all 13 fields for this crop but printed only field B(which is a crop 2). i also had to click the X each time to close each field.
i like the idea of a Product sheet for drop downs.

Hi

I haven’t worked out why it only printed the first 2 of each as you reported.

I tried this on another PC with Excel2016 and, to save paper, changed the vba routine to ‘print-preview’ rather than print the sheets. Surprisingly (to me) it allowed me to preview two sheets and then gave me the ‘finished’ message! Weird!

So I thought it might be something to do with the sheet print settings being different on some of the sheets.
I selected all of the Field sheets (as a group), and then set the Print-Page Setup to a value of ‘adjust to 60% normal size’ i.e. made the scaling the same for all of these sheets.

To simplify things, I have posted an updated sample file here which is in .xlsm format.
I managed to get the file size under the 1MB limit for this Lounge, by deleting rows and columns on the Field sheets.
I added a sheet named [B]Product[/B] which has a named list of products that can be used for data validation dropdowns.

I then modified the routine to show a ‘print-preview’ for the [Print Crop …] routines.
Have a try with this sample file.

zeddy

Reply | Quote

Hi

Try this attached version.
The pre-defined print setup range for any [Field x] worksheet tab would ‘normally’ print 2 pages for that worksheet.

The first page relates to Crop 1, and the 2nd page relates to Crop 2. So, if there is only one crop in the Field, we just need to print either just Page 1, or just Page 2, depending on what the crop is. That is what the vba print routine is attempting. For fields that are of type Crop 1, it will print the first page only; for fields that are of type Crop 2, it will print the second page only.

zeddy

Reply | Quote

it worked! THANKS for the Help!!

Reply | Quote

Not to dig up an old thread, but this has been extremely helpful. I’ve used the original code and it does a good portion of what I need, however I’m looking to have the script save each worksheet as a different file using the name of the sheet as well a some other items that I’ll insert (i.e. Format(Now, “ddmmyyyy HH:mm”)). Thanks in advance!

Reply | Quote

Hi ..jedi

Welcome to the Lounge as new poster!

..’dig up’ an old thread ..about crops in the field. I like that!
I will have a look at this after my (ploughman’s) lunch.

zeddy
..and may the mass times acceleration be with you

Reply | Quote

That is fantastic. I’m attempting to modify some of the code on my end based on some other snippets I’ve gathered but haven’t gotten it to quite work yet. Thanks in advance for the help!

Reply | Quote

Hi ..jedi

You could adapt this code as required.
It will save worksheets as new files if the tab colour is red (and if the sheet is visible).

Code:

Sub exportRedSheets()                           'v1a

Set zSource = ThisWorkbook                      'define shortcut

zCount = 0                                      'iniialise counter

'*********************************************
'Turn settings OFF for speedup..
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'*********************************************
'Create new folder to save the new files in..
'*********************************************
zFolder = Format(Now, "mmddyyyy")               'e.g. "02102017"
zFolderName = zSource.Path & "" & zFolder      '...2102017
On Error Resume Next                            'skip if folder already exists
MkDir zFolderName                                'create folder
On Error GoTo 0                                 'reset error trap
'*********************************************
'define filename timestamp..
zTimestamp = "@" & Format(Now, "yyyy-mm-dd hh-mm-ss") 'e.g. "@2017-02-10 21-30-17"
zSuffix = zTimestamp & ".xlsx"                  'e.g. "@2017-02-10 21-30-17.xlsx"
'*********************************************

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each z In zSource.Worksheets                'loop through ALL worksheets
If z.Visible = True Then                        'sheet is visible, so..
If z.Tab.Color = vbRed Then     '<< check sheet Tab colour; adjust as required
z.Copy                                          'sheet Tab colour is RED
Set zNew = ActiveWorkbook                       'shortcut for new workbook
zNew.SaveAs zFolderName & "" & zNew.Sheets(1).Name & zSuffix    'save the new workbook
zNew.Close False                                                'close new workbook
zCount = zCount + 1                             'increment counter
End If                                          'end of check for Tab colour
End If                                          'end of check for visible sheet

Next z                                          'process next sheet
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


'*********************************************
'Turn settings back ON..
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
'*********************************************
'Display completion messge..

MsgBox "You can find the " & zCount & " files in " & zFolderName


End Sub

If convenient, please let us know if this works OK.
If not convenient, let us know anyway.

zeddy

Reply | Quote

I’ll start working on this now. I’m going to add in the PDF instructions along with this to see if I can’t get it to work!

Reply | Quote

..you mean
” to see if I can get it to work! ”

zeddy

Reply | Quote

Hi ..jedi

..here’s the adapted code to export the sheets as PDF files (if tab colour is red)

Code:

Sub exportRedSheetsAsPDF()                      'v1a

Set zSource = ThisWorkbook                      'define shortcut

zCount = 0                                      'iniialise counter

'*********************************************
'Turn settings OFF for speedup..
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'*********************************************
'Create new folder to save the new files in..
'*********************************************
zFolder = Format(Now, "mmddyyyy")               'e.g. "02102017"
zFolderName = zSource.Path & "" & zFolder      '...2102017
On Error Resume Next                            'skip if folder already exists
MkDir zFolderName                                'create folder
On Error GoTo 0                                 'reset error trap
'*********************************************
'define filename timestamp..
zTimestamp = "@" & Format(Now, "yyyy-mm-dd hh-mm-ss") 'e.g. "@2017-02-10 21-30-17"
zSuffix = zTimestamp & ".pdf"                  'e.g. "@2017-02-10 21-30-17.pdf"
'*********************************************

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each z In zSource.Worksheets                'loop through ALL worksheets
If z.Visible = True Then                        'sheet is visible, so..
If z.Tab.Color = vbRed Then     '<< check sheet Tab colour; adjust as required

zSaveAs = zFolderName & "" & z.Name & zSuffix

z.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=zSaveAs, Quality:=xlQualityStandard, _
    OpenAfterPublish:=False

zCount = zCount + 1                             'increment counter
End If                                          'end of check for Tab colour
End If                                          'end of check for visible sheet

Next z                                          'process next sheet
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


'*********************************************
'Turn settings back ON..
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
'*********************************************
'Display completion messge..

MsgBox "You can find the " & zCount & " files in " & zFolderName

End Sub

zeddy

Reply | Quote

Hi ..jedi

..if you get stuck with the PDF instructions you could try using Adobe Wan Kenobi

Reply | Quote

I had some droids trying to distract me, but got all but one portion working. I’m trying to create subfolders as well as the main folder. Here’s what I have:

Code:

‘*********************************************
‘Create new parent folder to save the new files in..
‘*********************************************
zParentFolder = Format(Now, “MMMM YYYY”)               ‘e.g. “February 2017”
zParentFolderName = “C:UserstestDocumentstest1” & “” & zParentFolder      ‘…February 2017
On Error Resume Next                            ‘skip if folder already exists
MkDir zParentFolderName                                ‘create folder
On Error GoTo 0                                 ‘reset error trap
‘*********************************************
‘Create new sub folder to save the new files in..
‘*********************************************
zSubFolder = Format(Now, “mmddyyyy”)               ‘e.g. “02132017”
zSubFolderName = “C:UserstestDocumentstest1” & “” & zParentFolder & “” & zSubFolderName      ‘…2132017
On Error Resume Next                            ‘skip if folder already exists
MkDir zSubFolderName                                ‘create folder
On Error GoTo 0                                 ‘reset error trap
‘*********************************************

The parent folder is created perfectly fine, and all of the tabs save the way I need them. I just can’t get it to create the subfolders.

Reply | Quote

Hi ..jedi

Did good did you.

..let me have a think about using FSO to create those subfolders
..I must unlearn what I’ve learned
..patience you must have until tomorrow

zedi

Reply | Quote

I’ve created a helper tab that has the dates. B1 is the current month formatted as “MMMM YYYY” and B2 is “mmddyy”. I’m trying to think of how to use those as placeholders for a reference.

Reply | Quote

Jedi,

Try this:

Code:

'*********************************************
'Create new parent folder to save the new files in..
'*********************************************
zParentFolder = Format(Now, "MMMM YYYY")               'e.g. "February 2017"
zParentFolderName = "C:UserstestDocumentstest1" & "" & zParentFolder      '...February 2017
[COLOR="#008000"]'Now zParentFolder =[/COLOR] [COLOR="#0000FF"]C:UserstestDocumentstest1February 2017[/COLOR]
On Error Resume Next                            'skip if folder already exists
MkDir zParentFolderName                                'create folder
On Error GoTo 0                                 'reset error trap
'*********************************************
'Create new sub folder to save the new files in..
'*********************************************
zSubFolder = Format(Now, "mmddyyyy")               'e.g. "02132017"
zSubFolderName = "C:UserstestDocumentstest1" & "" & zParentFolder & "" & zSubFolderName      '...2132017
[COLOR="#008000"]'Using zParentFolder from above the above resolves to:
'C:UserstestDocumentstest1C:UserstestDocumentstest1February 20172132017[/COLOR]
'Change to:[SIZE=4][COLOR="#B22222"] zSubFolderName = zParentFolder & "" & zSubFolderName[/COLOR][/SIZE]
On Error Resume Next                            'skip if folder already exists
MkDir zSubFolderName                                'create folder
On Error GoTo 0                                 'reset error trap
'*********************************************

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Well, that’s weird. Now, it creates the parent folder in the correct directory, but doesn’t save the files there. Instead, it creates another parent folder in the Documents folder and stores all of the files there. Good catch on the duplication of paths!

*EDIT* Got it figured out! Here’s my final code:

Code:

Sub AutomatePDF()                      ‘v1a

Set zSource = ThisWorkbook                      ‘define shortcut

zCount = 0                                      ‘iniialize counter

‘*********************************************
‘Turn settings OFF for speedup..
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
‘*********************************************
‘Create the folders to save the new files in..
‘*********************************************
Dim objRow As Range, objCell As Range, strFolders As String

For Each objRow In Sheets(“Job Helper”).UsedRange.Rows
    strFolders = “C:UserstestDocumentstest1”
    For Each objCell In objRow.Cells
           strFolders = strFolders & “” & objCell
    Next
       Shell (“cmd /c md ” & Chr(34) & strFolders & Chr(34))
    Next
‘*********************************************
‘define filename timestamp..
zTimestamp = ” ” & Format(Now, “mmddyyyy”) & ” at ” & Format(Now, “hhmm”) & ” (NG)” ‘e.g. “02132017 at 1130”
zSuffix = zTimestamp & “.pdf”                  ‘e.g. “20132017 at 1130 (NG).pdf”
‘*********************************************

‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Z In zSource.Worksheets                ‘loop through ALL worksheets
If Z.Visible = True Then                        ‘sheet is visible, so..
If Z.Tab.ColorIndex = xlColorIndexNone Then     ‘check sheet Tab color; if no color then save

zSaveAs = “C:UserstestDocumentstest1” & “” & Sheets(“Job Helper”).Range(“A1”) & “” & Sheets(“Job Helper”).Range(“B1”) & “” & Z.Name & zSuffix

Z.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=zSaveAs, Quality:=xlQualityStandard, _
    OpenAfterPublish:=False

zCount = zCount + 1                             ‘increment counter
End If                                          ‘end of check for Tab colour
End If                                          ‘end of check for visible sheet

Next Z                                          ‘process next sheet
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


‘*********************************************
‘Turn settings back ON..
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
‘*********************************************
‘Display completion messge..

MsgBox “Finished processing ” & zCount & ” files ”

‘May the force be with you
‘Thanks to the folks at WindowsSecrets for the help!

End Sub

Reply | Quote

/showthread//183128-how-to-use-vba-variables-in-powershell-script-while-running-it-from-VBA?p=1096318#post1096318

Will you be able to help me with this ?

Reply | Quote