How to split MASTER DATA Into multiple sheets using VBA Macro

[@harshababu8321]

Excellent explanation. Made it vey simple for all leaners

[@satishkhubchandani520]

Thank you🙏 I was looking for this macro to save time and you have explained it very well..

[@kotakiran]

Hi Avit its not working at Auto Filter stage .ITs showing blank
Sheet1.Range("A1:P1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("V" & LoopCounter).Value

[@shona_joey.08]

This is brilliant.... Made my life very easy. Thanks a lot for the video.....

[@storekeeperTips]

Fantastic bhai

[@vandanabapunentertainment4798]

Very good. Easy Explain.👍👍👍

[@ravibhushan669]

Sir apki all video achhi hai, maine channel ko subscribe kar diya hai.
Main all video dekhunga.

[@meditatereflect3354]

I've used the macro but it seems it's counting all the cells in excel. It generated all sheets needed but no data

[@avitguru]

Can you share your code.

[@meditatereflect3354]

@@avitguru It's working already. In your code I changed the auto filter to 1 instead of 2. Thank you. Anyway.. do you have tutorial on how to split directly to multiple files instead of multiple worksheet?

[@avitguru]

@@meditatereflect3354 yes I have

[@avitguru]

th-cam.com/video/KMW45AwK44c/w-d-xo.html

[@meditatereflect3354]

@@avitguru Thank you!

[@riazkhan2623]

Sir agar hum mastersheet me city add kare to us city ke namke worksheet me data chal jayega kya

[@avitguru]

Yes

[@MIS_PRODUCTION_AAA]

Thanks

[@sheenagoyal280]

Sir why it is copying only 1st column in my code.

[@Omshanthiom9]

Hi Avit, the video is very much useful. Thanks for that.. i have a question, in case If there are some cells are blank ... In that case will macro work ...?

[@avitguru]

Yes, but we need to verify our output.

[@Omshanthiom9]

@@avitguru thanks for the prompt response... My doubt is, while macro creating unique value and splitting data to respective sheet ... In few of the lines are blank then will it skip those blank cells and take entire row data...?

[@avitguru]

@@Omshanthiom9 if entire row is blank then macro will skip those rows.

[@Omshanthiom9]

@@avitguru not entire row, few cells here and there are blank...?

[@avitguru]

@@Omshanthiom9 then it will not skip

[@umairsiddiqui3204]

Sir, why do I have this error. "Name argument not found" "CRITERIAL":=Sheet1.Range("L" & Loopcounter).Value. can any answer quickly please. I will appreciate it. thanks

[@avitguru]

Write Criteria1 instead of CRITERIAL

[@vishwa3448]

Sir review tab me share workbook karne ne bad ye Error aa jata hai- "run time error '1004' Advancedfilter method of Range class failed" , sir please help

[@avitguru]

Code share Kar sakte ho kya

[@letthemknowuncle5399]

Video is very useful.. thanks for this.

[@revanthr47]

Hi Sir, Video is great and easy to understand. Thanks!
I have serial number as column in my Master sheet. Is there any way to make the serial number start from 1 after splitting the Master data?

[@avitguru]

Yes, I will give you code tomorrow

[@besspallysaikiran7820]

Thanks bruh worked for me

[@vibhutikumar3]

very good

[@sanjanamhatre8926]

It's very helpful. Can you help me to send mail on outlook data which is split in different worksheets

[@nehasaini755]

I am getting runtime error 1004 in 11th line.

[@nehasaini755]

Please help to solve this

[@avitguru]

Please send code line where you get an error

[@nehasaini755]

Sheets(sheets.count).Name = Sheet1.Range("P" & LoopCounter).Value

[@nehasaini755]

I am getting error- you typed invalid name of sheet or chart.
1. The name you type doesn't exceed 31 characters
2. You did not leave the name blank.
Before this part my code is working fine. I used column P for storing unique values.

[@avitguru]

@@nehasaini755 we cannot rename sheet with more than 31 characters or variable may be blank.

[@Rohitburi]

Hello Sir,
Thank you for the Video
Can we talk about Some Other types of data kindly share your content Deatils please

[@manaswinisahoo4555]

Thanks for the great vdo❤️

[@pool54]

Can u paste coding here

[@avitguru]

you can give me your email id. i will send to you file.

[@pool54]

@@avitguru in this every time he add sheets i want if sheet there (check sheet name ) only paste data
mail me [e-mail]

[@a2zofloans521]

Thanks bro it helped me a lot

[@Bharat_mata_ki_jai_1]

nice

[@hemantsingh2825]

Can u please share the code

[@avitguru]

Give me your email I will share the fill

[@manaswinisahoo4555]

Mujhe ak error araha hai :named argument not found 🙄

[@avitguru]

plz send me syntax

[@manaswinisahoo4555]

@@avitguru thanks for your reply .
I fixed that issue and now its working fine ..😁

[@avitguru]

@@manaswinisahoo4555 👍

[@joaocruz2082]

Dear Sir , thank you, could you please give me a simple solution to split a file per number of lines, for example 200 ,300 500 lines.

[@avitguru]

Okay, I will make video for this

[@jayakrishnank4236]

@@avitguru sir im waiting for this video

[@bhimsharma3934]

Plz share code

[@trushikpanchal]

Wonderful video! Can you please share the code via email?

[@avitguru]

Sure.

[@jeromeimmanuvel3324]

If it is hindi video. Mention it is Hindi in the title. I have given dislike ...

[@avitguru]

Many youtuber mentioned english title of the Hindi video.

[@jayeshs6618]

Hello,
1. Ye line me Filed:=5 (Is it Row number or Column number)?
Sheets("Data").Range("A1:J1").AutoFilter Field:=5, Criteria1:=Sheets("Data").Range("R" & LoopCounter).Value
2. And Sheets create hua hai but data copy paste nahi ho raha hai except headers. Below is the coding. Please help.
Sub Split()
Workbooks("ZADJUST.xlsm").Sheets("Data").Activate
Dim iRow As Integer
Dim Rng As Range
Dim LoopCounter As Integer
iRow = Sheets("Data").Cells(Rows.Count, "I").End(xlUp).Row
Set Rng = Sheets("Data").Range("I1:I" & iRow)
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Data").Range("R1"), Unique:=True
For LoopCounter = 2 To Sheets("Data").Cells(Rows.Count, "R").End(xlUp).Row
Sheets("Data").Range("A1:J1").AutoFilter Field:=5, Criteria1:=Sheets("Data").Range("R" & LoopCounter).Value
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheets("Data").Range("R" & LoopCounter).Value
Sheets("Data").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Sheets(Sheets.Count).Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Next LoopCounter
Sheets("Data").AutoFilterMode = False
End Sub

[@avitguru]

Field=5 is a header position of data, means it is a column

[@jayeshs6618]

Thanks and mera data copy paste nahi ho raha hai except header.... please refer the coding

[@avitguru]

@@jayeshs6618 can you call me.
My mobile number is in my website.

[@rahulkamble4737]

Sub fileopen()
Dim My_file As String
Dim iRow As Integer
Dim Rng As Range
Dim LoopCounter As Integer
iRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
My_file = Application.GetOpenFilename()
Worksheets("Sheet1").Range("J3").Value = My_file
Workbooks.Open My_file
Set Rng = Sheet1.Range("B1:B" & iRow)
'here I am getting run time error from Rng.advanced.
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range("BA1"), Unique:=True
For LoopCounter = 2 To Sheet1.Cells(Rows.Count, "BA").End(xlUp).Row
Sheet1.Range("A1:AQ1").AutoFilter Field:=2, Criteria1:=Sheet1.Range("BA" & LoopCounter).Value
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet1.Range("BA" & LoopCounter).Value
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Sheets(Sheets.Count).Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Next LoopCounter
Sheet1.AutoFilterMode = False
'Select all sheets
Dim myArray() As Variant
Dim i As Integer
For i = 1 To Sheets.Count
ReDim Preserve myArray(i - 1)
myArray(i - 1) = i
Next i
Sheets(myArray).Select
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B").EntireColumn.Delete
Columns("AZ").EntireColumn.Delete
Sheet1.Delete
Application.DisplayAlerts = True
MsgBox ("All Sheets has been created")
End Sub

[@avitguru]

th-cam.com/video/NF3hW5M_0yo/w-d-xo.html
Isme use Kiya hai maine

[@rahulkamble4737]

@@avitguru Hello sir I don't want file path.. see I M getting error in below code..
Sub fileopen()
Dim My_file As String
Dim iRow As Integer
Dim Rng As Range
Dim LoopCounter As Integer
iRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
My_file = Application.GetOpenFilename()
Workbooks.Open My_file
Set Rng = Sheet1.Range("B1:B" & iRow)
*****'Geting error from below code.*******
Just want to open excel file and run your macro..
Actual code should be in different Excel workbook.
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range("BA1"), Unique:=True
For LoopCounter = 2 To Sheet1.Cells(Rows.Count, "BA").End(xlUp).Row
Sheet1.Range("A1:AQ1").AutoFilter Field:=2, Criteria1:=Sheet1.Range("BA" & LoopCounter).Value
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet1.Range("BA" & LoopCounter).Value
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Sheets(Sheets.Count).Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Next LoopCounter
Sheet1.AutoFilterMode = False
'Select all sheets
Dim myArray() As Variant
Dim i As Integer
For i = 1 To Sheets.Count
ReDim Preserve myArray(i - 1)
myArray(i - 1) = i
Next i
Sheets(myArray).Select
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B").EntireColumn.Delete
Columns("AZ").EntireColumn.Delete
Sheet1.Delete
Application.DisplayAlerts = True
MsgBox ("All Sheets has been created")
End Sub