Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Vba: Set Print Format Across Multiple Sheets


I'm trying to write a macro to select all the Sheets in a Workbook, and set some properties [Auto ColumnWidths, Landscape, and Fit to 1 page wide] for all of them.
I don't know the names of the sheets, nor how many there will be - this part is tagged on the end of a long macro that creates new files and pastes various data into them. The code below only seems to work on the Active sheet - not any of the others selected. Curiously, I can set a specific column width for all sheets, but not Auto Widths.

With ActiveWorkbook
Sheets.Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End With

Sheets.Select
ActiveWindow.Zoom = 80 ' This line works!
Cells.Select
Selection.RowHeight = 13.5
Selection.EntireColumn.AutoFit
Columns("C:C").Select
Selection.ColumnWidth = 34 ' This line works
Range("A1").Select


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Print Multiple Ranges From Multiple Sheets Userform
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:

Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub

The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.

So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.

Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With

I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.

My current muck ups are .....

1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?

2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=

3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.

I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.

To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.

View Replies!   View Related
Print Multiple Sheets At Once
I have a workbook consisting of about 20 worksheets. I have VBA code that prints any sheet that is used but skips any pages that are not used. This works fine but if there are more than one user printing sheets at the same time on our network printer, the pages get all mixed together and they have to pick through the stack to find their pages. Is there a way that the used pages can be assembled and then all printed as one printjob so that each users pages will all print together simplifying the sorting process.

View Replies!   View Related
Vba To Print Multiple Sheets
I've been feverishly searching for a pre-existing macro to print specific sheets assigned manually by the macro to the windows default printer (or better still bring up the printer dialog box and you can select which printer!)

The macro is attached to an image on Sheet 3 called "Details"
The respective sheets I want to print in succession are Sheet 2 "Letter Of Estimation", Sheet 6 "Labour & Equipment SOR" and Sheet 7 "Labour Only SOR".

Can anyone help me with this please? I've been searching for a while and come up with various bits of code that perform intricate loops based on user input, and other unrelated tasks I can't seem to ween out of the code by myself.

View Replies!   View Related
Print Multiple Sheets To Pdf File
I am working on a userform in which user can select diffrent sheets for print.
I want all selected sheets to be printed in a single pdf and it could be great if it not ask for a path to save file.


View Replies!   View Related
Macro: Print Ranges From Multiple Sheets
I am trying to write what I thought would be a simple macro to print out specific areas of my worksheets. I have shown the code below; the line causing the problem I have highlighted in RED. I am getting the following error message: "Select method of range class failed".

Reading other posts here. I think this may have something to do with the macro being assigned to a command button in one worksheet (AY114) and I am trying to get the macro to run on both the worksheet that the command button is in (AY114) as well as another worksheet (AY062).

Sub CommandButton1_Click()
Range("J2").Select
Sheets("AY114").Select
Range("A4:J53").Select
ActiveSheet.PageSetup.PrintArea = "$A$4:$J$53"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=45
Range("A56:M151").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$M$151"......................

View Replies!   View Related
Print Multiple Sheets Based On Criteria
I am trying to find a way to select and print multiple sheets based on a criteria or a list. I have a large worksheet with many sheets. Each sheet falls into one of three categories, and I want to be able to automatically print all tabs in each category. I have all of the sheets rolled up into a summary where I have access to all of the sheet name and print criteria.

View Replies!   View Related
Format Multiple Sheets At Once
I have 14 worksheets in a workbook.

All sheets are identical in that they have the same headings within the same range.

I want to apply formatting (bold some text, apply borders, center, etc.), but to all the sheets at once. I know I can do this with a simple loop, but I was wondering if there is a more efficient way of doing it.

I've been playing with
sheet( array(sheet1.name, sheet2.name))

I don't think I have correct syntax or use using the the above.

I tried coding what I would do if I was performing this manually, which is to select all the sheets and apply the formatting to sheet1. Then all the sheets would have the formatting.

View Replies!   View Related
Copy & Append To Report, Rows From Multiple Sheets If Any Cell Meets Format Color
I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet.

On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.

View Replies!   View Related
Print Multiple Pages With Single Print Job
I have a spreadsheet with 28 visible tabs. When I select all tabs and print, I get 3 cover (or banner) pages...I like having the first banner page, but the other two are in the middle of my report. I assume excel is sending 3 print jobs and I'm not sure why.

I've seen a couple of posts regarding this issue when using VBA, but I'm not using VBA...just selecting all the sheets and sending a single print job, but get 3 banner pages.


View Replies!   View Related
Print Multiple Worksheets As 1 Print Job
I'm using VBA code to select several worksheets at once, and print them. The code runs fine, but the result is multiple print jobs, not one job. This is OK when printing to the printer...but undesireable if you want to print to a pdf file, for example.

I've searched the site and found some references to this possibly being an excel problem. Is that true?

I've also checked the page setup setting to make sure that the print resolutions are all the same...that doesn't appear to be the problem either.

View Replies!   View Related
Print 2 Sheets
I have 2 sheets that I want to print off, these are called Questions & Answers.
I have the following code to print off Answers, how do I amend this to print off Questions & Answers?

'Print!
.PrintOut
.Columns("A:A").Select
Selection.Font.ColorIndex = 1
.Range("A1").Select
.Visible = True
End With
Sheets("SEARCH").Select
End Sub

View Replies!   View Related
Print Array Of Sheets
I know I can print sheets as such:

Sheets(Array(2,4,5,8,9)).printout

I want to populate an array list based on sheet names, but cannot figure out the last line for syntax:

Dim ws As Worksheet, arr() As String
ReDim arr(0 To Sheets.Count-1)
For Each ws In Worksheets
If InStr(1, ws.Name, "Crp-") Or InStr(1, ws.Name, "Reg-") Or InStr(1, ws.Name, "Grp-") Then
arr(counter) = ws.Index
counter = counter + 1
End If
Next ws
Sheets(arr()).printout

As I debug it the array is filled with the proper sheets, but I for some reason cannot figure out the syntax for this line? Does it have anything to do with me declaring the array as a string and using integers as the index?

View Replies!   View Related
Only Print Active Sheets
Is there any way to only print active sheets

i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets

i would like to print sheet 1 if cell D19 has data in it
i would like to print sheet 2 if cell D61 has data in it
i would like to print sheet 3 if cell D103 has data in it
i would like to print sheet 4 if cell D145 has data in it

View Replies!   View Related
Print All Sheets Without Opening WB
On right clicking a closed file and if print is clicked, it is opening the file and printing the activesheet only and then closing the file. I want that it should print all the sheets continously and not only the Active sheet. How ths can be done?

View Replies!   View Related
Macro: Print Sheets If Used
I'm in the process of placing a button on sheet#1 that will print any of the 5 sheets in the workbook that have been used... a sheets use is determined by cell I53 being > 0...

I've initially used this code to test I53, but it shows all sheets, regardless of I53 value...

Sub PrntUsedShts()
Dim ws As Worksheet

For Each ws In Worksheets
If Range("I53").Value > 0 Then
ws.PrintPreview
End If
Next ws
End Sub




Ideally, the finished code will send all selected sheets collated as a single print job...
Should I be looking at putting test positive sheets into a sheet array?, then printing the sheet array?

Once again, your valued input is greatly appreciated...

View Replies!   View Related
Print Chosen Sheets
I have a workbook containing over 75 sheets. I would like to print only a select few of those sheets using a macro. I did download the "sheets to print" workbook submitted by Dave Hawley ,and have tried (in vain) to adapt the code so that it does not display ALL of the workhseets in the userform box. I want to limit the sheets displayed in this box to onnly those that I name.

View Replies!   View Related
Print Visible Sheets Only
I use templates (.xlt) to generate reports for different organizations. In some cases certain pages in the reports are hidden.
When printing I get blank pages where pages are hidden. How do I not get these pages printed at all?
I do use page breaks to format the reoprts.

View Replies!   View Related
Transferring Print Format Info Via VBA
I am using VBA to copy and paste multiple sheets from one workbook to another. The workbook where the sheets are being pasted has no prior formatting. I want to transfer the *print* formats from the first to the second sheet via VBA. One key thing to note is that the print formats are constantly being changed in the first workbook. So I think I need code to "grab" the formats, then transfer them to the second workbook.

View Replies!   View Related
Print Sheets By Cell Reference
The following, within a macro prints the sheets requested:

Sheets(Array("Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

But I need to print only sheets the have data in cell A6. This is due to the footer page numbers (Page Of Page) I only need to print sheets with data in A6 of each sheet.


View Replies!   View Related
Using A Macro To Print All Sheets With A Twist
using a macro to print all sheets in a workbook. During this process each sheet has a print area to print as well. A button will be placed on all the sheets. No matter which sheet you are on I would like it to print all sheets. The code I have works I think, but I would like to not have to go by sheet names in case there are some added later, etc. Also, is there a way to do this without going to each sheet and the user seeing all this? Would like to do it in the "background" so to speak.

Sheets("sheet1").Select
'Range("B2").Select
Range("H1:K1").EntireColumn.Hidden = True
Range("O1:S1").EntireColumn.Hidden = True
Range("V1:W1").EntireColumn.Hidden = True
Range("Y1:AC1").EntireColumn.Hidden = True
Range("L1:N1").EntireColumn.Hidden = False
'Print range column A thru AD
ActiveSheet.PageSetup.PrintArea = "A1:AD84"
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.Zoom = 58
' .FitToPagesWide = 1
.BlackAndWhite = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, collate:=True

Range("H1:K1").EntireColumn.Hidden = False
Range("O1:S1").EntireColumn.Hidden = False
Range("V1:W1").EntireColumn.Hidden = False
Range("Y1:AC1").EntireColumn.Hidden = False
Range("L1:N1").EntireColumn.Hidden = True
Range("A1").Select

View Replies!   View Related
Macro To Print Selected Sheets ...
in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.

View Replies!   View Related
Print Sheets Checked In ListBox
I have 25 pages in a workbook/spreadsheet. On the front page I want to insert a macro button which when clicked, displays a message box presenting a list of pages in the workbook with tickboxes next to them - so the user can tick which pages he/she wants to print. I also want a 'select all' tickbox which when clicked - selects all the pages. However, I do not want a tickbox for all 25 pages in the workbook - only a selection of them. how to write this code so I can specify which pages will be displayed in the print tickbox screen that appears when the macro button is clicked?

View Replies!   View Related
Import, Format, Convert, Print VBA Module
I was wondering if its possible to build a module that would do the following: a) import a txt file to excel formatting it as text.

b) then in column D would remove the preceding space.

c) then convert data in D according to a separately kept Conversion table (or conversion table could be integrated into the code) and print conversion results into column J.

d) the last step is to print/copy columns A and J so that it looks like the final table in Sheet2.
Here are files attached.

sample data.txt
sample data.xls
conversion table.xls

To summarize I need to go from a txt file like the one attached and arrive at the table in Sheet2 of xls file attached.

View Replies!   View Related
Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View Replies!   View Related
Tweaking Dynamic Print Range Across 7 Sheets
In a school gradebook, there are 7 sheets for grading. In each sheet, student names and other info are in cols B through E, and headings are on rows 11 & 12. Grades are in cols Z11:AB..., AC11:AE..., and AO11:AQ..., where ... would be the last row of grades associated with a student.

My obstacle is that the grading cols contain formulas down to row 80, so Range(Selection, Selection.End(xlDown)).Select goes down to row 80 instead of stopping on the row of the last student's name on that sheet.

Another obstacle is that student records are on every other row (odd rows) starting on row 13. Their grades are in the same row.

View Replies!   View Related
Macro To Loop Through Sheets And Print Them Out Using An Array
I have created a macro that loops through and creates an array of the visible sheets. Now I would like for it to print each of those sheets out. I think my main mistake is in my declaration of the array type, since I have not worked with arrays much before.

how to make the following code operable. Currently when I run it I'm getting a "Run-time error (9): Subscript out of range" error.

View Replies!   View Related
Print To PDF Selected Sheets From An UserForm Listbox
I have created a custom Menu (excel add-in) to make my work easy in excel. My problem is to print only selected sheets from Workbook in one PDF file, for that I've created a Userform with 2 listboxes, add sheet and print buttons. In the first listbox are listed all the sheets and in the second listbox are the sellected sheets to be printed. What I've succeded so far is to print selected sheets, but it creates one PDF file for each sheet, only if I put my code in workbook and not in Menu add-in (.xla file). As PDF Printer I use PDF reDirect Pro v2.

View Replies!   View Related
Copy From Multiple Sheets (26), PASTE To 1 Sheet From 26 Sheets
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.

I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.


View Replies!   View Related
Selecting Sheets Meeting A Criteria For Print Preview
I have a print userform where I let the user print a summary package of sheets. I am trying to allow the user to see a print preview of certain sheets. The first one is always the "Bid Summary" sheet (sheet 2). The rest are a dynamic number of sheets with numeric names starting at 1. Now I need a easy way for me to allow the user to see a print preview of the Bid Summary, and then all the sheets right afterwards. I cant use sheets("1","2",...).select because it wont select a ever changing number of sheets. Really it is a problem of selecting a varied number of sheets in VB.

View Replies!   View Related
Print Selected Sheets 1004 Application Defined Or Object
i have found this code on the net but can only get the control sheet to work when i run macro "PrintSelectedSheets" i get a run time error 1004 application defined or object error i have put the code in the workbook object but having problems, it seems to be because the sheets that i am trying to print are hidden can this code be edited

Sub PrintSelectedSheets()
Dim i As Integer
i = 2

Do Until Sheets("Control Sheet"). Cells(i, 1).Value = ""
If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select
'ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
i = i + 1
Loop
End Sub

This code seems to work ok

Sub CreateControlSheet()
Dim i As Integer

On Error Resume Next 'Delete this sheet if it already exists
Sheets("Control Sheet").Delete
On Error Goto 0...........................................................

View Replies!   View Related
Creating A Macro To Compare 2 Sheets In A Workbook And Print The Differences To A 3rd Sheet
Creating a macro to compare 2 sheets in a workbook and print the differences to a 3rd sheet.

Each sheet will have the same number of fields, 5 columns with the header in the first row.

All values in the cells are integer except for the last field which will be a character.

The key is the value in the 2nd column. If it's not in the other sheet, then it's a new record. If it's a new record then highlight it a color depending on what sheet contains the new record. Now if the key is the same in both sheets, then check the other columns to see what's different. If there is a difference, print the record for both sheets in the third sheet and highlight the differences. I attached a sample of what I want.


View Replies!   View Related
Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.

View Replies!   View Related
Count Unique Logs With Multiple Conditions Of Multiple Sheets
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.

This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.

The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.

Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))

Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.

2. I don't know how to make it work across several sheets.

This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.

SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))

View Replies!   View Related
Adding Multiple Cells From Multiple Sheets With Sumif Function
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:

A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................

View Replies!   View Related
Format All Sheets The Same
On a daily basis I receive a workbook with one single spreadsheet. I then break it into multiple tabs (~ 40 to 50 different sheets). I am trying to apply the same formating as my data sheet (same column width, same row width, landscape for printing, same margins for printing, same headers/footers, etc).

I was originally going to use the macro recorder but ran into the issue that the sheet names are different each day. I tried the following bit of code as a start but keep getting an error.

View Replies!   View Related
Using 2 Variables To Return Multiple Items From Multiple Sheets
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.

I find writing out what I want helps some times so let me try it here.

So my variables are:

Product (there are 22 products)
Supply Less than (inset number)

These are the two criteria I want to use to produce a result.

The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.

What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.

The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.

View Replies!   View Related
Track Changes On Multiple Selected Ranges On Multiple Sheets
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.

is there a way to track changes on multiple selected ranges on multiple sheets

View Replies!   View Related
Copying VB To Multiple Sheets In Multiple Work Books
Ive put some sheet code together that i need copied to 12 sheets (jan to December) in 24 workbooks (each workbook has trhe same sheet names). I dont want to alter the actual content of the Excel sheets, I just need to copy VB code from a template (in VB editor) to the 12 sheets in each of the workbooks. Is this possible to do with VB or do i need some other utility since Im using the VB editor....

View Replies!   View Related
Print Multiple Tabs?
I have 3 sheets in the same workbook that I want to print in 1 PDF report, is there a way that I can do this?

View Replies!   View Related
Print Multiple PDF Files
I have around 100 pdf files. I need to print specific pages from those files (e.g. 5 to 10). I want to build a macro to do this task automatically.

View Replies!   View Related
Print Area - Multiple Selections
Is there a way of selecting several print areas on several worksheets and printing it on ONE page? Selections will not be the same size.

Also is there a way to lock your printareas? I would like to save the spreadsheet and open it up next month and the printareas are still there ready for me to print. It seems to reset after i have printed it

View Replies!   View Related
Code To Print Multiple Pages
I have a macro that cycles through several student ID numbers and prints a progress report for each one. It works great, but I would like to add an option to pick which printer to use at the beginning of the code. In other words, when I run the macro, the printer box opens and I can pick the printer. When I click okay to print, the macro continues.

View Replies!   View Related
Set Print Settings Multiple Workbooks
I have about 100 different workbooks that all need to have the same format and page set up for printing. The different workbooks just all need to be setup to have landscape.

View Replies!   View Related
Combine Multiple Sheets From Multiple Books Into One
I have 6 spreadsheets all within the same folder, these are pretty much identical (rows, colums, sheets within them) apart from the names of the files.

I then have a master spreadsheet within the same folder where I want to combine all the data, from all the sheets within each book (if that makes sense!) apart from the data on the last sheet within each book as this is the reference data, onto one sheet within this master file. If possible I only want to copy rows accross which have complete data too.

So: (names not correct)
From book1.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet
From book2.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet
combine onto masterfile.xls on sheet1.

I have searched on here and can only find how to do it with the first sheet in each workbook, not looping through all the sheets in each book. Please see below.

View Replies!   View Related
Multiple Column Lookup Across Multiple Sheets
After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method.

=IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0)))

It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails.

I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet.

This is how I have modified the formula to suit me..

=INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0))

<<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>


View Replies!   View Related
Comparing Multiple Columns In Multiple Sheets
I'm trying to do is develop a formula to:
- look at one column in sheet 1 and if a value in that column equals a value in another column in sheet2. then, display an X

View Replies!   View Related
Re-format All Sheets In Spreadsheet Except The Three Named
I'm writing a bit of a code that will re-format all sheets in spreadsheet except the three named. I keep on getting compile errors whatever I do. It's driven me mad.

View Replies!   View Related
Print Selected & Multiple Rows To A Form
The file consists of 3 sheets:Orders,Order Form PO1 & Purchase Order. When I mark "x" in the row that I want to print, Order Form PO1 will be displayed with all data in the specific columns. However, it only allows me to select one row at a time. Now, I want to select multiple rows so that it will pass the data to the sheet Purchase Order.

View Replies!   View Related
Setting Multiple Dynamic Print Ranges
So I've tried this a few different ways and every time I get the 1004 error: "Unable to set PrintArea property of PageSetup class"

Basically I have a macro that goes through a workbook to hide certain rows and columns based on some user inputs. After doing this I would like to set up appropriate print areas on certain sheets so that when you go to print anything it comes out clean. I thought this would be easy but I'm stuck.

The Plans variable in the code below is an integer from the user input. Think of this code as grabbing two separate boxes and setting them as print areas, which I've know I can do manually because I've tried it.

What's wrong with the following code?? (I've also tried using the union function here, to no avail)

View Replies!   View Related
Auto Print Based On A Cell Value For Multiple Works
There are 20 worksheets in my workbook. For the first ten, I would like to print out the only sheets in which cell E28 = 0. For the last ten, I would like to print out the sheets in which cell T1 = 0. Each sheet has a unique name.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved