[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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.
0
Alex_Vaughan87
Asked:
Alex_Vaughan87
  • 4
  • 3
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
No file was attached to your post. :-)
0
 
dlmilleCommented:
Please upload your attachment.  I assume the attachment has the output file names/paths as well as the data, etc...

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

Sigh I do apologise, what a silly mistake!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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


Dave
0
 
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 :)
0
 
dlmilleCommented:
Is this what you're looking for, except instead of File1-File5 tabs, they're in separate files?
Combination-r1.xlsm
0
 
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 :)
0
 
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
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now