Reference a cell.

[SteveABC]

Hi.

How do I change the FName so it names the file after cell O3 in sheets "Input"




FPath = "Q:\Finance"
FName = "WMT Client Report " & Format(Date, " ddmmyy") & ".xlsx"





ActiveSheet.Unprotect
Sheets("SUMMARY").Select
ActiveSheet.Unprotect
Sheets("TOTAL WEEK").Select
ActiveSheet.Unprotect
Sheets(Array("SUMMARY", "TOTAL WEEK")).Select
Sheets(Array("SUMMARY", "TOTAL WEEK")).Copy
Sheets("SUMMARY").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("TOTAL WEEK").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
FPath = "Q:\Finance"
FName = "WMT Client Report " & Format(Date, " ddmmyy") & ".xlsx"

[georgiboy]

Maybe:

Sub test()
Dim fPath As String
Dim fName As String

fPath = "Q:\Finance\"
fName = Sheets("Input").Range("O3").Value & ".xlsx"

Sheets("SUMMARY").Unprotect
Sheets("TOTAL WEEK").Unprotect
Sheets(Array("SUMMARY", "TOTAL WEEK")).Copy
Sheets(1).UsedRange.Value = Sheets(1).UsedRange.Value
Sheets(2).UsedRange.Value = Sheets(2).UsedRange.Value
End Sub

[Grade4.2]

You can change the FName to reference cell O3 in the "Input" sheet using the Sheets and Range objects in VBA. Here is an example of how you can modify the code to achieve this:



FPath = "Q:\Finance"
' Reference the "Input" sheet
Dim inputSheet As Worksheet
Set inputSheet = Sheets("Input")
' Reference the cell O3 in the "Input" sheet
Dim filenameCell As Range
Set filenameCell = inputSheet.Range("O3")
' Use the value in cell O3 as the filename
FName = filenameCell.Value & Format(Date, " ddmmyy") & ".xlsx"
' Rest of your code goes here
Note that the code above assumes that the cell O3 in the "Input" sheet contains the desired filename. You may need to modify the code to handle situations where the cell is empty or contains an invalid filename.