• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split into new workbooks based on column value along with existing macro copied to the new workbooks

R

roshalphonso

New Member
Hi Team,

I need help to create a vba code that will help me split an existing workbook into multiple new workbooks based on Team (column J) of the attached sample. Along with creating a new workbook, it should also copy any of the existing vba scripts into each of the new workbooks and save them as .xlsm files.
 

Attachments

  • ROSH DATA.xlsm
    35.7 KB · Views: 6
Hi, you can start with the Workbook.SaveCopyAs method like explained in VBA help or​
as your attachment has a single worksheet containing all the VBA procedures so you can directly use the Worksheet.Copy method and​
as it depends on the missing informations about the expected result workbooks, an attachment should help,​
as guessing can't be helping neither coding …​
​
 
Like already explained in my previous post so share at least the mandatory informations about the expected result workbooks,​
a result attachment should help, as guessing can't be coding !​
​
 
There are 7 teams in Column J (Header as Team) of my attached data. The expected result is that I need one workbook created for each team named as PROTECT TEAM 1, PROTECT TEAM 2 and so on.........with their respective data (which would have otherwise taken me enormous time, if I would have to filter each time and create a new workbook). Additionally I need the new workbooks created as a .xlsm file with all of the existing vba codes from the parent file to be reflected in each of the new workbook.

I had got a code from a google search to do this, which i have updated in the this updated attachment here. Refer VBA code named as Sub ExtractToNewWorkbook(). BUT i cant get that to work on my data. Neither does it copy the existing vba code from parent to new workbooks

Sorry i dont have a result attachment, but hopefully the above explanation should be enough.

Your assistance is appreciated.
 

Attachments

  • ROSH DATA.xlsm
    38 KB · Views: 5
  • Column J contains an error : is it normal or that is just both bad attachments ?!

  • Confirm all needed VBA procedures to be in result workbooks are in your source workbook Sheet1 worksheet module so Module1 is useless …
 
roshalphonso said:
refer VBA code named as Sub ExtractToNewWorkbook(). BUT i cant get that to work on my data. Neither does it copy the existing vba code from parent to new workbooks
Click to expand...

I took a peek at the code in your Module1 (Sub ExtractToNewWorkbook). It isn't good. There are undeclared variables and also declared variables that are never assigned a value, but are used in the code. I cannot even get it to compile becuase of syntax errors, so I wonder how you got it to run even to a level that would allow you to assess that it did not work on your data.
 
Hi Marc,

Column J contains an error only for one record. So that can be deleted/ignored.

Yes I Confirm that all needed VBA procedures to be in result workbooks are in my source workbook Sheet1 worksheet module. so Module1 is useless, which is the code that i need help with …
 
Yes thats correct. Error values are deleted
Technically all error values also should have been transported into a new workbook. Can you make that happen as well?
 
Confusing / unclear ! If you delete any error in column J before to launch the VBA procedure so no problem …​
Now you change the rules but as a workbook name can't be a cell error so well elaborate at least with details !​
​
 
To keep it simple, lets work with the assumption that error values in column J are deleted. The only values in column J will be the team names 1 to 7. Hope that helps
 
Last chance : so difficult to explain to which workbook the errors must be exported ?!​
​
 
Hi Mark,

I do not understand your question, neither I have a great technical mindset like you to be able to define it to your style of understanding. I feel that you are being difficult. Neither do i understand what you mean by saying "last chance", makes it sound like I am begging for a solution.

For simplicity, I am re-attaching the Excel workbook. I have deleted the error values in column J. So that just leaves us with Team 1 to Team 7. I need help with macro to create a separate workbook for each of these teams based on the column value J (requirement 1) and also ensure that each of the individual team workbooks that get created have the existing macros in the parent file (requirement 2).

Hope this helps. If not possible, then its fine. Thank you for all your help.
 

Attachments

  • ROSH DATA.xlsm
    35.2 KB · Views: 5
Alphonzo, delete first the useless Module1 then according to your last explanation​
try this starter VBA demonstration to paste only to ThisWorkbook module :​
Code: 
Option Explicit
Sub Demo1()
     Dim S As Boolean, V
         S = Saved
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    With Sheet1.UsedRange
        .Parent.Copy
        .Columns(10).AdvancedFilter 2, , .Range("AF1"), True
     For Each V In .Range("AF2", .Range("AF1").End(xlDown)).Value
         ActiveWorkbook.Sheets(1).UsedRange.Clear
        .Range("AF2") = V
        .AdvancedFilter 2, .Range("AF1:AF2"), ActiveWorkbook.Sheets(1).[A1]
         ActiveWorkbook.SaveAs Path & "\" & V, 50
     Next
        .Range("AF1").CurrentRegion.Clear
    End With
        .Speech.Speak "Done!", True
         ActiveWorkbook.Close
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
         Saved = S
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
You must log in or register to reply here.
Back
Top