How to make macro code universal

[Kelsey]

Hello,
I've been using the below code to separate items in a column into different worksheets, and it's been working really well. The only issue that I have now is that I'd like to make this macro universal so that it is available in different worksheets. When I add it to my personal workbook (which I've found makes the macro universal) it doesn't run correctly. It's my assumption that something in the coding is referencing the personal workbook instead of the workbook that I'm trying to run the macro in, but I'm not yet familiar enough with the coding to recognize where this is or know what to change it to. Can anyone point me in the right direction?

Sub GenerateCSV()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

iCol = 20  '### Define your criteria column
strOutputFolder = "Leads 360"  '### Define your path of output folder

Set ws = ThisWorkbook.ActiveSheet  '### Don't edit below this line
Set rngLast = Columns(iCol).Find("*", Cells(1, iCol), , , xlByColumns, xlPrevious)
ws.Columns(iCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUnique = Range(Cells(2, iCol), rngLast).SpecialCells(xlCellTypeVisible)

If Dir(strOutputFolder, vbDirectory) = vbNullString Then MkDir strOutputFolder
For Each strItem In rngUnique
  If strItem <> "" Then
  ws.UsedRange.AutoFilter Field:=iCol, Criteria1:=strItem.Value
  Workbooks.Add
  ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=[A1]
  strFilename = strOutputFolder & "\" & strItem
  ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlCSV
  ActiveWorkbook.Close savechanges:=False
  End If
Next
ws.ShowAllData

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[NARAYANK991]

Hi Kelsey ,

I am not sure but it might be this statement :

Set ws = ThisWorkbook.ActiveSheet '### Don't edit below this line

Changing the highlighted part as follows might help.

Set ws = ActiveWorkbook.ActiveSheet '### Don't edit below this line

Narayan

 

[Kelsey]

Hi Narayan,
Thanks for commenting! I made this change and it looks like it's working, but it doesn't actually create the new folder and save the worksheets.

 

[Kelsey]

Here is a sample file, if this helps. Please note that the file we usually work with is larger and has more information than this. Additionally, we traditionally use a .csv version.

 

[NARAYANK991]

Hi Kelsey ,

Is it possible for you to upload the workbook from which you wish to run this code ? I will copy + paste this code into my Personal workbook and try running it.

Narayan

 

[NARAYANK991]

Hi Kelsey ,

It seems to be working ; I copied and pasted your posted code into one file ; I opened the file you uploaded , made it the active workbook and ran the macro.

5 CSV files were created in the Leads 360 folder.

Narayan

 

[Kelsey]

Hi Kelsey ,

It seems to be working ; I copied and pasted your posted code into one file ; I opened the file you uploaded , made it the active workbook and ran the macro.

5 CSV files were created in the Leads 360 folder.

Narayan

Okay, I'll have to do a little more digging and see what it's doing different here.
Thank you so much for your help!!

 

[KenU]

Kelsey,

Perhaps the directory is being created, but not where you want it. Since you are not giving a full path, the directory is probably being created in your default file location for Excel.

You can either specify a complete path or use something like below.

strOutputFolder = "Leads 360"'### Define your path of output folder
strPath = ActiveWorkbook.Path & "\"
strOutputFolder = strPath & strOutputFolder

The first line is from your original code to show you where this should go.

Hope that helps.

Regards,
Ken