Excel Macro help

I have no experience of attempting this so I was hoping one of you guys may have a solution..

Attached is an excel sheet which shows on lines 2-7 how data is likely to be entered onto a spreadsheet. Blank data in a field means no data is necessary, but could be entered.
 
Lines 11-51 show the files that would have to be output.
 
Could you have a look at the possibility of producing the 5 output files from the lines 2-7.
Alex_Vaughan87Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
No file was attached to your post. :-)
dlmilleCommented:
Please upload your attachment.  I assume the attachment has the output file names/paths as well as the data, etc...

Dave
Alex_Vaughan87Author Commented:
Copy-of-Example-of-combination.xlsx

Sigh I do apologise, what a silly mistake!
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

dlmilleCommented:
Are each of the files to be created, Excel Files?  Same path as current workbook?  File1.xlsx, File2.xlsx, etc.?


Dave
Alex_Vaughan87Author Commented:
Yes that's right.

This is not really my question it is on behalf of a manager, I just happened to have an account and thought you guys would be useful :P

Neither of us have any idea :)
dlmilleCommented:
Is this what you're looking for, except instead of File1-File5 tabs, they're in separate files?
Combination-r1.xlsm
Alex_Vaughan87Author Commented:
Where are the macros behind it?

I'd like to see what the process is so I can learn it myself for the future, but that is what we're looking for :)
dlmilleCommented:
The app works on the activesheet, so be on the input sheet when you run the macro (you can run it from the Developer's ribbon, called generateFiles().

The app creates a tab for each of the files to be created (so make sure the input sheet is not protected), it makes a copy of the input tab to a new tab, then performs the operation, and finally, it generates each of the output files in the same workbook path as the active workbook you started with.

Here's the code:
Option Explicit
Sub generateFiles()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim fRange As Range
Dim lastRow As Long
Dim outSht As Worksheet
Dim i As Integer

    Application.DisplayAlerts = False
    
    On Error GoTo errHandler
    
    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    lastRow = wks.Cells.Find(what:="*", searchdirection:=xlPrevious).Row
    
    'Create File1 = columns ALL, unique based on column C
    wks.Copy after:=wks
    Set outSht = ActiveSheet
    outSht.Name = "File1"
    outSht.Range("$A$1:$S$7").RemoveDuplicates Columns:=3, Header:=xlYes
    
    'Create File2 = columns C and P
    Set outSht = wkb.Sheets.Add(after:=outSht)
    outSht.Name = "File2"
    
    wks.Range("C1:C" & lastRow).Copy
    outSht.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    wks.Range("P1:P" & lastRow).Copy
    outSht.Range("B1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    'Create File3 = columns C and Q
    Set outSht = wkb.Sheets.Add(after:=outSht)
    outSht.Name = "File3"
    
    wks.Range("C1:C" & lastRow).Copy
    outSht.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    wks.Range("Q1:Q" & lastRow).Copy
    outSht.Range("B1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    'Create File4 = columns C and R
    Set outSht = wkb.Sheets.Add(after:=outSht)
    outSht.Name = "File4"
    
    wks.Range("C1:C" & lastRow).Copy
    outSht.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    wks.Range("R1:R" & lastRow).Copy
    outSht.Range("B1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    'Create File5 = columns C and S
    Set outSht = wkb.Sheets.Add(after:=outSht)
    outSht.Name = "File5"
    
    wks.Range("C1:C" & lastRow).Copy
    outSht.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    wks.Range("S1:S" & lastRow).Copy
    outSht.Range("B1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    'now write out the files
    For i = 1 To 5
        Application.StatusBar = "Generating File" & i & ".xlsx...."
        wkb.Sheets("File" & i).Move
        ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\File" & i & ".xlsx"
        ActiveWorkbook.Close
    Next i
    
    MsgBox "Process Completed - File1-File5 Created", vbOKOnly
    GoTo gracefulExit
    
errHandler:
    MsgBox "Error Processing:  Err Number: " & Err.Number & ", Desc: " & Err.Description, vbCritical, "Aborting..."
    
gracefulExit:
    wks.Activate
    Application.StatusBar = False
    
End Sub

Open in new window


See attached file.

Enjoy!

Dave
Combination-r1.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.