Extract and Loop Macro - Extract Range and save in multiple workbooks

sush23

New Member
Joined
Aug 8, 2011
Messages
24
Hi there VBA Wizards,

I have a workbook with an output tab called "Template." The vlookup formulas within this tab will populate the template depending on what number I put in a cell. The numbers are assigned to a person, so it will populate all the information related to that person in the template. I have a macro that will extract this template and loop through all the possible numbers (defined names) and create separate pdf files for each number/person. The problem I am running into is that I need to create EXCEL files not PDF Files. The macro works like a charm, but from the research that I did it looks like the "Export as Fixed Format" function can be used to only create pdf and XPS. Can anyone help me tweak this macro to do the exact same thing it's doing but output Excel files rather than PDF? This code seems so simple and efficient! ANY help would be highly appreciated.

I removed the file path I save the files in to make the macro more readable, replaced with [Insert File Path]

VBA Code:
[/COD
Sub PDF_Exporter()


    pdfName = ActiveSheet.Name
    'ChDir ActiveWorkbook.Path & "\"
    fileSaveName = Range("PDF_File_Name")
    
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "[Insert File Path]" & fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    
    'MsgBox "File Saved " & " " & fileSaveName
End Sub

Sub ID_Counter()

   Dim LCounter As Integer
   Loop_Start = Range("Starting_Person")
   Loop_Last = Range("Ending_Person")
   
   For LCounter = Loop_Start To Loop_Last
      Range("Person_ID") = LCounter
      PDF_Exporter
      'MsgBox LCounter
   Next LCounter

End Sub

E]
 

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.

Forum statistics

Threads
1,175,472
Messages
5,897,650
Members
434,667
Latest member
Ftdsa

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
Top