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
Hi 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
There is already a For loop looping through all the sheets in the workbook which contains this macro...
For Each sh In ThisWorkbook.Worksheets
If you want to run the macro on ActiveWorkbook, just remove ThisWorkbook from the For loop so that it would become For Each sh In Worksheets
Though I didn't test the code earlier, I have just tested it after getting your feedback and it worked on all the sheets not on just one.
Now there's a new error. Pic attached...what did I do wrong? I'm running it from the first worksheet in the file.
