How can I reset the “LastCell” in Excel?

LonnieCurrier
Copper Contributor
May 15, 2020
Riny_van_Eekelen Thanks for the information! No, the macro that deletes rows and columns was not saving the file before trying to use the "LastCell" function. The documents are received by e-mail, so they are read-only when opened. Now I need to figure out how to Save-As in the macro so that it will save to a location that the user would have permissions to. I tested Save-As in the macro, and it records my specific username in the path:
        ActiveWorkbook.SAVEAS Filename:= _
        "C:\Users\LonnieCurrier\Documents\FILENAME.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
Is there a variable/function that I can use in place of my username so that Save-As will work for other users?
- Riny_van_Eekelen
  Platinum Contributor
  May 15, 2020
  LonnieCurrier You can declare three variables e.g. Dim UserName, SaveToPath, FileName as String
  and then build the entire path, something like:
  SaveToPath = "C:\Users\" & UserName & "\Documents\" & FileName
  
  Then you SaveAs code could become:
  
  ActiveWorkbook.SAVEAS Filename:= SaveToPath, FileFormat:= _
  xlOpenXMLWorkbook, CreateBackup:=False
  
  Now, I didn't test this and hope I didn't make any typos. But, in case I did, I guess you can figure it out by yourself and make it work.
  - LonnieCurrier
    Copper Contributor
    May 15, 2020
    Riny_van_EekelenThanks again! I got this to work:
    
        ActiveWorkbook.SAVEAS Filename:= _
        Environ("UserProfile") & "\Desktop\TemporaryFileName.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Forum Discussion

How can I reset the “LastCell” in Excel?

Resources