macro to create and save multiple files with different file names

R

rkowalik

New Member
Joined
Oct 10, 2008
Messages
3
First, let me state that I have zero VBA experience....

I am trying to create a macro that creates a new excel file and then performs some tasks and then saves the resultant file with a automatically generated and different file name. More specifics are as follows:

I have a excel spreadsheet and the columns have a bunch of payment details (who I have to pay and how much and for what). In the rows, I have a list of multiple companies and each company can have multiple rows (I owe them for multiple invoices). When I process the payment, I have to copy the payment details in the rows for each company into a new file so that I can send the details along witht he payment. (Note that I use the subtotal function in excel the break down each payment by supplier.)

I have created a simple macro that takes the initial file, lets call it the "Master", and copies all the details into a new file. In the new file, the macro deletes all rows, except those associated with the top supplier on the list, now I want to tell the macro to save this new file under the name of the supplier (lets call it cell G3). The macro then goes back to the master and deletes the rows associated with the supplier in the file that was just created and saved and then stops. (My plan is to then assign a shortcut key and keep pressing it until I have rerun the macro the total number of times that I need to in order to get each file that I need for each supplier).

My problem is that I do not know how to tell the macro to save the file with a different file name (i.e cell G3) each time that it is run.

Really could use some help on this one. Thanks in advance.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hello,

let me see if I have this correct.

You have a spreadsheet with a list of different suppliers in Col G.
For each different supplier you want the data of all columns copied into another spreadsheet then saved with the suppliers name.
In the original spreadsheet you then want these rows deleted.

Where do you want the files saved?

Could the filenames already exist? If they do how do you propose to handle it?

Are their any other criteria you require for copying the rows to a new spreadsheet, or is it purely based on data in COl G?
 
Upvote 0
Here is a sample of what the data looks like

<TABLE style="WIDTH: 461pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=615 border=0 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 8009" width=219><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=108 height=17>Invoice Number</TD><TD class=xl19 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 154pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=205>Property Address</TD><TD class=xl19 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 164pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=219>Supplier</TD><TD class=xl19 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=83>Amount</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 2" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>8240513</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">123 main street</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Bob's machines</TD><TD class=xl21 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2052.44">$2,052</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl20 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Bob's machines Total</TD><TD class=xl21 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2052.44" x:fmla="=SUBTOTAL(9,D2:D2)">$2,052</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 2" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1245</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">234 west 3rd</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Steve's Auto</TD><TD class=xl21 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2384">$2,384</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 2" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1255</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">234 west 3rd</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Steve's Auto</TD><TD class=xl21 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="425">$425</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl20 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Steve's Auto Total</TD><TD class=xl21 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2809" x:fmla="=SUM(D4:D5)">$2,809</TD></TR></TBODY></TABLE>

Based on the above, I take all this and copy it into a new spreadsheet, then I delete all the info related to "Steve's Auto" and I want to save the file with the names "Bob's Auto." I then want to close the file that we just created and saved (i.e. with just the "Bob's machines" details) and then revert back to the original file that has all the info above. At this point I delete everying associated with "Bob's machines," create a copy in a new spreadsheet. Now, in the new spreadsheet I have "Steve's Auto" at the top of the list (and in reality I would have stuff below this and therefore would follow the steps above to delete anything not related to Steve). Again, I want to then save this file with the name "Steve's Auto," close the file, then revert back to the original....

Hope that helps
 
Upvote 0
This code is based on your sample data, so you will need to change range references to suit your actual data.

Code: 
Sub movepay()
Sheets("Master").Select
'remove subtotals
Cells.RemoveSubtotal
'create vendor list change "Range("I1" " to an unused column on your worksheet then change all subsequent references to "I"
Dim lr as integer
lr = Range("C" & Rows.Count).End(xlUp).Row
Columns("C:C").Select
    Range("C1:C" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I1" _
        ), Unique:=True
'create sheet for each vendor in list
Dim lrvendors As Integer
Dim c As Range
lrvendors = Range("I" & Rows.Count).End(xlUp).Row
Range("I2:I" & lrvendors).Name = "vendors"
For Each c In Range("vendors")
Sheets.Add
ActiveSheet.Name = c.Value
Next c
 
'copy Master sheet into each vendor sheet
For Each c In Range("vendors")
Sheets("Master").Cells.Copy Destination:=Sheets(c.Value).Range("A1")
Next c
'Deletes rows within each vendor sheet not for that vendor
Dim ws As Worksheet
Excluded = Array("Master")
For Each ws In Sheets
    x = Application.Match(ws.Name, Excluded, 0)
If IsError(x) Then
        For i = Last To 1 Step -1
        If (Cells(i, "C").Value) <> ws.Name Then
        Cells(i, "C").EntireRow.Delete
        End If
        Next i
Range("I:I").ClearContents
End If
Next ws
'saves each sheet as it's own file under worksheet name
Set MyBook = ActiveWorkbook
Application.DisplayAlerts = False
For Each ws In Sheets
    x = Application.Match(ws.Name, Excluded, 0)
 
    If ws.Range("A2").Value <> "" And IsError(x) Then
        MyFile = ws.Name
        MyPath = "ENTER THE PATH OF YOUR FOLDER WHERE FILE IS TO BE SAVED"
        ws.Copy
        ActiveWorkbook.SaveAs MyPath & MyFile
        ActiveWorkbook.Close
        MyBook.Activate
    End If
Next ws
'deletes the vendor sheets
For Each ws In Sheets
    x = Application.Match(ws.Name, Excluded, 0)
 
    If IsError(x) Then
    ws.Delete
End If
Next ws
'adds Subtotals back to master sheet
Sheets("Master").Select
Cells.Select
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'saves master file
MyBook.Save
Application.DisplayAlerts = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
119
harmless92
H
C
  • Solved
Save a New Invoice with a Macro already Included
Replies
2
Views
64
Code Ghost
C
K
  • Question
Excel Macro to Save to .pdf not working
Replies
9
Views
231
kpost
K
Y
  • Question
A more efficient approach to my data cleansing task
Replies
4
Views
225
Yamezz
Y
G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
253
bobaol
B

Forum statistics

Threads
1,214,518
Messages
6,119,988
Members
448,935
Latest member
ijat

Share this page

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