Printing Variable Rows With A Macro
Feb 14, 2007
I export data from another programme into excel and have written a macro to move and format this. The number of rows of data exported will be different each time. I need to be able to print this data and want to add it to the macro. I have learnt to use this code to select this data working from the last used cell to the top
Range("a1:d1", Range("a65536:d65536").End(xlUp)).Select
Using the macro recorder for printing one of my exports I got this code
Range("A1:D53").Select
Range("D53").Activate
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup.......................
View 5 Replies
ADVERTISEMENT
Apr 2, 2007
I used the macro recorder to take selected cells from a worksheet and automaically print the selected area. It works great - but i (as usual) want it do more - more specifically, i want it to print, say 10 rows, from any row i select.
The worksheet is a live schedule which is updated constantly as production works through the scheduled orders. I want to identify the row which has the current running production order and print that row and the next ten.
how to use the input box to ask for the row # but don't know how to code the actual passing of that variable. I figure i can then marry my existing code to make a working frankensteinish macro.
View 4 Replies
View Related
Apr 13, 2007
I am trying to create a primitive counter. My idea is to write a value to a file, read it, add one and write it back. The file as a single value tab-delimted of 5000. The code below produces the odd output on debug. I really cant understand it.
Public Function nextInv() As Integer
Dim fname As String
Dim fnum As Integer
Dim stringArray()
Dim invNum As Integer
fnum = FreeFile()
fname = "nextInv.txt"
stringArray() = parse(OpenRecordClassesFn(fname), vbTab)
For i = UBound(stringArray) + 1 To LBound(stringArray)
invNum = stringArray(i)
Next
If FileExists(fname) Then
Open fname For Output As #fnum
End If
invNum = invNum + 1
Debug.Print "invNum"
Print #fnum, (invNum & vbTab)
Close #fnum
invNum = invNum + 1
End Function
Public Sub testHarness()
Debug.Print nextInv()
End Sub
with a output of
5000
invNum
0
and the value in the file of 1. It should be 5001.
View 9 Replies
View Related
Jan 6, 2008
I have a multitude of reports to work on. Right now, the headers are made manually using the first few rows of each sheet, which is very inefficient, and difficult to get them to look the same. If the sheet is put on "Fit to X pages", the header is shrunk still. The reason this is done is because in the header / footer section of Excel 2002/2003, you can only use static text and predefined footer data (pg 1 of X, date, etc). Basically, I'd like to use a header that stays the same size, format, look between sheets/workbooks, but can accept variables in some way.
View 7 Replies
View Related
May 5, 2009
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2()
Dim WhatToPrint As String
WhatToPrint = Sheets("Reports").Cells(3, 6).Value
'sets the variable to equal the contents of cell D3 which contains the formula
'summarising the print ranges I want to print
Sheets("Reports").Cells(3, 6).Select
ActiveCell.FormulaR1C1 = WhatToPrint
' pastes the variable in cell F3 - just to check that it looks like I want it to
Sheets("Reports").PageSetup.PrintArea = WhatToPrint
'uses the variable to set print area - this is where it fails!
'if you replace the variable with the contents of cell F3 the macro will work
ActiveWindow.SelectedSheets.PrintPreview
End Sub
View 3 Replies
View Related
Nov 2, 2011
I'm trying to write a macro with a variable number or rows depending on the total number of rows the workbook has.
I tried like this:
Code:
Sub provaanova()
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range(("$J$2:$J$") & Range("J" & Rows.Count).End(xlUp).Row) _
, ActiveSheet.Range(("$J$2:$J$") & Range("J" & Rows.Count).End(xlUp).Row), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
and:
Code:
Sub Provaregress()
r = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$r") _
, ActiveSheet.Range("$K$2:$M$r"), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
But it gives me an error. The only way i was able to do it without error was:
Code:
Sub Provaregress()
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$53968") _
, ActiveSheet.Range("$K$2:$M$53968"), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
But as u can see it has a fixed number of rows. ( I need variable number of rows detected on column J, and then run regression with K,L,M as depending variables).
View 5 Replies
View Related
Jan 4, 2012
I am creating a macro to tidy up a large data sheet. it is a list of products as follows:
Heading1Code; Heading2date; etc
12
12
12
13
13
14
15
15
15
The blanks beneath each series of products need to be deleted but they are variable and a macro that i write is not flexible enough to remove a variable number of blank rows beneath a variable number of each product.
View 9 Replies
View Related
Apr 12, 2012
Our company gets excel spreedsheets with UPC numbers. The numbers are divided in parts or columns. Sometimes it's in 2 columns; sometimes in 3; and sometimes in 4 like this:
A_____B_____C____D
023__14444__779__9
023__14442__789__7
I'd like to be able to concatenate the digits and have the entire UPC number appear in a blank column to the immediate right and go straight down the line and calculate them all. I figured out how to do this with separate macros for 2, 3, and 4 digits or columns.
Sub Combine_2_Part_UPC()
Do While ActiveCell ""
ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.Offset(0, -1) & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Is there a way I could do this with just one macro, by maybe selecting the digits of the first UPC number in the first row of the data to cue the macro in as to how many columns to process?
(The UPCs are in the midst of a lol of other data like product cost, so a macro probably wouldn't be able to figure out how many to calculate on its own.)
View 2 Replies
View Related
Nov 14, 2012
Sheet 2 has 3 cells with values:
C14, C15, C16
I need VB code to:
copy the values in those cells
Return to Sheet 1, let the user click a cell in any row in Colum F, then Paste them (Special, Values and Transpose)
View 4 Replies
View Related
Jan 13, 2009
I am working on a macro where I am creating a formula to string together some text columns and then copy the formula down the entire column. The data source I will be performing this on will change in number of rows period to period. The data would be in columns A,B & C and the formula is in D. The formula in D is stringing together the data in AB & C and then I want to copy and paste that formula down to the bottom of all of the data. What would the code be for the copy and paste with variable rows?
View 9 Replies
View Related
May 29, 2008
I am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column
e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17
As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.
I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17
View 3 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Aug 11, 2013
I have a few pages of information and have grouped rows collapsed to show only the summary information. When I try to print only visible summary rows, whether I use print, print selection, copy and paste to another sheet or copy and "paste special" values to another sheet, all the hidden/grouped rows print or the summary rows print separately on different pages. Is it possible to print only the summary rows.
View 3 Replies
View Related
Jul 23, 2014
I have a worksheet that has rows of information up to row 3000. Sometimes all the rows are shown and sometimes a lot of them are hidden. When I try and print, the rows with info are printed but then the hidden rows are included as just blank pages. Is there a way I can get excel to print only the rows in 1-3000 that are shown and not hidden?
View 1 Replies
View Related
Sep 6, 2007
I've a worksheet that contains a whole list of items in stock.
For example
Item Quantity
hot water bottle
maggi
fab
cooking oils
breakfast oats
The above is an example of what might be seen in the spreadsheet. What i'd like to be able to do is before printing it out, i want items with 0 quantity to be shown only. So i decided to hide the rows that have items with no quantity. To do this, all i could think of is to have a button that may contain codes to hide the rows. The problem with that is the button will appear there in the printout. Is there any way of making the rows hidden before printing without using a button to trigger the code?
View 14 Replies
View Related
Nov 28, 2007
Is there a VB routine that can unhide rows when I print?
View 9 Replies
View Related
Mar 10, 2006
I have a report that pulls data from another worksheet in Range A26:J58.
What I want to be able to do is assign a button for printing the report but
before printing from A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26 down so it is not a
random fill.
Also, I want to save this workbook as a template so that it can be used over
again, so I guess it would not be good to delete the rows in the range
otherwise I would have to recreate them.
View 14 Replies
View Related
Nov 1, 2009
I am making a project in which there is vast data of around 10-15 pages to get print...but due to vast data its not possible to get all printed in 1 pages...So i want that cell A1:K4 & A47:K53 to get print in every sheet...Header and footer is not posisble due to some logo at the end of the page..is there anyway for such printing option...
View 9 Replies
View Related
Jan 9, 2007
By adapting a worksheet macro obtained previously via Ozgrid, I built a workbook that dynamically produces a database, in a worksheet titled “NumLtrWBS DB”, based on all possible combinations of what is listed in column A of three 3 worksheets titled: “ Num”, “Ltr” and “WBS, respectively.
The macro, named “NumLtrWBS", works wonderfully, until I add a row at the top of each list to contain a column header. The unchanged macro includes the column headers in the “produced” database. When I change the macro by altering the variable definitions to begin at A2 rather than A1, it still includes the column headers in the “produced” database. How should I change the macro so it doesn’t “produce” the column headers in the database worksheet?
View 3 Replies
View Related
Jan 18, 2007
I'm completely at a loss I have a worksheet with hidden rows which I do not want to print yet Excel prints them anyway resulting in 12 pages rather than just the pages I want.
View 7 Replies
View Related
Dec 12, 2007
I want to be able to print individual rows from my sheet. When I try, they run onto a number of pages. The information needs to be printed along with the column headers to make sense. Is there a way I can do this and get the rows to 'wrap' to save paper?
View 9 Replies
View Related
Nov 10, 2005
I have formulae in cells to reference another sheet, but if the referenced cell is blank, then the new cell I made blank (using the IF function). The problem is, when I want to print, Excel wants to print all of the cells with entries in them - even the ones with formulae in them that are blank.
Is there any way to prevent the empty cells from printing?
View 11 Replies
View Related
Jan 26, 2009
in my file i have rows/columns a1:z50. now my requirement is that rows as header a1:a3, as footer a47:a50 and columns a:c to be printed in every page.
View 9 Replies
View Related
Jan 10, 2010
I have a workbook containing several sheets. New worksheets may be added. From each sheet, I would like to print the first row in range (AB1:AE200) along with any rows below the first that contains the value “Red” in column AB. From what I have been reading, it would seem that a temporary worksheet would be the answer using a copy/paste.
As each sheet would likely contain only three or four rows to then print, is there a way then to get all the data onto a single page, thereby preventing the need to print a single page for each sheet?
View 9 Replies
View Related
Jun 27, 2006
I have a worksheet that I use to track my clients and their meal selections over a five day week. Col A is their name, B is their shift (am or pm) C is their table number, D is whether they will attend that day (yes or no) and E is their meal choice (a b or c) Columns D & E repeat the same information for each day Mon-Fri.
I would like to have a print button so that on a daily basis I can print the clients name and thier meal selection (only those those who have a yes for that day) separating AM shift from PM and then grouping them by their table number rather than alphabetical order.
View 2 Replies
View Related
Dec 1, 2006
I tried to reduce the size of the printing area by deleting the blank rows. To achieve this I entered VBA code that appeared in the newsletter issue 3 after slightly modifying the code suiting to my need. But, while exeucting the code with all the parameters, xlCellTypeBlanks, xlCellTypeFormulas,xlErrors, the error as shown as a screen shot herein is populated. But, with the parameter,""xlCellTypeBlanks"" all the Blank Rows only got deleted and similarly only those rows containing Formulas are deleted, while the parameter is""xlCellTypeFormulas".
I want all the cells that contain xlFormulas, xlErrors and xlCellTypeBlanks as well be removed while executing the code given herein. But, if the range contains "xlFormulas" and "xlCell TypeBlanks", the rows containing blank cells are not removed and vice versa.
View 2 Replies
View Related
Mar 3, 2007
I am trying to print only the non-empty rows in my sheet ("1"), so I am checking every row from row 1 to row 500 to determine if that row is empty, in that case it will be hidden temporarily, then I will PrintPreview only non hidden rows. Finally, I will unhide all rows.
The problem is that although the following code works, it takes forever to run due to large number of rows. Based on sheet design, once the first empty row is discovered, all the following rows beneath it are all blank by default, so all I really need to do is automatically hide all the rows following the first discovered blank one without checking them, then proceed with PrintPreview as above. Sometimes my 3rd row is blank, so I could save a lot of time by automatically hiding rows 3 to 500 without checking them.
code below:
Sub Hide_Print_Unhide()
Dim rw As Long
Application. ScreenUpdating = False
With Sheets("1")
For rw = 1 To 500
If Application.WorksheetFunction. CountA( _
. Cells(rw, 1).Range("A1:C1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A1:A500").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub
View 7 Replies
View Related
Sep 14, 2007
I've got a report that is filled out every day and submitted to the VIP's within the company.
They normally just open the file, press print and read the hard-copy.
Quite often the text of the column is larger than the cell size and the text is cut off.
View 7 Replies
View Related
Jul 17, 2013
I'd like to know if is possible and how to move up rows that I change a value for example the column time has a row with value 02:00 and I change the value to 01:00 and the row moves up
Also when I change the value to 00:00 how to move up the row and when printing skip these rows.
View 1 Replies
View Related
Apr 14, 2014
In the column marked SA I have some numbers. These are Sub-Accounts. I am looking to run off a report that prints off all rows with the respective SA number. I have only included a "4" and a "5" in my example but there is going to be more than one row "assigned" to Sub Account 4, Sub Account 5 etc. Ultimately it provides a break down of the accounts.
View 7 Replies
View Related
