Sep 01 2020 05:00 PM
I am new to VBA. I tried recording a Macro for a particular task. The files I run this macro will have the range that keep changing every time. Is there a way I can edit this Macro to choose all the columns that is no empty? Below is the code I created with the record macro. Thank you in advance :)
Sub Smart_biz_report()
'
' Smart_biz_report Macro
' Smart Biz report formating
'
'
ActiveWindow.ActivateNext
ActiveWindow.ActivateNext
ActiveWindow.ActivateNext
ActiveWindow.ActivateNext
Windows("Sales report - SBO02371.xlsx").Activate
Columns("U:U").Select
Selection.Insert Shift:=xlToRight
Range("U1").Select
ActiveCell.FormulaR1C1 = "Category"
Range("U2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
Range("U2").Select
Selection.Copy
Range("T2").Select
Selection.End(xlDown).Select
Range("U1284").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Columns("U:U").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Range("V1").Select
ActiveCell.FormulaR1C1 = "Category 2"
Range("V2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Sales report - Category.xls]Categories'!R2C1:R224C2,2,TRUE)"
Range("V2").Select
Selection.Copy
Range("U2").Select
Selection.End(xlDown).Select
Range("V1284").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Columns("V:V").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("V:V").Select
Selection.Replace What:="0", Replacement:="Other", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Range("V2").Select
ActiveWindow.LargeScroll Down:=1
Range("V27").Select
ActiveWindow.LargeScroll Down:=1
Range("V52").Select
ActiveWindow.LargeScroll Down:=1
Range("V77").Select
ActiveWindow.LargeScroll Down:=1
Range("V102").Select
ActiveWindow.LargeScroll Down:=1
Range("V127").Select
ActiveWindow.LargeScroll Down:=1
Range("V152").Select
ActiveWindow.LargeScroll Down:=1
Range("V177").Select
ActiveWindow.LargeScroll Down:=1
Range("V202").Select
ActiveWindow.LargeScroll Down:=1
Range("V227").Select
ActiveWindow.LargeScroll Down:=1
Range("V252").Select
ActiveWindow.LargeScroll Down:=1
Range("V277").Select
ActiveWindow.LargeScroll Down:=1
Range("V302").Select
ActiveWindow.LargeScroll Down:=1
Range("V327").Select
ActiveWindow.LargeScroll Down:=1
Range("V352").Select
ActiveWindow.LargeScroll Down:=1
Range("V377").Select
ActiveWindow.LargeScroll Down:=1
Range("V402").Select
Selection.End(xlUp).Select
Rows("1:1").Select
Range("V1").Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("V1").Select
Selection.AutoFilter
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Sales report - SBO02371'!R1C1:R1284C30", Version:=6).CreatePivotTable _
TableDestination:="", TableName:="PivotTable6", DefaultVersion:=6
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Sheets("Sheet1").Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields("textBox2")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Category 2")
.Orientation = xlColumnField
.Position = 1
End With
Sheets("Sales report - SBO02371").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("textBox26"), "Sum of textBox26", xlSum
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Sum of textBox26")
.NumberFormat = "$#,##0.00"
End With
End Sub
Sep 03 2020 06:26 AM
Hi Sibujoy,
Since I cannot run this macro it is difficult to determine which cells you are referring to. You start out selecting Column U, shifting it to the right, then labeling the new U. Later, you do the same for column V. Where is the "blank" column at that you want to select? Assuming I understand what you are looking for, if, starting with Column U, you named the range and then put the named range in your macro, it will follow the named range when performing the next tasks. To name a range you can select U to highlight the column, then in the upper left box (right above column "A") where it says 'U1' click in that box and type in a name like "START" then press enter. (If it does not take the name, it will remove it and put U1 back in.)
In your macro where you have "U:U" you can now use "START". As another column is placed before column U, the START range will now be in column V and so forth. To see how it is working, you can highlight the START row in some fashion and then when in VBA, under debug, STEP INTO - F8 and run your macro one step at a time to see how this works. Let me know if this helps.
Sep 06 2020 09:31 PM