Automating a task

Needsomehelp10 · Sep 5, 2017

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

Michael M · Sep 5, 2017

Re: Need help automating a task

This is untested......but try

Code:

Sub PLformat()
'
' PLformat Macro
' P&L Format
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Range("A2").Copy Range("A1")
Range("A2").Value = "Statement of Operations"
Range("A3").Value = "Period Ending August 20, 2017"
Range("B3").ClearContents
Cells.Columns.AutoFit
Range("C4").Value = "PTD"
Range("E4").Value = "PTD"
Range("G4").Value = "QTD"
Range("K4").Value = "YTD"
Range("C5").Value = "8/17/2017"
Range("C5").NumberFormat = "[$-en-US]mmm-yy;@"
Range("C5").Copy Range("E5")
Range("E5").Value = "8/17/2016"
Range("C5:E5").Copy Range("G5")
Range("C5:E5").Copy Range("K5")
Range("C4:C5,E4:E5,G4:G5,I4:I5,K4:K5,M4:M5").Font.Bold = True
With Range("C4:C5,E4:E5,G4:G5,I4:I5,K4:K5,M4:M5")
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
With Range("C7:C180,E7:E180,G7:G180,I7:I180,K7:K180,M7:M180")
.Style = "Comma"
.NumberFormat = "_(* #,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL]  );_(* (#,##0);_(* ""-""??_);_(@_)"
End With
Range("C7").Select
ActiveWindow.FreezePanes = True
ActiveCell.SpecialCells(xlLastCell).Select
ActiveSheet.Name = "ACT" & Right(Range("A1").Value, 2)
End Sub

My Aswer Is This · Sep 5, 2017

Re: Need help automating a task

Try this:

Code:

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 = "_(* #,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL]  );_(* (#,##0);_(* ""-""??_);_(@_)"
Range("C7").Select
ActiveWindow.FreezePanes = True
ActiveCell.SpecialCells(xlLastCell).Select
ActiveWindow.SmallScroll Down:=-180
[COLOR=#ff0000]ActiveSheet.Name = "ACT" & Right(Cells(1, 1), 2)[/COLOR]
[COLOR=#ff0000]End Sub[/COLOR]

Needsomehelp10 · Sep 5, 2017

Re: Need help automating a task

Both of these work perfectly. Thanks to both of you. Now for one more step. Is it possible add another step to the code so that it automatically saves is in a specific folder with the name Store and the last 5 numbers of cell A1? So it would save as Store00102. Once again thanks for y'alls help already.

My Aswer Is This · Sep 6, 2017

Re: Need help automating a task

Since Michael cleaned up your script I added your second request to the bottom of his script.
You will need to modify the path marked in red.
This script saves file as Macro Enabled.

Code:

Sub PLformat()

' PLformat Macro
' P&L Format
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Range("A2").Copy Range("A1")
Range("A2").Value = "Statement of Operations"
Range("A3").Value = "Period Ending August 20, 2017"
Range("B3").ClearContents
Cells.Columns.AutoFit
Range("C4").Value = "PTD"
Range("E4").Value = "PTD"
Range("G4").Value = "QTD"
Range("K4").Value = "YTD"
Range("C5").Value = "8/17/2017"
Range("C5").NumberFormat = "[$-en-US]mmm-yy;@"
Range("C5").Copy Range("E5")
Range("E5").Value = "8/17/2016"
Range("C5:E5").Copy Range("G5")
Range("C5:E5").Copy Range("K5")
Range("C4:C5,E4:E5,G4:G5,I4:I5,K4:K5,M4:M5").Font.Bold = True
With Range("C4:C5,E4:E5,G4:G5,I4:I5,K4:K5,M4:M5")
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
With Range("C7:C180,E7:E180,G7:G180,I7:I180,K7:K180,M7:M180")
.Style = "Comma"
.NumberFormat = "_(* #,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL]   );_(* (#,##0);_(* ""-""??_);_(@_)"
End With
Range("C7").Select
ActiveWindow.FreezePanes = True
ActiveCell.SpecialCells(xlLastCell).Select
ActiveSheet.Name = "ACT" & Right(Range("A1").Value, 2)

'New Part
Dim FileName As String
Dim FilePath As String
FileName = "Store" & Right(Range("A1").Value, 5)
[COLOR=#ff0000]FilePath = "C:\Users\owner\Documents" 'Modify this line with proper path[/COLOR]
ActiveSheet.SaveAs FileName:=FilePath & "" & FileName, FileFormat:=52
End Sub

Needsomehelp10 · Sep 14, 2017

Re: Need help automating a task

@ My Aswer is This, when I run that it is saving the it with a name that is made up of the File name combined with part of the file path. Also it is not saving it in the correct spot but rather one step back. For example it would save it in C:\Users\owner\ instead of saving it in C:\Users\owner\Documents. Not sure if this is a coincidence but the last part of the file path which giudes it to correct spot is what is being added to the beginning of the file name. Any ideas. Thank you for your help.

My Aswer Is This · Sep 14, 2017

Re: Need help automating a task

Please post the correct path here:

Needsomehelp10 · Sep 14, 2017

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\(8) August"
ActiveSheet.SaveAs FileName:=FilePath & "" & FileName, FileFormat:=51
End Sub

Here you go. I had previously edited the line

ActiveSheet.SaveAs FileName:=FilePath & "" & FileName, FileFormat:=51

to

ActiveSheet.SaveAs FileName:=FileName, FileFormat:=51

This solved the issue of the file name not including (8) August in the title anymore. However, when I did that it would not save at the intended file path.

My Aswer Is This · Sep 15, 2017

Re: Need help automating a task

Not sure why you think you can change around the script I wrote to what you think it should be and then wonder why it's not working.

FileName is the entire name of the file.

FilePath is the path where the file is to be stored.

Are you now saying this is not the correct way to get the Filename:

You earlier said: Quote: "with the name Store and the last 5 numbers of cell A1

Not sure what these last 5 numbers are.

So maybe you need to explain again how we get the filename

And will the filepath always be: "R:\TAX MGMT\TSP Share\2017\(8) August"

And I'm not sure that is a proper path but I'm no expert and cannot test that.

Needsomehelp10 · Sep 15, 2017

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\(8) August"
ActiveSheet.SaveAs FileName:=FilePath & "" & FileName, FileFormat:=51
End Sub

The issue Im running into with this script is the file name of the saved sheet is (8) AugustStore 00165. The (8) August portion is coming from the file path I believe. I want the file name to just be Store 00165. The 00165 is the last 5 numbers of cell A1. In regards to the file path, it will change every month. Next month it will not be (8) August but instead (9) September.

Thanks for the help @ My Aswer is This. I appreciate your time and feedback

Automating a task

Needsomehelp10

New Member

Excel Facts

Michael M

Well-known Member

My Aswer Is This

Well-known Member

Needsomehelp10

New Member

My Aswer Is This

Well-known Member

Needsomehelp10

New Member

My Aswer Is This

Well-known Member

Needsomehelp10

New Member

My Aswer Is This

Well-known Member

Needsomehelp10

New Member

Similar threads

Forum statistics

Share this page

Automating a task

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock