0

I'm new to VBA, and using a macro to split a workbook into many files, based on some criteria, and then split that files again in multiple sheets, based on other criteria.

After splitting the end file into multiple sheets, I want to copy a sheet from the main file, to every destination file, and this copied sheet should be at the end of all other newly created sheets in the new files.

My code is having a problem with the line I am trying to copy that sheet with. If I don't try to copy that sheet at the end of the code, every thing works fine, and I get many destination files splitted into multiple sheets inside. If I try to add the line of code to copy the sheet I need to be copied as it is, I get just one destination file (1st split) and the code bugs at that line.

Here is the whole code to do so: 

Sub Split()

Dim wswb As String
Dim wssh As String

wswb = ActiveWorkbook.Name
wssh = ActiveSheet.Name

vColumn = InputBox("Please indicate which column you would like to split by", "Column selection")

Columns(vColumn).Copy
Sheets.Add
ActiveSheet.Name = "_Summary"
Range("A1").PasteSpecial
Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes

vCounter = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To vCounter
    vFilter = Sheets("_Summary").Cells(i, 1)
    Sheets(wssh).Activate
    ActiveSheet.Columns.AutoFilter field:=6, Criteria1:="100%"
    ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter
    Cells.Copy
    Workbooks.Add
    Range("A1").PasteSpecial

    ActiveSheet.Name = "Master"


    dspColumn = "D"


    Columns(dspColumn).Copy
    Sheets.Add
    ActiveSheet.Name = "dspSummary"
    Range("A1").PasteSpecial
    Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes

    dspCounter = Range("A" & Rows.Count).End(xlUp).Row
    Splitcode = Range("A" & Rows.Count).End(xlUp).Row

    For j = 2 To dspCounter
        dspFilter = Sheets("dspSummary").Cells(j, 1)
        Sheets("Master").Activate
        ActiveSheet.Columns.AutoFilter field:=Columns(dspColumn).Column, Criteria1:=dspFilter
        Cells.Copy
        Sheets.Add
        ActiveSheet.Name = Left(dspFilter, 30)

        Range("A1").PasteSpecial
    Next j
    Sheets("Master").Delete
    Sheets("dspSummary").Delete

    If vFilter <> "" Then
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Results\" & vFilter
    Else
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Results\_Empty"
    End If


    ActiveWorkbook.Close
    Workbooks(wswb).Activate
    Sheets("Reasons codes").Copy After:=Workbooks(ThisWorkbook.Path & "\Split Results\" & vFilter & ".xlsx").Sheets(Sheets.Count)
Next i
Sheets("_Summary").Delete

End Sub
Sub SplitM()

End Sub

the line which is bugging is this one: 

Sheets("Reasons codes").Copy After:=Workbooks(ThisWorkbook.Path & "\Split Results\" & vFilter & ".xlsx").Sheets(Sheets.Count)

Without it everything works fine, and no bugging. With that line added, just one file created, and the sheet that I want to copy at the end of the loops doesn't get copied of course, and I get a bug at that line.

What I am actually trying to do before that line, is to get the main Workbook, activate it, and copy a sheet called "Reasons code" from it, to every split file I created, at the very end of the sheets (also splitted with a loop from the destination file) created in that destination file.

And by the way, the column "G" in the main (original) file, and original sheet I am splitting from, has some data validation, linked to the sheet "Reasons codes" I am trying to copy. So if you have a solution also, how to link that column in the new sheets of the new files, after of course copying the "Reasons codes" sheet to each destination file, that would be REALLY great! Actually every splitted sheet in the new (splitted / destination) files, has also at column "G" the same content as the main sheet in the main file I am copying from. It is just pasting now the text, but when I look at the data validation, I find that it is linked to the main file name, and to that "Reasons codes" of the main file, that's why the validation is not working. Every row should be rather linked to a new created "Reasons codes" sheet in the same file.

ZelelB
  • 1,836
  • 7
  • 45
  • 71
  • I have a vba script that takes sheets from 6 master files and outputs them - 40 sets in all... so have a look and see if you can adapt it to your needs - if it helps an up vote would be appreciated : https://stackoverflow.com/a/30605765/4961700 – Solar Mike Aug 01 '18 at 07:03
  • Target file must be open to copy to. So comment `ActiveWorkbook.Close` above and it should work. You can find a good example for programming validation here: https://stackoverflow.com/questions/22956604/how-to-add-data-validation-to-a-cell-using-vba – AcsErno Aug 01 '18 at 07:47
  • @AcsErno no, I tried that before, and now again, but that's not the problem. Still bugging at that line. Maybe I cannot give a path to "Workbooks" as argument or something? PS: as you can see, "vFilter" is just the filename of the splitted file from original. So its a string – ZelelB Aug 01 '18 at 08:44

0 Answers0