Hello, all,
I know there must be a way to do this, so I'm throwing it out to you.
I am trying to redefine a print area. It is different for every sheet. What I do know is that it will end in column L.
Can I do something similar to this? (This, of course, failed.) ActiveSheet.PageSetup.PrintArea = "$A$1:L1.End(xlDown).Select"
ActiveSheet.PageSetup.PrintArea = "$A$1:$L$147" That works and I am trying to make the end property a variable.
Any ideas?
Hi,
would something like following help just from top of my head
Dim lastrow As Long lastrow = Sheets(1).Cells(Rows.Count, "L").End(xlUp).Row ActiveSheet.PageSetup.PrintArea = "$A$1:$L$" & lastrow
You can build up a string of cariables by using the & and the "signs
example
dim somefigure as long
dim lastrow as long
"$A$" & somefigure & ":$L$" & lastrow
where somefigure and lastrow are variables set by you.
Last edited by Demeter; 12-02-2014 at 05:47 PM.
thanks, I'll give it a try;
No, it didn't work. It set the print area as A1 through L1, lol
Last edited by up4asmile; 12-02-2014 at 05:50 PM.
I tried this and it almost worked:
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlDown).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$" & lastrow
except it selected all of the rows instead of just to the end of column H
Can you post an example?
really any excel, any information, with the cells being filled from A1 through L ?.
In a previous process, I defined the print area for the first report that was produced.
Now I am adding different information and need to be able to redefine the print area. I have automated all of the other steps except redefining the print area. That one sure is tricky.
The coding above sure is close.
I appreciate your help!
I tried this, thinking the last part needed linking, but it didn't work:
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlDown).Row
ActiveSheet.PageSetup.PrintArea = ("$A$1:$H$" & lastrow)
Last edited by up4asmile; 12-02-2014 at 06:16 PM.
What do you mean exactly with:
except it selected all of the rows instead of just to the end of column H ?
The code just searches for the last used row in column H and uses this as last row in your range.
In your code leave at the ()
and you use xldown this emans that you are searching for first cell from top, so if you have empty cell in between to cells it will not discover the second full cell (example you have A2 and A4 filles, you search from top you find A2 and not A4. when xlup, you will find A4.)
Dim lastrow As Long lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlDown).Row ActiveSheet.PageSetup.PrintArea = "$A$1:$H$" & lastrow
Last edited by Demeter; 12-02-2014 at 06:34 PM.
Thanks, Demeter, for taking a long look at this. I appreciate it.
When I use the code I posted it selects A:H as the print area, so it goes way past what I want.
There are no blank lines in the file (and never will be), so the xlDown should work. (Yes, I know about the blank lines possibility you mentioned)
I used the coding just as you showed above and it still set the page to A:H.
Hello,
Can you post a sample file?
Or only the code you are using?
Think there is some small thing wrong in your code
Here is the first macro that I use. I does everything except define the print area.
Sub Googlebills()
'
' Macro1 Macro
' Macro recorded 7/2/2007 by
'
'
Cells.Select
Range("D1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("L:S").Delete
ActiveSheet.Cells(1, 12) = "Paid by Distributor"
ActiveSheet.Cells(1, 13) = "Paid by EXAIR"
Columns("L:M").Select
Selection.NumberFormat = "$0.00"
Selection.ColumnWidth = 18
Columns("F:F").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],pcodes,3,FALSE)"
Selection.AutoFill Destination:=Range("F2:F672"), Type:=xlFillDefault
Range("F2:F672").Select
ActiveWindow.SmallScroll Down:=-63
ActiveWindow.ScrollRow = 1
Dim ws As Worksheet
Dim icnt()
Dim ccnt()
Dim tot(2) As Single
Dim timp(2) As Long
Dim tclk(2) As Long
Dim costcnt()
rr = 2
Set ws = ActiveSheet
Do While ws.Cells(rr, 4) <> "" Or ws.Cells(rr + 1, 4) <> ""
ReDim icnt(2)
ReDim ccnt(2)
ReDim costcnt(2)
sr = rr
Do While ws.Cells(rr, 5) <> ""
If ws.Cells(rr, 6) = "A List" Then ii = 1 Else ii = 2
icnt(ii) = icnt(ii) + Val(ws.Cells(rr, 7))
ccnt(ii) = ccnt(ii) + Val(ws.Cells(rr, 8))
tot(ii) = tot(ii) + Val(ws.Cells(rr, 10))
timp(ii) = timp(ii) + Val(ws.Cells(rr, 7))
tclk(ii) = tclk(ii) + Val(ws.Cells(rr, 8))
costcnt(ii) = costcnt(ii) + Val(ws.Cells(rr, 10))
rr = rr + 1
Loop
ws.Cells(rr, 5).FormulaR1C1 = "=VLOOKUP(" & ws.Cells(rr - 1, 3) & ",pcodenames,3,FALSE)"
ws.Cells(rr, 1).EntireRow.Insert
ws.Cells(rr, 1).EntireRow.Insert
ws.Cells(rr, 5) = "A list"
ws.Cells(rr + 1, 5) = "B list"
ws.Cells(rr, 7) = icnt(1)
ws.Cells(rr + 1, 7) = icnt(2)
ws.Cells(rr, 8) = ccnt(1)
ws.Cells(rr + 1, 8) = ccnt(2)
ws.Cells(rr, 10) = costcnt(1)
ws.Cells(rr, 12) = (costcnt(1) / 2)
ws.Cells(rr, 13) = (costcnt(1) / 2)
ws.Cells(rr + 1, 10) = costcnt(2)
ws.Cells(rr + 1, 12) = (costcnt(2) / 2)
ws.Cells(rr + 1, 13) = (costcnt(2) / 2)
For rr = rr - 1 To sr Step -1
ws.Cells(rr, 1).EntireRow.Delete
Next rr
rr = rr + 4
'ws.Cells(rr, 3).FormulaR1C1 = "=VLOOKUP(ws.cells(rr-3,2),pcodenames,3,FALSE)"
Loop
rr = rr + 3
ws.Cells(rr, 5) = "A List Totals"
ws.Cells(rr, 5).Font.Bold = True
ws.Cells(rr, 12) = tot(1) / 2
ws.Cells(rr, 13) = tot(1) / 2
ws.Cells(rr, 7) = timp(1)
ws.Cells(rr, 8) = tclk(1)
ws.Cells(rr + 1, 5) = "B List Totals"
ws.Cells(rr + 1, 5).Font.Bold = True
ws.Cells(rr + 1, 12) = tot(2) / 2
ws.Cells(rr + 1, 13) = tot(2) / 2
ws.Cells(rr + 1, 7) = timp(2)
ws.Cells(rr + 1, 8) = tclk(2)
ws.Cells(rr + 2, 7) = timp(1) + timp(2)
ws.Cells(rr + 2, 8) = tclk(1) + tclk(2)
ws.Cells(rr + 2, 12) = tot(1) / 2 + tot(2) / 2
ws.Cells(rr + 2, 13) = tot(1) / 2 + tot(2) / 2
ws.Cells(rr + 2, 1).EntireRow.Font.Bold = True
Columns("A:D").Delete
Columns("B:B").Delete
ActiveSheet.Cells(1, 1) = "Product Category"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""-,Bold""&12Google " & ActiveSheet.Name & " 2014 Bill"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Columns("G:G").Select
Selection.ColumnWidth = 18
End Sub
Then I made a new macro containing only the code that you gave me. If it gets to the point where the code works, then I will add it to the end of the current macro. (At least that's the plan.)
This macro was created quite a while ago and may have some extra stuff in it that it doesn't need. It basically takes a report on costs, and divides the cost between us and our distributor, and shows how much cost was in each product line.
I hope this helps.
Dont see anything strange, except for some minor improvements
Should work if you change your last part of code to:
Columns("A:D").Delete Columns("B:B").Delete ActiveSheet.Cells(1, 1) = "Product Category" Dim lastrow As Long lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlDown).Row With ActiveSheet.PageSetup .PrintArea = ActiveSheet.Range("$A1:H" & lastrow).Address .CenterHeader = "&""-,Bold""&12Google " & ActiveSheet.Name & " 2014 Bill" .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0.25) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True End With Columns("G:G").ColumnWidth = 18
I'll give it a try. Thank you!
Nope, I tried it, with the same results.
I'm going to shelve this till time to do next month's reports. Time to move on and update more macros.
Thank you so much!!!!
Last edited by up4asmile; 12-03-2014 at 06:22 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks