Hi
I found a part of my answer in an old post but I would like extra help please. I'm not an expert at Excel or VBA.
I have a workbook that must be filled in by a number of different people each day, after each of 3 shifts. I have created a button with a VBA macro to save the workbook into a folder and filename supplied by a range of cell contents (A3, B3 C3, D3 and E3 where A3 is t be used as the folder name and the rest as a filename). I can't get the Cell A3 to use the existing folder on the desktop. The whole string gets saved onto the desktop as a filename AND I'm left with a workbook that now has the new filename.
I started the VBA module just to clear all entered cells at the end - hence the Sub still has this name.
I would really like to only save the sheet and not the whole workbook - and be left with just the original workbook template on my screen for the next person on shift to fill out.
I need to eventually have this workbook/worksheet saved into SharePoint in the 365 cloud where the Admins can access it once saved.
Sub ClearValuesOnly()
Dim Path As String
Dim filename As String
Dim folder As String
folder = Range("A3").Value
Path = "C:\Users\garth\Desktop\" & folder
ThisFile = Range("B3").Value & Range("C3").Value & Range("D3").Value & Format(Range("E3").Value, "dd-mm-yyyy")
ActiveWorkbook.SaveAs filename:=Path & ThisFile & ".xls"
Range("A3:D3").ClearContents
Range("B7:B28").ClearContents
Range("B40:B50").ClearContents
End Sub
I found a part of my answer in an old post but I would like extra help please. I'm not an expert at Excel or VBA.
I have a workbook that must be filled in by a number of different people each day, after each of 3 shifts. I have created a button with a VBA macro to save the workbook into a folder and filename supplied by a range of cell contents (A3, B3 C3, D3 and E3 where A3 is t be used as the folder name and the rest as a filename). I can't get the Cell A3 to use the existing folder on the desktop. The whole string gets saved onto the desktop as a filename AND I'm left with a workbook that now has the new filename.
I started the VBA module just to clear all entered cells at the end - hence the Sub still has this name.
I would really like to only save the sheet and not the whole workbook - and be left with just the original workbook template on my screen for the next person on shift to fill out.
I need to eventually have this workbook/worksheet saved into SharePoint in the 365 cloud where the Admins can access it once saved.
Sub ClearValuesOnly()
Dim Path As String
Dim filename As String
Dim folder As String
folder = Range("A3").Value
Path = "C:\Users\garth\Desktop\" & folder
ThisFile = Range("B3").Value & Range("C3").Value & Range("D3").Value & Format(Range("E3").Value, "dd-mm-yyyy")
ActiveWorkbook.SaveAs filename:=Path & ThisFile & ".xls"
Range("A3:D3").ClearContents
Range("B7:B28").ClearContents
Range("B40:B50").ClearContents
End Sub
