Solved: Macro not working properly

[abraham30]

Hello Everyone,

In the attached sheet, I have to run the macro twice to get the required output. It is not working properly for column with variable range.
Can anyone help me to resolve this. Advance thanks


Sub macro()
Dim LstRw As Long, LstCol As Long
LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LstCol = Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
With Sheets("overview")
PivotTables("PivotTable4").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Worksheets("rawdata").UsedRange, _
Version:=xlPivotTableVersion14)
.PivotTables("PivotTable4").PivotCache.Refresh
End With
With Range(Cells(1, 1), Cells(4, LstCol)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Range(Cells(8, 3), Cells(10, LstCol)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range(Cells(11, 2), Cells(LstRw - 1, LstCol)).Borders.Weight = xlThin
Columns("C:Q").ColumnWidth = 12.23
Dim lr As Long, lc As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(8, Columns.Count).End(xlToLeft).Column
With ActiveSheet.PageSetup
.PrintArea = "$A$8:" & Cells(lr, lc).Address
.PrintTitleRows = "$1:$8"
.CenterFooter = "&P/&N"
.PrintQuality = 600
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Public\saabx.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub

[SamT]

Abraham,

I notice that your procedure is actually working with two sheets, which may or may not be the same sheet.

Your first "With" statement is working with Sheet ("overview") but the rest of the procedure is working with the ActiveSheet, wether or not you have explicitly declared so.

Now, I can't tell from your code if "overview" is in fact active when you run the code.

HPH

[abraham30]

Hello Sam,

Thanks for your quick response on my below query.

The sheet "Raw data" contain data with variable range. From that data, a pivot table has been created and rename with "Overview".

So the macro will auto-refresh the pivot based on data from "Raw data" which is working perfectly.

With Sheets("overview")
.PivotTables("PivotTable4").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Worksheets("rawdata").UsedRange, _
Version:=xlPivotTableVersion14)
.PivotTables("PivotTable4").PivotCache.Refresh
End With



My requirement is that I have to put full border on all cell and then print those data into pdf format. When new columns are added, then the border and print (pdf) option is not working properly.

How to solve this issue. Advance thanks

[SamT]

Ok, you are working with three sheets. One of which is the one VBA assumes you mean when you leave out the Red words in this code.
Sub macro()

Dim LstRw As Long, LstCol As Long
LstRw = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LstCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column

With Sheets("overview")

.PivotTables("PivotTable4").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Worksheets("rawdata").UsedRange, _
Version:=xlPivotTableVersion14)
.PivotTables("PivotTable4").PivotCache.Refresh

End With
With ActiveSheet.Range(Cells(1, 1), Cells(4, LstCol)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With ActiveSheet.Range(Cells(8, 3), Cells(10, LstCol)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With

ActiveSheet.Range(Cells(11, 2), Cells(LstRw - 1, LstCol)).Borders.Weight = xlThin
ActiveSheet.Columns("C:Q").ColumnWidth = 12.23

Dim lr As Long, lc As Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
lc = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column
With ActiveSheet.PageSetup
.PrintArea = "$A$8:" & Cells(lr, lc).Address
.PrintTitleRows = "$1:$8"
.CenterFooter = "&P/&N"
.PrintQuality = 600
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Public\saabx.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

End Sub
You say you have a problem after columns are added. Are they added with VBA and does that VBA activate the correct sheet when it is done adding columns?

In either case, if you edit all the "ActiveSheet"s in your code to explicitly name the sheet you want the code to work on, I think it will clear your problems.

[Dave]

This should be...

Dim LstRw As Long, LstCol As Long
With Sheets("overview")


Then move your first "End With" to here...

Dim lr As Long, lc As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(8, Columns.Count).End(xlToLeft).Column
End With

Also all of your "Cells" should be ".Cells" and "Range" should be ".Range"
You should also specify the sheet not use Active sheet and Dim statements are usually left on top. Not sure if these suggestions will resolve your troubles but there's no point trying complicated fixes until the simple is done first. Dave
edit: I see that SamT is continuing to offer assistance and I will bow out

[SamT]

Dave,

YOu don't have to do that. I'm not the greatest coder in the wolrd, Yano?:doh:

[abraham30]

Thank you very much for your kind help.

Sorry to bother you once again.

When I have used the macro, the pivot is auto-refreshed but the macro can not border the newly created column. can you plz check

Advance thanks

[SamT]

Try moving the LstRow and LstCol lines after the Pivot Table's "End With"

[abraham30]

I did as you suggested. The issue was solved. Thankss..

Another issue comes with printing pdf. while printing, all the columns are not taken into consideration.

[SamT]

This:
.FitToPagesWide = 1
.FitToPagesTall = False

is the same as:
.FitToPagesWide = 1
.FitToPagesTall = 1

Does that make a difference to you?

Otherwise, you may have to use the .Zoom Property

[abraham30]

Hello Sam,

I tried the whole process in most of my working sheet.

If I put
.FitToPagesTall = 1 in stead of 'False', then the pdf is not printing properly.

See the attachment for output which I save in ms word format

Zoom is also not working for few sheet.

Thanks

[SamT]

Abraham,

I am sorry, but I just don't know enough about printing to pdf to help you with this issue.

Please mark this thread as solved and post your printing issue in another thread with the title "Page Width When Excel Print To PDF".

Be sure to re-attach your xlsm file.