Hi,
I am trying to use a vlookup to look at a cell in each of my split sheets then pick a folder location stored in a vlookup on a sheet within the document. I have done this by writing a vlookup as location then added & location to the file path.
it is sort of working but... it's saving all the files in the cell reference for the first split sheet rather than looking at each sheet so is saving them all in the same place!
the code I am using is below. Any suggestions how I can edit this?
the range in the lookup is a named range pointing to my lookup table
thanks
Sub Seperatesheets()
Dim Ws As Worksheet
Dim location As Variant
Dim WsEligibilitySheets As Worksheet
Dim wsfilesplitsheet As Worksheet
Dim wbtosave As Workbook
Dim filepathtosave As String
Application.ScreenUpdating = False
Set WsEligibilitySheets = Worksheets("Eligibility sheets")
Set wsfilesplitsheet = Worksheets("File Split Sheet")
location = Application.WorksheetFunction.VLookup(Range("E2"), Range("lookuprange"), 2, 0)
filepathtosave = "C:\2024\Test\2024\" & location
For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> WsEligibilitySheets.Name Or Ws.Name <> wsfilesplitsheet.Name Then
Ws.Copy
Set wbtosave = ActiveWorkbook
wbtosave.SaveAs _
Filename:=filepathtosave & "2023 Eligibility Sheets Test2 " & wbtosave.Worksheets(1).Name & ".xlsx", _
FileFormat:=51
wbtosave.Close True
End If
Next Ws
Application.ScreenUpdating = True
msgbox "complete"
End Sub