Macro / Set Dynamic Print Range?
May 6, 2004
I am still having some difficulty in getting a macro to select a print range on a text file imported into excel. All the data pulls into column A which is OK. I'm trying to get my macro to highlight & select the print range by going Home, End Down, Tab right Tab right and then select whatever that highlighted range is. This range will change from file to file.
My macro reads as follows:
Sub PrintToView()
'
' PrintToView Macro
' Macro recorded 5/5/2004 by
'
'
Range("A1").Select
[code]....
View 5 Replies
ADVERTISEMENT
Sep 24, 2013
I've found some code which works to print certain pages with value in cell A1 but I need to print dynamic ranges on some of the sheets as they will have filters on so the rows ranges will be different each time.
So far this is what I have but the dynamic range part is not working:
VB:
Sub Print_All_Worksheets_With_Value_In_A1()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
[Code] ....
View 3 Replies
View Related
Jan 27, 2010
This one seems tricky to me. I have a spreadsheet which is being used to collect data. Depending on what is selected in my drop-down menus in row 8 (columns B-N) my print range needs to change. The goal is to create a button that allows the end user to select the range without having to find the end of the spreadsheet.
- I have attached a grid named “RANGE.xls” that shows the logic I’m looking to automate.
- I have also attached the data collections spreadsheet (FY10 Data Collection Tool_V0.6.xls)
The end users of this spreadsheet are very Excel naive and I’m trying to make this semi-idiot proof.
View 2 Replies
View Related
Dec 17, 2006
I have a sheet with a top portion like a fax cover letter.
Starting at row 17 I merged from B thru L.
This is where the user will type in a message.
The message can extend several pages long, so I merged about 100 rows same as 17.
My sheet only will extend to column N.
There may be instances where the user may leave several rows blank in between what they type.
I am looking for a way to set the print area from A1 to the last row of data and not the last row with formatting.
I have a command button the user is supposed to click when they want to print the sheet.
This is acceptable, but if there was a way around that it would be better.
Note:
I do have code in the sheet that will automatically adjust the row height in the merged cells if the data gets too wide. I do not want to upset that.
I have just recently started to learn VBA, so my knowledge of the various commands and syntax is limited.
This is what I have right now that doesn't work.
Private Sub CommandButton2_Click()
Dim LastCell, DataCells As Range
Dim LastDataRow As Integer
Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
Set DataCells = ActiveSheet.Cells.SpecialCells(xlconstants)
LastDataRow = DataCells.Cells(DataCells.Cells.Count).Row
ActiveSheet.PageSetup.PrintArea = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(LastDataRow, LastCell.Column)).Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
View 9 Replies
View Related
Jul 20, 2007
I have a print range set up that is based on a named range that is an offset formula. The problem is that I would like to set it up as portrait instead of landscape, but if I do that the area to the right doesn't all fit on the page. The print area will always be a certain width, just different hights. I just want to be able to have the print range adjust downwards and still fit the whole area on one page wide.
View 2 Replies
View Related
Oct 26, 2008
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 10 Replies
View Related
Jul 19, 2014
I have a worksheet where the number of rows will change on a weekly basis and I would like to print the sheet out without having to change the print range every week.
I have tried to use the OFFSET function in the print range in print setup but once this has been entered and printed, it changes to the actual print range the OFFSET returns.
View 1 Replies
View Related
Feb 25, 2009
Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction
View 2 Replies
View Related
Jun 10, 2009
setting up a print range macro:
I have a sheet where:
1) column A (rows 1-54) needs to be printed with every printing
2) other columns (b-e) or (f-i) etc need to be printed out with each printing
Is it possible to setup a macro to do this as long as I know which columns need to be printed every time (will be same number of rows every time)
Basically, there is info in column A that needs to be printed every time, then I want to be able to print out a months worth of data (b-e for january) for each printout. It is essentially a calendar that I need to print up a hard copy of.
I know zero about vba and much of what I have found on the internet has only confused me more.
View 14 Replies
View Related
Oct 16, 2006
I would like to have a code that allows user to print. The print area is fixed at A1 to U50 and should be printed in 1 page. The sheet name is called "apple".
View 2 Replies
View Related
Jan 11, 2008
the macro simply copies a master sheet, then re-organizes/re-sorts the raw data in the new sheet. At the end, I'm trying to set a print range to 1 page wide and inifinity pages long, figure out where the end of the fourth page is, then reset the print range to accommodate four pages. In case it matters, the macro starts with a form that allows the user to input a company ticker and name used in output.
The problem is that when I use the debugging step function (very tedious), it works perfectly. But when I run it normally, it locks up for about 30 seconds when setting the print range to 1 page wide, and cuts off the print range vertically at just over 2 pages tall. Brief explanation of a couple variables:
ColLetter is a function as follows:
Function ColLetter(ColNumber As Integer) As String
ColLetter = Left(Cells(1, ColNumber).Address(False, False), 1 - (ColNumber > 26))
End Function
ActiveSheet.PageSetup.PrintArea = "$A$1:$" & ColLetter(j + 3) & a + 7 .............................
View 2 Replies
View Related
Nov 17, 2011
I am trying wihout suceess to create a range to copy in a macro each time the macro loops. This is thus a dynamic range. My failed attempt is listed below. How is this done?
ColumnAdjust=2
For I = 1 To 282
Range ("B5").Select
Range(ActiveCellOffset(0,ColumnAdjust),ActiveCellOffset (0,ColumnAdjust+1))
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
.
.
.
ColumnAdjust= ColumnAdjust=2
Next I
View 4 Replies
View Related
May 28, 2007
I am attempting to create a dynamic range within a macro.
Each week, a list of performance factors is recorded for employees. The macro is about to do an Advanced Filter to create reports based on user-provided criteria. Since there is new data every week, the range used for the Advanced Filter is dynamic. The code for the Advanced Filter refers to a range entitled “Database.” My problem is in defining “Database.”
It is currently 534 rows and 8 columns, starting at A1.
The relevant portion of the macro currently reads:
Sub Check1()
ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=OFFSET('Loan Officer History'!$A$1,0,0, COUNTA('Loan Officer History'!$A:$A),8)"
Range(Database).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"J1:Q2"), CopyToRange:=Range("J5:Q5"), Unique:=False
End Sub
When running the macro, the debugger stops at the line which starts "ActiveWorkbook.Names" and tells me “Run-time error ‘1004’. The formula you typed contains an error.”
View 3 Replies
View Related
Jun 6, 2013
I have a Ranking tab that has the persons name, weeks, months that subtotal into 2 columns, MTD and YTD. I have set up with 2 Macros to sort based on two columns based on their selection of MTD or YTD.
What I did not take into consideration is if a person was added at the bottom of the list.
Is there a way to modify this to include rows that may be added?
VB:
[SIZE=4]CODE HERE[/SIZE]
VB:
[B]Macro1
[/B]Sub SortYTD()
[Code].....
View 8 Replies
View Related
Sep 6, 2007
I recorded this macro based on a column with a drop down window in which data has been filtered .....
View 12 Replies
View Related
Feb 20, 2009
I need a bit of assistance in identifying the dynamic range so that my macro below will work:
View 3 Replies
View Related
Oct 13, 2008
I am looking to run a marco for a selection range, and the selection range could vary in size.
Using the Macro recorded (whilst turning on the relative reference) the Macro runs for a defined number of cells. I would like to run certain Macro, for different ranges of cells.
View 9 Replies
View Related
May 18, 2006
I am not sure if my problem is in the dynamic range or not. I have attached a small sample file. I tried to create a dynamic range by creating a named range of my "LabelRow" with the following as the refers to: =OFFSET($C$32,0,1,0, COUNTA($32:$32))
When I set my cursor on this, Excel makes an outline on the whole row 32 where there is data, which gives me some clue that it understands what I am trying to define. Next, I try to pick this range up and paste it using named ranges in a macro (CopyRange). It gives me an error like it doesn't understand the named range. I do similar macro code in many other places in this project and it works with non-dynamic ranges. Here are some other details on this project. I need a dynamic range because I insert new data into the dataset, which may be 2 quarters up to 20 quarters. This insert causes a regular range to be messed up. My ultimate goal is to graph selected rows of data with the same labels, but I need to have it starting at Q1, which is pushed to the right as new rows are added.
View 2 Replies
View Related
Jan 28, 2014
I am trying to select rows in excel using a macro and then hide the rows, but i want to be able to add rows within this selection and still be able to hide the complete selection of rows.
The macro i am currently using is shown below: This hides rows 131 - 205, but if i was to add in another row in between so the range i now want to hide is 131-206 can i get the macro to update to this new range?
[Code] ......
View 10 Replies
View Related
Feb 26, 2008
I have been tring to define a dynamice range in VBA. At the moment, I use following method to Define the range,
Sub OTC_Future_Total()
Dim DynamicRange As Range
Worksheets("FinalReport").Select
Range("e9").Select
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select...
View 5 Replies
View Related
Aug 1, 2013
I need to find average of the values , the count of the cells will be dynamic (may be 5 or even 200).
View 2 Replies
View Related
Apr 9, 2008
Ultimate goal is to automatically update the source data for 4 pivot tables that are on 1 worksheet. The data for those 4 pivot tables are on 4 different worksheets w/in the same workbook. Consolidating into one dataset is not an option.
I'm familiar with a dynamic named range, but the 4 worksheets that contain the data are replaced daily via automated Access output which creates an error.
I know how to do this adhoc by matching the pivot table names with their respective worksheets, but there are many other documents with similar layouts where this would beneficial.
Below is an example of how I currently update 3 pivots on separate worksheets w/ the same data range which is w/in the same workbook. My proposed changes are below the current. Any ideas on how to return the workbook name as a string...Or am I going about this the completely wrong way...
View 8 Replies
View Related
Feb 5, 2009
I can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup.
The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.
View 3 Replies
View Related
Feb 24, 2014
I have a pretty large spreadsheet set up that invoices our clients. A few tabs in the front allow us to globally invoice if we did certain services for all clients and then we can also go into each tab and invoice each client for specific services performed on their property. Some invoices are two pages long and other may be up to seven pages long and anywhere in between... So that's the first issue, how do you find how many pages to print and then set the print range for each invoice.
The second issue centers around being able to print all the invoices at one time.
The spreadsheet is set up in this manner: A recap sheet we print to check off that each invoice was printed; an IIF statement to get the Excel info into QuickBooks; a template to set up each invoice's information with dates, dates services were performed,etc.; then there are five Global billing tabs where I can invoice all accounts globally or by their type of account (Saturday or Sunday open, 24/7 etc.); then we get into the tabs for each account. Each account has its own tab with an invoice loaded inside where we can itemize the services they received. Inside all these individual account tabs we have set up 'Zone' tabs where we can invoice all the clients we set up within a zone. There are about twenty of these tabs. Then at the end I have a few more tabs that aren't used any longer, there are about ten tabs there...
Is there a way I can hit Print and get all of my invoices to print out at one time versus having to go into each and every tab, set the print range, and then hit Print for all 250ish invoices?
This is the biggest complaint I have right now about the invoicing program I have set up...
View 4 Replies
View Related
Oct 31, 2009
My worksheet (excel 2003) contains lots of details and requires different page setup for printing e.g.
(a) Cells B3 to R3 downwards: Data details, which user may add more rows of records
(b) Cell S1-Z20: Data summary (formula) linked with (a)
(c) Cell AA1-AK15: Pivot Table linked with (b)
Therefore, I set buttons, linked with 3 macros to do page setup for the different ranges. However, I had some problems i.e.
1. For print area (a), how to define print area parameter in a macro to check the last row of data. i.e. check for last "blank cell" of column "C-E" (others columns contain formula before input the data)
2. In the future, if I or user insert new columns in area (a), is it possible to setup macro that have print area for (a) - (c) shifted without changing the parameter in the macro?
View 3 Replies
View Related
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
Nov 18, 2008
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 6 Replies
View Related
Oct 18, 2006
I have a "submit" button macro which user would click after he has finished his input. This macro would update a reference number on the worksheet named "orange" and then print out this worksheet.
My purpose is "orange" is printed out with a reference number.
Below is the 1st code.....
View 9 Replies
View Related
Jan 24, 2012
I have a worksheet that has a few ranges and I need a printarea statement that looks like this:
Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$26,$G$1:$L$9,$M$1:$P$16,$Q$1:$S$7"
The above works, but each time I generate this worksheet, the ranges for the last row of each area can be dynamic.
So, I tried something like this:
Code:
Sub setPrtArea()
'set the print area
lr1 = Range("F65536").End(xlUp).Row
rngA = Range("$A$1:$F$" & lr1)
lr2 = Range("L65536").End(xlUp).Row
rngB = Range("$G$1:$L$" & lr2)
lr3 = Range("P65536").End(xlUp).Row
rngC = Range("$M$1:$P$" & lr3)
lr4 = Range("S65536").End(xlUp).Row
rngD = Range("$Q$1:$S$" & lr4)
ActiveSheet.PageSetup.PrintArea = rngA & "," & rngB & "," & rngC & "," & rngD
But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.
View 9 Replies
View Related
Jan 27, 2008
I have a dynamic print range defined as follows:-
=OFFSET(Sheet1!$A$1,0,0,MATCH("PRINT_AREA_ROWS",Sheet1!$A:$A,0),MATCH("PRINT_AREA_COLUMNS",Sheet1!$1:$1,0)), lets say this equates to range("A1:D12")
I also have a workbook beforeprint event defined as follows:-
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "Pete"
End Sub
My problem is that after the Print Event has fired , the dynamic range has been converted into a static one i.e range("A1:D12"). I have tried this on all version of Excel from 97 to 2007 and it behaves the same so premably this must be by design rather than a bug?
Am I doing something wrong? What is the work around? I'd rather not have to create the dynamic range within the print event procedure unless I have to as my workbook has many, many tabs!
View 9 Replies
View Related
