VBA file path location from named sheet

[Pheonix2332]

Good afternoon everyone, been struggling to see what am doing wrong with this code - been trying to have the documents saved is a specific location determined in cell D4. I had the code working to produce them directly onto my desktop but now i have had a few colleges ask to use the same macro program and they are wanting filepath they choose so it can be organised y date, is there a way I can have “<target folder path>” be chosen from a designated cell ? As am loosing my mind trying to get this to work

so far the code I have is this -

Option Explicit
Const Target_Folder As String = "<Target Folder Path>"
Dim wsSource As Worksheet, wsHelper As Worksheet
Dim LastRow As Long, LastColumn As Long
Sub SplitDataset()
Dim collectionUniqueList As Collection
Dim i As Long
Set collectionUniqueList = New Collection Set wsSource = ThisWorkbook.Worksheets("masters standards")
Set wsHelper = ThisWorkbook.Worksheets("Helper") wsHelper.Cells.ClearContents
With wsSource .AutoFilterMode = False LastRow = .Cells(Rows.Count, "A").End(xlUp).Row LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
If .Range("A2").Value = "" Then
GoTo Cleanup End

If Call Init_Unique_List_Collection(collectionUniqueList, LastRow) Application.DisplayAlerts = False For i = 1 To collectionUniqueList.Count SplitWorksheet (collectionUniqueList.Item(i)) Next i ActiveSheet.AutoFilterMode = False End With Cleanup: Application.DisplayAlerts = True
Set collectionUniqueList = Nothing
Set wsSource = Nothing
Set wsHelper = Nothing
End Sub

Private Sub Init_Unique_List_Collection(ByRef col As Collection, ByVal SourceWS_LastRow As Long)
Dim LastRow As Long, RowNumber As Long wsSource.Range("G2:G" & SourceWS_LastRow).Copy wsHelper.Range("A1")
With wsHelper If Len(Trim(.Range("A1").Value)) > 0 Then LastRow = .Cells(Rows.Count, "A").End(xlUp).Row .Range("A1:A" & LastRow).RemoveDuplicates 1, xlNo LastRow = .Cells(Rows.Count, "A").End(xlUp).Row .Range("A1:A" & LastRow).Sort .Range("A1"), Header:=xlNo LastRow = .Cells(Rows.Count, "A").End(xlUp).Row On Error Resume Next For RowNumber = 1 To LastRow col.Add .Cells(RowNumber, "A").Value, CStr(.Cells(RowNumber, "A").Value) Next RowNumber
End If
End With
End Sub

Private Sub SplitWorksheet(ByVal Category_Name As Variant)
Dim wbTarget As Workbook Set wbTarget = Workbooks.Add With wsSource With .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)) .AutoFilter .Range("G1").Column, Category_Name .Copy wbTarget.Worksheets(1).Paste wbTarget.Worksheets(1).Name = Category_Name wbTarget.SaveAs Target_Folder & Category_Name & ".xlsx", 51 wbTarget.Close False
End With
End With
Set wbTarget = Nothing
End Sub

 

[severynm]

Welcome! Does Target_Folder = SheetName.Range("A1").Value accomplish what you need?

 

[Pheonix2332]

Welcome! Does Target_Folder = SheetName.Range("A1").Value accomplish what you need?

I shall give it a go and let you know hopefully I don’t need to redo the whole script

 

[Pheonix2332]

Welcome! Does Target_Folder = SheetName.Range("A1").Value accomplish what you need?

Thanks for the help severynm but now I have tried this I am getting compile error for SheetName variable not defined

 

[Fluff]

You will not be able to declare Target_Folder as a constant if you want to use a cell address.
Whilst you can have it as a public variable it will need to be assigned in the actual code.

 

[Pheonix2332]

You will not be able to declare Target_Folder as a constant if you want to use a cell address.
Whilst you can have it as a public variable it will need to be assigned in the actual code.

Thanks for the advice - I removed the constant from the beginning and relocated the suggested code before the files get saved in the code as a public variable and still getting variable for sheet name not defined, apologies for this everyone when it comes to VBA I am rusty beyond belief- last time I remember using it was several years ago and even then at limited need

 

[Fluff]

What is the name of the sheet with the file path?

 

[Pheonix2332]

What is the name of the sheet with the file path?

Hi fluff - sheet name with file path is named location and ideally taken from cell A2

 

[Fluff]

In that case try it like

Public Target_Folder As String
Sub SplitDataset()
Target_Folder = Sheets("Location").Range("A2").Value

 

[Pheonix2332]

In that case try it like

Public Target_Folder As String
Sub SplitDataset()
Target_Folder = Sheets("Location").Range("A2").Value

This has worked thank you ?