Consulting

Results 1 to 3 of 3

Thread: Problem with VBA in Excel - following code giving me an error

  1. #1

    Problem with VBA in Excel - following code giving me an error

    ' Get the filename from a specific cell, for example, cell A1
          CellValue = ThisWorkbook.Sheets("FORM").Range("D14" & "-" & "E8").Value   (<--------THIS ONE)
    ' Combine folder path and cell value as the file name
        FileName = CellValue & ".xlsm"
    ' Combine folder path and file name
        FilePath = FilePath & FileName
    ' Save the active workbook with the specified file path
        ThisWorkbook.SaveAs FilePath
    This code is supposed to take two values from the excel spreadsheet and place them in the name of the newly saved file.
    I am getting an error from the code above where it says THIS ONE
    The file will be named NAME-20240612.xlsm
    Can anyone help... Thanks Adam

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Either:
    CellValue = ThisWorkbook.Sheets("FORM").Range("D14").Value & "-" & ThisWorkbook.Sheets("FORM").Range("E8").Value
    or
    With ThisWorkbook.Sheets("FORM")
      CellValue = .Range("D14").Value & "-" & .Range("E8").Value
    End With
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Well that worked perfectly. I thought using the "ThisWorkbook...." twice would only pickup one value.
    You are awesome thank you so much!!!

Posting Permissions

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