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.
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
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
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.
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.
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.
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
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.
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...
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?
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
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)
I am trying to take a range ("a5:k23") of data in a sheet name Kelly and paste that data at range ("a1") in Sheet4 when the numbers change in cells "(K3") and "(J3") in the Kelly Sheet. When the numbers change, I want this to automatically paste and paste special. Since there are some conditional formats with color, I want to first Paste and then Paste Special the range of data.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("k3") = 1 And Range("j3") = 10 Then Range("A5:K23").Select Selection.Copy Sheets("Sheet4").Select Range("A1").Select ActiveSheet.Paste Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("KELLY ").Select Range("F2").Select
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.
=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!
I am trying to capture data for statistical analysis, but have hit a snag. As shown in attached sheet I have each day for the year broken down into hour time slots into which data will be added. As I will need to come up with a number of similar sheets in the future I was wondering if there is a way to automate through vba, a quick way to copy down the hour slots for each day, 365 times, rather than the laborious task of copy and paste manually.
Once a user of my workbook has input various price levels on the Input Page, the data is sorted on the output page. As the data on the Output Page is quite vast and not all of it is relevant on a particular day, the user is interested in just printing a certain range of data that are in 5 columns. I am trying to figure out how to set up a macro that will allow the user to enter into an Input Box the range to print (ie. Cells C25:G60).
I found one example on this site listed below that allows one to do that but I would like to make it more user friendly. I do not want to make the user have to write out Excel cooridinates. Specifically by allowing the user to input in the Input Box a High price level in Column C and a Low Price level in Column C ; The macro will look in Column C for the highest instance of this High Price and begin the range down to the last instance of the Low Price (there can be several instances of the exact price level.) The macro would then create the print preview of the cells Cxx:Gyy.
Sub SelectPrintArea() Dim PrintThis As Range ActiveSheet.PageSetup.PrintArea = "" Set PrintThis = Application.InputBox _ (Prompt:="Select the Print Range", Title:="Select", Type:=8) PrintThis.Select Selection.Name = "NewPrint" ActiveSheet.PageSetup.PrintArea = "NewPrint" ActiveSheet.PrintPreview End Sub
I'm trying to automate the process of exporting a range of cells to a text file. I think the Print# command will work because I don't want the quotes at the end of each line. Each range of cells will be a separate text file, and I want to save and name the file according to the column it's in. This bit of code from dominicb seems to be what I'm looking for, in principle.
The codes in E column are what is entered in a worksheet range (user cannot see the list). Needed is to show both the two-character code in column E and the text definitions in column F in the dropdown. As data validation now works, users only see the two-character code and are not remembering what the code stands for - they need to see the definition.
Is there a way to show both the two-character code from column E along with the text in column F? When user selects, only the code would be entered in the target cell. (Is data validation the correct technique?)
I have set this problem up in closely located cells to construct the issue. In reality, the validation list is well off-screen from where the user is actually inserting the codes and there are many more codes/definitions.
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB: Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long, FillCT As Long For X = 1 To 4 If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
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
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range. B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed The code is then highlighted in yellow, the code is: Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
I am trying to print a range that falls just outside of a normal A4 size. Is there a way of scaling this range to fit the page. Withough changing the column sizes before printing(each column is diffrent size). Or will i need to load the column sizes into a array and resize for the print then back to original size from array.
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.
