modify vba code so it works no matter what the name of the file is

[gmccray]

when the file is downloaded it always has a random name that begins with "report...….." the numbers part is random. I am trying to modify the code so that it will work no matter the name of the file and worksheet.

see code below:

Option Explicit
Sub MORE_ReportFormatter()
'
' MORE_ReportFormatter Macro
'
'
' Dim sheets As sheets
' Set sheets("report1551216764815") = sheets
ChDir "C:\Users\gmccr\OneDrive\Documents\CodingIssues\VBA"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\gmccr\OneDrive\Documents\CodingIssues\VBA\More_CentralWesternMassCT.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
sheets("report1551216764815").Select
sheets("report1551216764815").Name = "MORE_CentralWesternMassCt"
Range("A1:P1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
End Sub



can you help?

 

[Fluff]

How many sheets are there in the file?

 

[gmccray]

Just the one.

 

[Fluff]

In that case you can simply use the index number like

Sheets(1).Name="..."

 

[gmccray]

In that case you can simply use the index number like

Sheets(1).Name="..."

which would require me to change it every time I have to run the report, correct? If so, I was hoping to aviod having to have to do that. is that possible?

 

[gmccray]

which would require me to change it every time I have to run the report, correct? If so, I was hoping to aviod having to have to do that. is that possible?

Oh, I think I see what you are saying. Let me try it, can I get back to you on it?

 

[Fluff]

Let me try it, can I get back to you on it?

But of course