Selecting tabs and printing

[davidathoegh]

Hi all,

I produce a monthly pack which requires multiple worksheets in different excel files to be selected and printed (over 100pages).

What I need to do is activate a workbook, select the required worksheet(s) then print, then go to the next workbook.
For example, open up Report A file, select Sheet1 to sheet ?, print, then go to report B file and so on.

Through recording a macro and reading some threads, I have attempted to use the below code, repeating this code for around 40files which is specific to the file name/tab.

Workbooks("Report 1").Activate
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Sheet1").Select

this works fine, but when the file name changes every month (file is dated), I manually do a find and replace in the code to change the date, then use the macro.

I know there is a better way to do this, but my skills are very basic, which is why I an open to any suggestions available....

I have now typed the file names required in a "Data" tab, where column E holds the report name and column F has the tab name.
e.g.
Report 1 tab1
Report 1 tab2
Report 2 tab1
Report 3 tab1

Etc etc

Any suggestions would be greatly appreciated.

DC

 

[p45cal]

This would require careful editing of the Data tab before each print run, getting the filenames exactly right each time.
Is there something in the non-date part of the file name from which could be derived what sheets you want printed from it? If not, each file could contain data within itself defining which sheets need to be printed from it.

I ask this because there is an easy way of selecting files by browsing for them, selecting multiple files using the usual windows key/mouse combination of Shift or Ctrl and click to select, then processing those selected files. For example, the following snippet will run through selected files, opening and closing them. It can be tweaked to offer up a default directory, amongst other things:

Sub blah()
ListOfFilesToProcess = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", , "Select files to print", , True)
For Each fil In ListOfFilesToProcess
  Set wbk = Workbooks.Open(fil)
  MsgBox wbk.Name & " opened"
  wbk.Close
Next fil
End Sub

 

[davidathoegh]

Hi P45cal,

thank you for your reply, really appreciated. I think you have pushed me in the right direction. I guess what I need tweaked comes from the following code I have come accross:

Dim wb As Workbook
Dim c As Range
Sheets("Data").Select
For Each c In Range("F35:F41").Cells
Set wb = Workbooks.Open(Filename:=c.Value)
Next c

Where the red line needs to be changed to activate the file (as the file is already open).
Next line then select the tab name (in G35:G41),
Print,

Loop.

What Ive written makes sense I think, I am just not sure how to script it? any suggestions?

kind Regards

 

[p45cal]

Untested:

Set wb = Workbooks(c.Value)
'wb.activate 'might need this line.
wb.sheets(c.offset(,1).value).select

 

[davidathoegh]

Thanks again p45cal.

The final line does not seem to work though? It opens the correct file (which is great), but then the macro fails on the red line?

The code currently stands as:

Dim wb As Workbook
Dim c As Range
Sheets("Data").Select
For Each c In Range("D8").Cells
Set wb = Workbooks(c.Value)
wb.Activate
wb.Sheets(c.Offset(0, 1).Value).Select
Next c

Any ideas?

 

[davidathoegh]

I actually was looking at the wrong cell, and have found the issue. Thanks again! true legend!!

Just a case of printing which (almost) works lol.
in the last line do i add another line "Dim d As Range" and change Range at the bottom to d?

Dim wb As Workbook
Dim c As Range
Sheets("Data").Select
For Each c In Range("D8").Cells
'This range is for all Aequitas files
Set wb = Workbooks(c.Value)
wb.Activate
'the next line looks for the tab names - three columns from the file name
wb.Sheets(c.Offset(0, 3).Value).Select
wb.PrintOut Range:=wdPrintCurrentPage

 

[p45cal]

You've used wb.Printout where wb is a workbook, so it should try to print the entire workbook. If there are printareas setup on each sheet, it should only print those areas out. Perhaps if some sheets have no printareas they won't be printed? I've never tried.
Perhaps you're looking for
activesheet.printout
or even
range("zz1:yy99").printout
?

 

[davidathoegh]

P45cal, you hero!

Just used the ActiveSheet.PrintOut which worked a charm,

Can now press one button which prints over 100pages from different files, while i make a cup of tea!

Thanks again for your help!