Forum Discussion
Is there a way to use a macro to find a print area when it varies with each run?
dchiggins54 This macro would set the print-area of each sheet for columns A:N for as many rows as there is data in the sheet:
Sub SetPrintAreas() Dim Sh As Worksheet For Each Sh In Worksheets With Sh.PageSetup .PrintArea = Intersect(Sh.UsedRange, Sh.Range("A:N")).Address End With Next End Sub
dchiggins54 This macro would set the print-area of each sheet for columns A:N for as many rows as there is data in the sheet:
Sub SetPrintAreas()
Dim Sh As Worksheet
For Each Sh In Worksheets
With Sh.PageSetup
.PrintArea = Intersect(Sh.UsedRange, Sh.Range("A:N")).Address
End With
Next
End SubHi JKPieterse,
Now I need something similar to reformat the same column in each worksheet. Again, the number of rows is different in each worksheet, so I can't just use the same macro I created for one on all others. It involves adding a working column after column J, dividing column J by $1,000,000, copying the results into the column J, and then deleting the working column. I know you need to create some type of loop to run through each worksheet again, but my knowledge of the language is minimal.
Here's what the initial macro looks like:
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("K2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/1000000"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K59")
Range("K2:K59").Select
Selection.Copy
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.NumberFormat = "$#,##0.0_);($#,##0.0)"I need to replace the part that shows the range and have it search for the range on each worksheet. Does that makes sense? If not, please let me know what else you need.
Thanks so much!
Hi Debbie,
Please try the following code...
Sub ReFormatColumns() Dim sh As Worksheet Dim LastRow As Long With Application .Calculation = xlCalculationManual .EnableEvents = False .ScreenUpdating = False End With For Each sh In ThisWorkbook.Worksheets LastRow = sh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row sh.Columns("K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove With sh.Range("K2:K" & LastRow) .FormulaR1C1 = "=RC[-1]/1000000" .Copy End With With sh.Range("J2:J" & LastRow) .PasteSpecial xlPasteValues .NumberFormat = "$#,##0.0_);($#,##0.0)" End With sh.Columns("K").Delete Application.CutCopyMode = False Next sh With Application .Calculation = xlCalculationAutomatic .EnableEvents = True .ScreenUpdating = True End With End SubThank you so much Subodh! This works on one worksheet. However, I need it to work through all 23 of the worksheets. The same column is on all worksheets and must be converted.
Can you add the loop that is required, please? I so appreciate your help! My boss is waiting for this worksheet to be completed today, so I'm so happy you are available.
Debbie
Thank you so much for your kind response!
I created the macro (copied/pasted) and I get the error stating that only one cell has been selected (see attached file). I was hoping that with a click of a button on a "master" worksheet in the file, the assigned macro would run through all of the 23 other worksheets and set the print area accordingly to each one. This macro did not accomplish that, I'm afraid.
What am I doing wrong?
Add one line to activate the sheet in your code like this...
For Each Sh In Worksheets Sh.Activate With Sh.PageSetup .PrintArea = Intersect(Sh.UsedRange, Sh.Range("A:N")).Address End With NextAnd when the code produces an error, can you look at the ActiveSheet and confirm if that sheet is empty?
What is the layout of your data on each sheet in the workbook? Is the data scattered all over or it starts from row1 and column A with no rows or columns in between the data?
Hi Subodh,
There is always data on each worksheet. They are never null...at least 10 rows of data or more. The data is not scattered. Starts in A1.
Are you saying to use the code you just gave me instead of the previous version? I ask because the line you gave me in the previous post to add to the macro worked, so I want to be clear.
Debbie
dchiggins54 I was assuming the worksheets contain sufficient data, hence the intersection with the usedrange to get the correct number of rows. Apparently one or more worksheets contain just one cell with data in the desginated columns. YOu can avoid the message by adding this line of code to the macro, just below the Dim statement:
Application.DisplayAlerts = False
