Object Required Error in VBA,

[Sharda]

Sub Split()


Dim wb As String
Dim ws As String


wb = ActiveWorkbook.Name
ws = ActiveSheet.Name


Vcolumn = InputBox("Please indicate which column (i.e. A,B,C,...), 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:=x1Yes


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


For i = 2 To vCounter
vfilter = Sheets("_Summary").Cells(i, 1)
Sheets(ws).Activate
ActiveSheet.Columns.AutoFilter field:=Columns(Vcolumn).Column, Criteria1:=vfilter
Cells.Copy
Workbooks.Add
Range("A1").PasteSpecial
If vfilter <> "" Then
ActiveWorkbook.SaveAs ThiWorkbook.Path & "\Split Result" & vfilter
Else
ActiveWorkbook.SaveAs ThisWorkbook.Path & "Split Result\_Empty"
End If
ActiveWorkbook.Close
Workbooks(wb).Activate
Next i


Sheets("_Summary").Delete


End Sub

 

[Sharda]

I'm applying above VBA on below Excel, where I want my first row to be constant. Many Thanks in Advance for help.

LEDES98BI V2[]
INVOICE_DATE	INVOICE_NUMBER	CLIENT_ID	LAW_FIRM_MATTER_ID	INVOICE_TOTAL	BILLING_START_DATE	BILLING_END_DATE	INVOICE_DESCRIPTION	LINE_ITEM_NUMBER
20171031	1040448	37160	43168082-2015-14	532	20171001	20171031	Processing	1
20171031	1040450	37160	43326013-2012-13	2270.5	20171001	20171031	Processing	1

<colgroup><col span="3"><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Fluff]

Hi & welcome to the board.
I suspect the line that is causing the problem is this

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

which should be

vCounter = Range("B" & [COLOR=#ff0000]Rows[/COLOR].Count).End(xlUp).Row

Even so that will not work, as you have copied 1 column into col A so col B is blank. Therefore vCounter will =1 & your loop will then be from 2 to 1.
Could you please explain what you are try to achieve.

 

[Sharda]

Thanks Mr. Fluff for immediate response.
I'm a beginner with VBA stuff.
And need to split huge data into multiple csv's and found this script online which help to split data into multiple excels.
I didn't change script to column B
Because as soon as we run script it asks for which column you want split
I could be wrong on that.
Please see if you assist me this will be grateful.

 

[Fluff]

Give this a go

Sub Split()

    Dim SrcSht As Worksheet
    Dim TmpSht As Worksheet
    Dim vCounter As Long
    Dim vColumn As String
    Dim vFilter As String
    Dim i As Long
    
    Set SrcSht = ActiveSheet
    vColumn = InputBox("Please indicate which column (i.e. A,B,C,...), you would like to split by", "Column selection")
    
    Sheets.Add.Name = "_Summary"
    Set TmpSht = ActiveSheet
    
    SrcSht.Columns(vColumn).copy TmpSht.Range("A1")
    TmpSht.Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes
    
    vCounter = SrcSht.Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 2 To vCounter
        vFilter = TmpSht.Range("A" & i)
        SrcSht.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter
        Workbooks.Add
        SrcSht.Range("A1").CurrentRegion.SpecialCells(xlVisible).copy ActiveWorkbook.Sheets(1).Range("A1")
        ActiveWorkbook.SaveAs fileName:=ThisWorkbook.path & "\Split Result" & vFilter, FileFormat:=6
        ActiveWorkbook.Close False
    Next i
    TmpSht.Delete

End Sub

 

[Sharda]

Thanks Fluff for all the efforts. However, I want first two row of my data to be constant. I tried with "If vCounter < 2 Then" but i'm getting error "Block IF without End If"
Could please advise a way out.

My Data (Blue Highlighted) are constant row (Headers):

LEDES98BI V2[]
INVOICE_DATE	INVOICE_NUMBER	CLIENT_ID	LAW_FIRM_MATTER_ID	INVOICE_TOTAL	BILLING_START_DATE	BILLING_END_DATE
20171031	1040448	37160	43168082-2015-14	532	20171001	20171031
20171031	1040450	37160	43326013-2012-13	2270.5	20171001	20171031

<colgroup><col span="3"><col><col><col span="2"></colgroup><tbody>
</tbody>

Sub Split()


Dim SrcSht As Worksheet
Dim TmpSht As Worksheet
Dim vCounter As Long
Dim vColumn As String
Dim vFilter As String
Dim i As Long

Set SrcSht = ActiveSheet
vColumn = InputBox("Please indicate which column (i.e. A,B,C,...), you would like to split by", "Column selection")

Sheets.Add.Name = "_Summary"
Set TmpSht = ActiveSheet

SrcSht.Columns(vColumn).Copy TmpSht.Range("A1")
TmpSht.Columns("B").RemoveDuplicates Columns:=1, Header:=xlYes


vCounter = SrcSht.Range("B" & Rows.Count).End(xlUp).Row
If vCounter < 2 Then

For i = 2 To vCounter
vFilter = TmpSht.Range("A" & i)
SrcSht.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter
Workbooks.Add
SrcSht.Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy ActiveWorkbook.Sheets(1).Range("A1")
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Split Result" & vFilter, FileFormat:=6
ActiveWorkbook.Close False
Next i
End Sub
TmpSht.Delete

 

[Stiuart_W]

Every If needs and End If

For future please enclose your code in Tags (Select it when entering and click the "#" button in the editor menu

Dim SrcSht As Worksheet
    Dim TmpSht As Worksheet
    Dim vCounter As Long
    Dim vColumn As String
    Dim vFilter As String
    Dim i As Long

    Set SrcSht = ActiveSheet
    vColumn = InputBox("Please indicate which column (i.e. A,B,C,...), you would like to split by", "Column selection")

    Sheets.Add.Name = "_Summary"
    Set TmpSht = ActiveSheet

    SrcSht.Columns(vColumn).Copy TmpSht.Range("A1")
    TmpSht.Columns("B").RemoveDuplicates Columns:=1, Header:=xlYes


    vCounter = SrcSht.Range("B" & Rows.Count).End(xlUp).Row
    If vCounter < 2 Then

[COLOR=#ff0000]    [/COLOR]For i = 2 To vCounter[COLOR=#ff0000][/COLOR]
        vFilter = TmpSht.Range("A" & i)
        SrcSht.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter
        Workbooks.Add
        SrcSht.Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy ActiveWorkbook.Sheets(1).Range("A1")
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Split Result" & vFilter, FileFormat:=6
        ActiveWorkbook.Close False
    Next i
 End Sub
[COLOR=#FF0000][B]End if[/B][/COLOR]
    TmpSht.Delete

 

[Sharda]

Sure Stiuart! going forward I'll enclose code as advised. However I'm still getting similar error.
Just to give you gist about what I'm looking for is I have bulk Data that I want to split in individual CSV file.
For which we got successful with script provided by Mr. Fluff, but Instead of one row, I want to keep two rows of my data constant.
Could you help by any chance..?
Many Thanks in advance!

 

[Stiuart_W]

Sorry. I put the End If statement outside the End Sub

Dim SrcSht As Worksheet
    Dim TmpSht As Worksheet
    Dim vCounter As Long
    Dim vColumn As String
    Dim vFilter As String
    Dim i As Long

    Set SrcSht = ActiveSheet
    vColumn = InputBox("Please indicate which column (i.e. A,B,C,...), you would like to split by", "Column selection")

    Sheets.Add.Name = "_Summary"
    Set TmpSht = ActiveSheet

    SrcSht.Columns(vColumn).Copy TmpSht.Range("A1")
    TmpSht.Columns("B").RemoveDuplicates Columns:=1, Header:=xlYes


    vCounter = SrcSht.Range("B" & Rows.Count).End(xlUp).Row
    If vCounter < 2 Then

For i = 2 To vCounter
        vFilter = TmpSht.Range("A" & i)
        SrcSht.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter
        Workbooks.Add
        SrcSht.Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy ActiveWorkbook.Sheets(1).Range("A1")
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Split Result" & vFilter, FileFormat:=6
        ActiveWorkbook.Close False
    Next i
 
[COLOR=#ff0000][B]End if[/B][/COLOR]
    TmpSht.Delete
End Sub

 

[Fluff]

re think