Link to home
Start Free TrialLog in
Avatar of Ted Penner
Ted PennerFlag for United States of America

asked on

Expand macro to ask for filename column

I had this question after viewing Save as filename macro.

This macro is intended to separate a selected number of rows into a file based which rows are selected.

What I would like to do now is expand it so that it is looking at the contents of the filename column to make that decision instead of the selected rows.

I have attached empty sample data which is quite large at over 260,000 rows.

The existing macro is listed in the code below as well as in the attached file.160412-Separation-EE-testing.xlsb
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try
Sub Macro()
Application.ScreenUpdating = False
Set oldWbk = ActiveWorkbook
Selection.Copy
Set newWbk = Workbooks.Add(Template:="Workbook")
ActiveSheet.Paste
Range("A1").EntireRow.Insert
Windows(oldWbk.Name).Activate
ActiveSheet.Range("1:1").Copy Destination:=newWbk.Sheets(1).Range("A1")
Windows(newWbk.Name).Activate
[a1].Select
Application.ScreenUpdating = True
strDirectory = GetFolder(ActiveWorkbook.Path)
If strDirectory = "" Then Exit Sub
Set rngFound = Nothing
Set rngFound = Range("1:1").Find("Filename")
If Not rngFound Is Nothing Then
    ActiveWorkbook.SaveAs rngFound.Offset(1).Value
End If
End Sub

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function                  

Open in new window

EDIT Corrected code

Regards
Avatar of Ted Penner

ASKER

I'm not sure what that did but, I was expecting to see 36 files, each with their own corresponding rows in the folder I chose.

No files were created at all, but no errors.
My original file now contains two headers and has a different filename called "filename".
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OUTSTANDING!

Fast and easy!   That's exactly what I was looking for.

One concern before I run on actual data.  In the actual data, there is some sorting, and some of the items have font colors applied to certain text items.

The resulting files should maintain whatever sort order was there originally.

As long as all of that is OK, I'll go ahead and test on actual data after my meeting.

Thank you again sir!!
the colors should remain but use a copy of your file at best
Of'course.  Can I trust that they will still remain in the same order from where they started?
they should because my code copy the whole sheet then deletes the lines which have a different filename without trying to re-sort it
OK great!