Automating a task

[Needsomehelp10]

Every month I run a bunch of reports and then format them to a specific format. I have recorded a macro that will automatically format it for me. Now I am trying to add to this macro so that it will rename the tab it is on to the name I want. This name is something like ACT + the last two digits of the data in A1. So the tab name when done correctly would be something like ACT02. Does anyone know the code that would need to be added to automate this task. Below is my current macro.



Sub PLformat()
'
' PLformat Macro
' P&L Format
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Range("A2").Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
Range("A2").Select
ActiveCell.FormulaR1C1 = "Statement of Operations"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Period Ending August 20, 2017"
Range("B3").Select
Selection.ClearContents
Cells.Select
Range("B3").Activate
Selection.Columns.AutoFit
Range("C4").Select
ActiveCell.FormulaR1C1 = "PTD"
Range("E4").Select
ActiveCell.FormulaR1C1 = "PTD"
Range("G4").Select
ActiveCell.FormulaR1C1 = "QTD"
Range("K4").Select
ActiveCell.FormulaR1C1 = "YTD"
Range("C5").Select
ActiveCell.FormulaR1C1 = "8/17/2017"
Range("C5").Select
Selection.NumberFormat = "[$-en-US]mmm-yy;@"
Selection.Copy
Range("E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "8/17/2016"
Range("C5:E5").Select
Selection.Copy
Range("G5").Select
ActiveSheet.Paste
Range("K5").Select
ActiveSheet.Paste
Range("C4:C5,E4:E5,G4:G5,I4:I5,K4:K5,M4:M5").Select
Range("M4").Activate
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C7:C180,E7:E180,G7:G180,I7:I180,K7:K180,M7:M180").Select
ActiveWindow.SmallScroll Down:=-3
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""-""??_);_(@_)"
Range("C7").Select
ActiveWindow.FreezePanes = True
ActiveCell.SpecialCells(xlLastCell).Select
ActiveWindow.SmallScroll Down:=-180


End Sub

 

[Needsomehelp10]

Re: Need help automating a task

Dim FileName As String
Dim FilePath As String
FileName = "Store " & Right(Range("A1").Value, 5)
FilePath = "R:\TAX MGMT\TSP Share\2017"
ActiveSheet.SaveAs FileName:=FilePath & "" & FileName, FileFormat:=51
End Sub


However, when I run this is it works perfectly fine and saves the file with the correct name, Store 00165. For some reason the (8) August part of the file path is messing it up.

 

[Fluff]

Re: Need help automating a task

You missing a backslash

FilePath = "R:\TAX MGMT\TSP Share\2017\(8) August"
ActiveSheet.SaveAs FileName:=FilePath & [COLOR=#ff0000]"\ "[/COLOR] & FileName, FileFormat:=51

(but you need to remove the space that comes after)

 

[Needsomehelp10]

One last question guys. What would be the best way to run this on multiple different workbooks at once? Or have it so that it will run through my designated downloaded list of files?