VBA file path location from named sheet

Pheonix2332

New Member
Joined
Feb 3, 2021
Messages
20
Office Version
  1. 2013
Platform
  1. Windows
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 -
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome! Does Target_Folder = SheetName.Range("A1").Value accomplish what you need?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
What is the name of the sheet with the file path?
 
Upvote 0
In that case try it like
VBA Code:
Public Target_Folder As String
Sub SplitDataset()
Target_Folder = Sheets("Location").Range("A2").Value
 
Upvote 0
Solution

Forum statistics

Threads
1,214,954
Messages
6,122,461
Members
449,085
Latest member
ExcelError

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top