Solved: Need For Help

[grichey]

[VBA]
dim wsheet as Worksheet
For each wsheet in WorkSheets

'calculations and formatting going on here.

Next wsheet

[/VBA]

What am I doing wrong here? It keeps printing the 1st sheet over and over again??

[Bob Phillips]

Are you referring to wsheet or ACtivesheet in the code not shown?

[grichey]

Yes. Below is the entire thing. Thanks for the fast response.

[VBA]Sub Make_FS()
'Comment next few lines if you don't want to use 1000 sheets of paper
Dim wSheet As Worksheet
For Each wSheet In Worksheets

'hide 1.X's
Columns("D:R").Select
Selection.EntireColumn.Hidden = True
'Add Columns for Variance
Columns("X:AA").Select
Selection.Insert Shift:=x1ToRight
'Add Variance A-B, A-B/A, A-P, A-P/A
Range("X7").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Jan Prior"",""A-B"",IF(RC[-3]<>"""",ROUND(RC[-5]-RC[-3],0),""""))"
Range("X2").Select
ActiveCell.FormulaR1C1 = "A-B"
Range("Y2").Select
ActiveCell.FormulaR1C1 = "%"
Range("Y7").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""Jan Prior"",""%"",IF(RC[-6]<>0,ROUND((RC[-6]-RC[-4])/RC[-6],2),""""))"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "A-P"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "%"
Range("Z7").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""Jan Prior"",""A-P"",IF(RC[-3]<>"""",ROUND(RC[-7]-RC[-3],0),""""))"
Range("AA7").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""Jan Prior"",""%"",IF(RC[-8]<>0,ROUND((RC[-8]-RC[-4])/RC[-8],2),""""))"

'Fill Down and Make pctages

Range("X7:AA4622").Select
Selection.FillDown
Columns("Y:Y").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Columns("AA:AA").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Columns("X:X").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Columns("Z:Z").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
'Sub Print_Selection()
'Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Assets()
Range("C1:AA146").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Liabs()
Range("C163:AA256").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cash()
Range("C269:AA371").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Income_Stmt()
Range("C386:AA461").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_ISRM()
Range("C480:AA540").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_ISCM()
Range("C553:AA613").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_ISCV()
Range("C626:AA686").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_GP()
Range("C695:AA818").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SLSRM()
Range("C830:AA871").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SLSCM()
Range("C883:AA920").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SLSCV()
Range("C933:AA974").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Resale()
Range("C1007:AA1301").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Discs()
Range("C1315:AA1399").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P11()
Range("C1423:AA1545").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P12()
Range("C1556:AA1686").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P21()
Range("C1701:AA1805").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P22()
Range("C1812:AA1936").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P31()
Range("C1950:AA2049").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P32()
Range("C2057:AA2177").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs1()
Range("C2193:AA2343").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs2()
Range("C2359:AA2465").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs3()
Range("C2477:AA2562").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs4()
Range("C2575:AA2660").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs5()
Range("C2672:AA2757").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs6()
Range("C2769:AA2854").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs7()
Range("C2866:AA2951").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs8()
Range("C2962:AA3047").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs9()
Range("C4069:AA4145").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Selling()
Range("C3059:AA3337").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SERM()
Range("C3351:AA3435").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SECM()
Range("C3447:AA3535").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SECV()
Range("C3547:AA3625").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SEGEN()
Range("C3638:AA3696").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Freight()
Range("C3709:AA3808").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Admin()
Range("C3824:AA3877").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Corp()
Range("C3890:AA3969").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Int()
Range("C3982:AA4016").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Rent()
Range("C4029:AA4057").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Tum()
Range("C4162:AA4200").Select
Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Crush()
Range("C4210:AA4235").Select
Selection.PrintOut Copies:=1, Collate:=True

'Comment out the below line if you do not wish to use 1000 sheets of paper
Next wSheet
End Sub[/VBA]

[Bob Phillips]

Therin lies your problem

[vba]

Sub Make_FS()
'Comment next few lines if you don't want to use 1000 sheets of paper
Dim wSheet As Worksheet
For Each wSheet In Worksheets

'hide 1.X's
With wSheet

.Columns("D:R").EntireColumn.Hidden = True
'Add Columns for Variance
.Columns("X:AA").Insert Shift:=x1ToRight
'Add Variance A-B, A-B/A, A-P, A-P/A
.Range("X7").FormulaR1C1 = "=IF(RC[-1]=""Jan Prior"",""A-B"",IF(RC[-3]<>"""",ROUND(RC[-5]-RC[-3],0),""""))"
.Range("X2").FormulaR1C1 = "A-B"
.Range("Y2").FormulaR1C1 = "%"
.Range("Y7").FormulaR1C1 = "=IF(RC[-2]=""Jan Prior"",""%"",IF(RC[-6]<>0,ROUND((RC[-6]-RC[-4])/RC[-6],2),""""))"
.Range("Z2").FormulaR1C1 = "A-P"
.Range("AA2").FormulaR1C1 = "%"
.Range("Z7").FormulaR1C1 = "=IF(RC[-3]=""Jan Prior"",""A-P"",IF(RC[-3]<>"""",ROUND(RC[-7]-RC[-3],0),""""))"
.Range("AA7").FormulaR1C1 = "=IF(RC[-4]=""Jan Prior"",""%"",IF(RC[-8]<>0,ROUND((RC[-8]-RC[-4])/RC[-8],2),""""))"

'Fill Down and Make pctages

.Range("X7:AA4622").FillDown
.Columns("Y:Y").Style = "Percent"
.Columns("Y:Y").NumberFormat = "0.0%"
.Columns("AA:AA").Style = "Percent"
.Columns("AA:AA").NumberFormat = "0.0%"
.Columns("X:X").Style = "Comma"
.Columns("X:X").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
.Columns("Z:Z").Style = "Comma"
.Columns("Z:Z").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
'Sub Print_Selection()
'Selection.PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Assets()
.Range("C1:AA146").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Liabs()
.Range("C163:AA256").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cash()
.Range("C269:AA371").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Income_Stmt()
.Range("C386:AA461").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_ISRM()
.Range("C480:AA540").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_ISCM()
.Range("C553:AA613").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_ISCV()
.Range("C626:AA686").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_GP()
.Range("C695:AA818").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SLSRM()
.Range("C830:AA871").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SLSCM()
.Range("C883:AA920").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SLSCV()
.Range("C933:AA974").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Resale()
.Range("C1007:AA1301").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Discs()
.Range("C1315:AA1399").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P11()
.Range("C1423:AA1545").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P12()
.Range("C1556:AA1686").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P21()
.Range("C1701:AA1805").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P22()
.Range("C1812:AA1936").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P31()
.Range("C1950:AA2049").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_P32()
.Range("C2057:AA2177").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs1()
.Range("C2193:AA2343").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs2()
.Range("C2359:AA2465").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs3()
.Range("C2477:AA2562").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs4()
.Range("C2575:AA2660").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs5()
.Range("C2672:AA2757").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs6()
.Range("C2769:AA2854").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs7()
.Range("C2866:AA2951").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs8()
.Range("C2962:AA3047").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Cogs9()
.Range("C4069:AA4145").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Selling()
.Range("C3059:AA3337").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SERM()
.Range("C3351:AA3435").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SECM()
.Range("C3447:AA3535").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SECV()
.Range("C3547:AA3625").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_SEGEN()
.Range("C3638:AA3696").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Freight()
.Range("C3709:AA3808").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Admin()
.Range("C3824:AA3877").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Corp()
.Range("C3890:AA3969").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Int()
.Range("C3982:AA4016").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Rent()
.Range("C4029:AA4057").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Tum()
.Range("C4162:AA4200").PrintOut Copies:=1, Collate:=True
'End Sub
'Sub Print_Crush()
.Range("C4210:AA4235").PrintOut Copies:=1, Collate:=True

'Comment out the below line if you do not wish to use 1000 sheets of paper
End With
Next wSheet
End Sub
[/vba]

[grichey]

Thanks dude - Do you have a quick explanation for what I was doing wrong (aside from the right way). Like you can't do blah blah blah... so that I don't make the same mistake again?

Appreciate the help

[Bob Phillips]

Yes, very straightfoward.

You were iterating through all of the sheets, For Each wSheet In Worksheets, but you then ignored wSheet which was pointing to the next sheet. Instead of qualifying all of the range objects by wSheet, wSheet(Columns) wSheet.Range(, etc., you referenced them unadulterated, or worse still via ACtiveCell and ACtiveSheet. This is wrong because you are then only pointing at the activesheet, so all ranges are on the active sheet, not the sheet referenced by wSheet.

All the Selecting is bad too.

[grichey]

thanks for the help