Consulting

Results 1 to 3 of 3

Thread: Reference a cell.

  1. #1
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location

    Reference a cell.

    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"

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    VBAX Regular
    Joined
    May 2018
    Location
    Sydney
    Posts
    57
    Location
    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.
    Last edited by Aussiebear; 12-09-2022 at 03:40 AM. Reason: Added code tags to supplied code
    If you only ever do what you can , you'll only ever be what you are.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •