I didn't understand what you were saying, but this whole video was very understandable by the way you were moving your mouse around and showing the results side by side. Or maybe I spoke Hindi (language?) in a past life? Great job, I am checking out your other videos!
Very excellent vedio. But it woks only for Number of rows data less then 1000, whatif its more data 300000 lines . What changes need to make in macro code
Great! Thanks sharing such an excellent video. I have some predefined invoice templates in which each supplier is different. How do I copy the data and paste those different templates? I will remain grateful to you.
Thank you.u r explained step by step but I have spliced the data based on distrct code its splitting only column header for all districts data not coming
Hi sir humne data split kra according to city but jb city wise split krke new workbook city name h to city column kyu sana chahiye split hokr sirf 4 column hi aana chahiye
Hi Avit, I actuality dont know about excel but in my work excel is been used where data is pull from an application into excel report, where in that report there is huge data and data starts from 5 row, where in first four rows contains formulas and and some data about report which is of no use, so i just wanted to know that this coding will help when i select the data from 5 row and it gets worked. Thank you in advance!
Thanks for sharing such a great content. I am using two sheets, first sheet has some summary based on 2nd sheet. 2nd sheet has the main data. I am able to split the workbook but I don’t see the 1st sheet in the splitted workbook. Kindly advise !
Sirji specific location ka liye kya karna hoga, Like if i want to save file into specific location.which is ask to as when we download file from online. Can you please so tht
Hi Sir, how can I use different names of sheet1(Amit Kumar) which is in column (d), and save it as file name (Amit Kumar Aug'2022 Report.xlsx) in column(E). so where I can change in VBA code. pls help
Hello Sir, I am able run the code getting files with new name but under new excel file only headers are present, data is missing. Could you please help me
agar 2 ya usse jyada sheets hai states ke related data wale in 1 workbook and split karne ke baad resulting file mein bhi us state ke multiple sheets 1 file mein chahiye toh kaise possible hoga? For e.g yaha pe aapke 2 master sheet hote Master 1 & Master 2 aur aapko resulting file mein Mumbai wale file mein Mumbai related Master 1 & Master 2 chahiye.
****Working Code***** Sub Split_Master() Dim iRow As Integer Dim loopcounter As Integer Dim Rng As Range Dim wkb As Workbook iRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Sheet1.Range("A1:A" & iRow) 'Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range("M1"), Unique:=True Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("M1"), Unique:=True
I am getting run time error 1004...with the comment - This cant be applied to the selected range, select a single cell in a range and try again.. I copied the exact code but not working
Sir Naamaskar, Mujhe autofilter jo lagana h, usme, first row ko skip kar ke lagana h, send row se. Mere data me first row me kuch remarks dia hua h. aapne field autofilter field 2 direct select kar dia h, video me, please mujhe help kare sir, row no 2 se aur columns B se autofilter lagana h. Send mujhe ek summary bhi h usi file me dono sheet me ek sath loop laga kar seperate file banana h. Sir please help me.
@@avitguru sir, maine koshish kia h, ho gaya ye, thanks. But mujhe ek issue aa raha h, mere file me do sheet h, vo dono sheet ko kaise set kar VBA me. Mob 8802357077
EXCEL VBA MACRO FILE SAVE AS .XLSB format /Same sheet Name /Particular Folder Hi, Please help me to auto save an excel file with this below options by using VBA Macro. 1. The workbook should save as Excel Binary format (.xlsb) 2. The file name should be the same sheet name. 3. The file should save as in particular folder. Thanks!
Sir mai ney split wale per try kia tha pre mj sy nehn bana Hm jo b new entry kary wo auto master sheet sy dosary sheet relfect ho Please is ka vedio baney ka Mj sy nehn hva try kia th
Hi , It was wonderful vides with so simple step, i am trying VBA for the first time and Tried the code , however i am getting invalid or unqualified reference on Set.wkb.= Workbook. Below is the code, please help Sub Split_Master() Dim iRow As Integer Dim loopcount As Integer Dim Rng As Range Dim wkb As Workbook iRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Sheet1.Range("B1:B" & iRow) Rng.AdvancedFilter Action:=xlFilterCopy, CopytoRange:=Sheet1.Range("H1"), Unique:=True For loopcount = 2 To Sheet1.Cells(Rows.Count, "H").End(xlUp).Row Sheet1.Range("A1:F1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H" & loopcount).Value Set .wkb = Workbooks.Add Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wkb.Sheets(1).Range("A1").PasteSpecial xlPasteAll Application.CutCopyMode = False wkb.Sheets(1).Name = Sheet1.Range("H" & loopcount).Value wkb.SaveAs "\\File\dateosrt \" & Sheet1.Range("H" & loopcount).Value & ".Xlsx" wkb.Close
@@avitguru Thanks for your help :), i need one more help like i have total 700 worksheets and i want date wise like AU-AUS 19-04-2022, so here i need to update the date wkb.Sheets(1).Name = Sheet1.Range("I" & loopcounter).Value
Thanku so much Avit sir you helped me a lot and you are very nice person god bless you sir.
I didn't understand what you were saying, but this whole video was very understandable by the way you were moving your mouse around and showing the results side by side. Or maybe I spoke Hindi (language?) in a past life? Great job, I am checking out your other videos!
Excellent teaching, you saved my day...
Thanks
Great brother....you explained in too simple way
This can't be applied to the selected range error aa rha hai
Advancedfilter copy to range wale m
Please solve it.
Can you share your code?
@@avitguru this code not run.
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("M1"), Unique:= True
Very excellent vedio. But it woks only for Number of rows data less then 1000, whatif its more data 300000 lines . What changes need to make in macro code
Please reply
Hi , Thank you so much for providing complete step by step details...Can you please share the scripted code...
Sure
Great! Thanks sharing such an excellent video. I have some predefined invoice templates in which each supplier is different. How do I copy the data and paste those different templates? I will remain grateful to you.
It is doable, Can you call me, my number is my website.
Thank you so much
Thank you.u r explained step by step but I have spliced the data based on distrct code its splitting only column header for all districts data not coming
Hi sir humne data split kra according to city but jb city wise split krke new workbook city name h to city column kyu sana chahiye split hokr sirf 4 column hi aana chahiye
Hi Avit,
I actuality dont know about excel but in my work excel is been used where data is pull from an application into excel report, where in that report there is huge data and data starts from 5 row, where in first four rows contains formulas and and some data about report which is of no use, so i just wanted to know that this coding will help when i select the data from 5 row and it gets worked. Thank you in advance!
Thanks for sharing such a great content. I am using two sheets, first sheet has some summary based on 2nd sheet. 2nd sheet has the main data. I am able to split the workbook but I don’t see the 1st sheet in the splitted workbook. Kindly advise !
Is there solution to this
Thanks☺️ - good explained step by step
Kindly explain us more project in hindi language
Sure
Good 👍
Hi sir, I am getting an error while execution "Run time error '91' object variable or with block variable not set
How to remove this error
On which line?
Hi Avit, I have query. how can I connect with you?
Avitwebtraining@gmail.com
I have questions
Are you students of Future in hand Vaishali NCR
Sirji specific location ka liye kya karna hoga,
Like if i want to save file into specific location.which is ask to as when we download file from online.
Can you please so tht
Hello, i used the same code, but the sheets that get created have only the header. How do i solve that problem
May be data is not visible while filtering. Please check field position in the data.
Hi Sir, how can I use different names of sheet1(Amit Kumar) which is in column (d), and save it as file name (Amit Kumar Aug'2022 Report.xlsx) in column(E). so where I can change in VBA code. pls help
Hello Sir can you add pivot table also in multiple workbook using vb
Thanks for such a learning content. Can you share the code please
Can you contact me.
Hello Sir,
I am able run the code getting files with new name but under new excel file only headers are present, data is missing.
Could you please help me
Yes I can. Please call me tomorrow.
This macro coding will run for how many rows?
Macro can run for 1048576 rows. But in that case we need to change data type for numerical values
Again thanks for the video ❤️
Good video - Thanks👍
Pls share more coding video explain in hindi language
Sure
agar 2 ya usse jyada sheets hai states ke related data wale in 1 workbook and split karne ke baad resulting file mein bhi us state ke multiple sheets 1 file mein chahiye toh kaise possible hoga?
For e.g yaha pe aapke 2 master sheet hote Master 1 & Master 2 aur aapko resulting file mein Mumbai wale file mein Mumbai related Master 1 & Master 2 chahiye.
Ok
@@avitguru kaise possible hoga uska coding bata sakte hai kya aap please
@@nitesh.2019 can you contact me? Mobile numbers is on my website.
Hi sir, i want to split each and every row in excel as different workbooks can you pls help me?
Then you don't need to unique value from advanced filter.
Remove advanced filter from code
Perfectly Worked. But Dear it is opening all exported files. This should not happen.
You need to close workbook after exporting. I used in the code
****Working Code*****
Sub Split_Master()
Dim iRow As Integer
Dim loopcounter As Integer
Dim Rng As Range
Dim wkb As Workbook
iRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Sheet1.Range("A1:A" & iRow)
'Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range("M1"), Unique:=True
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("M1"), Unique:=True
For loopcounter = 2 To Sheet1.Cells(Rows.Count, "M").End(xlUp).Row
Sheet1.Range("A1:E1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("M" & loopcounter).Value
Set wkb = Workbooks.Add
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
wkb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
Selection.EntireColumn.AutoFit
wkb.Sheets(1).Name = Sheet1.Range("M" & loopcounter).Value
wkb.SaveAs "C:\Users\Ravindra\Downloads\Result\" & Sheet1.Range("M" & loopcounter).Value
wkb.Close
Application.CutCopyMode = False
Next loopcounter
Sheet1.AutoFilterMode = False
End Sub
But it will throw an error where data size is big. Error: 6
@@ravindrabisht7372 what is the data size
@@avitguru yes same I have done, it is fixed...shared in code as well
Please provide video for merging separate excel files to one file for vba
Already made this.
th-cam.com/video/9KYVNHrY0as/w-d-xo.html
Want total also in same
I am getting run time error 1004...with the comment - This cant be applied to the selected range, select a single cell in a range and try again..
I copied the exact code but not working
This will not work on selected range.
@@avitguru i have not selected any range..its the same code
@@anujafal1173 sorry for late reply, can you share me your code and error on my email
Thanks Bhai G,Lekin ek question hai ki jab hum sheet open karte hai folder ja ke waha data autofit nehi ata hai uskeliye kya karna hai please
Autofit karna padega
Codeing kya hoga bhai g
Kaha mention karna padega
@@jaimajhi7227 Sheet1.Range("A").CurrentRegion.Columns.Autofit
Wkb.Sheets(1).Range("A").CurrentRegion.Columns.Autofit
Use this code after pastespecial
Sir Naamaskar,
Mujhe autofilter jo lagana h, usme, first row ko skip kar ke lagana h, send row se. Mere data me first row me kuch remarks dia hua h. aapne field autofilter field 2 direct select kar dia h, video me, please mujhe help kare sir, row no 2 se aur columns B se autofilter lagana h. Send mujhe ek summary bhi h usi file me dono sheet me ek sath loop laga kar seperate file banana h. Sir please help me.
Filter to header pe lagana hai na
Header kis row me hai apka
@@avitguru sir, maine koshish kia h, ho gaya ye, thanks. But mujhe ek issue aa raha h, mere file me do sheet h, vo dono sheet ko kaise set kar VBA me. Mob 8802357077
@@rahulyoutube209 ok I call you tomorrow
@@avitguru thank you sir for your corporation.
SIR ji, NIce
please share us code !
Share your email ID
How to add Sub total after split Data .. please suggest us sir !
@@bskrmusic3932 can you call me.
I
here I want to save my file in binary format what we do. ?
Record a macro and save as binary, use same code
@@avitguru I will try. and update
@@avitguru not working sir.
@@k.k.sabariraj6484 I will share code today
Named argument not found ye error aa raha hai sir kya karu?
Hi sir 2 excel sheet spareat karna hai to kaise kare..
I have made video for also
Search in Playlist
Wkb.saveas is showing error 91
Please help
Can you send me image of code
My work book isn't splitting in different workbooks ... It is showing error... you can't make work book more than 31 characters
Then you need to set limit
@@avitguru bhai apna number do
@@artistdeepakmeena9591 7042009885
EXCEL VBA MACRO FILE SAVE AS .XLSB format /Same sheet Name /Particular Folder
Hi,
Please help me to auto save an excel file with this below options by using VBA Macro.
1. The workbook should save as Excel Binary format (.xlsb)
2. The file name should be the same sheet name.
3. The file should save as in particular folder.
Thanks!
I will share code today
@@avitguru thks sir
this viedo is not Available in youtube. here I m searching all channels.
Sub SaveAsBinary()
Dim FolderPath As String
Dim StrWs As String
Dim ws As Worksheet
'You can change sheet name here in bracket
Set ws = ThisWorkbook.Sheets("Macro")
'Change folder path ends with \
FolderPath = "C:\Users\akash\OneDrive\Documents\"
StrWs = ws.Name
ThisWorkbook.SaveAs Filename:=FolderPath & StrWs & ".xlsb", FileFormat:=xlExcel12, CreateBackup:=False
End Sub
@@avitguru 😍😍😍😍😍😍😍😍code working thks sir
@@avitguru I need your contact details
Not worked code.. Help me
What is the issue
WHY IS THE TITLE OF THIS VIDEO IN ENGLISH ????
Sir kya aap coding ka file share kar sakte ho ?
Yes. I can
You can copy the code from below url
www.avitguru.com/blog/how-to-split-spreadsheet-into-separate-workbooks-using-excel-vba/
@@avitguru thank you so much sir. I am trying if I will face any difficulty i will contact you sir. Very nice and gorgeous trick sir. Very useful
When I run this file I face error of dbug. How to solve it ? Pls help me
@@nittalpatel1206 can you share error with me in whatsapp
Sir aik request hai please attached practice file with vedios
Ok I will upload file in drive and share links
@@avitguru thanks sir jee
I am waiting
Sir mai ney split wale per try kia tha pre mj sy nehn bana
Hm jo b new entry kary wo auto master sheet sy dosary sheet relfect ho
Please is ka vedio baney ka
Mj sy nehn hva try kia th
@@mehboobali7543 ok, mai upload Kar dunga or file ka path description me paste Kar dunga
@@avitguru saada Khush haro sir
I am waiting
pls code source
Please send me mail I will send the code in your mail id
Hi
Hi
good morning sir, i work with L&T Construction ,i can't one spilt master data some error showing, can share data for you mail id plz send me sir
Avitwebtraining@gmail.com
Hi , It was wonderful vides with so simple step, i am trying VBA for the first time and Tried the code , however i am getting invalid or unqualified reference on Set.wkb.= Workbook. Below is the code, please help
Sub Split_Master()
Dim iRow As Integer
Dim loopcount As Integer
Dim Rng As Range
Dim wkb As Workbook
iRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Sheet1.Range("B1:B" & iRow)
Rng.AdvancedFilter Action:=xlFilterCopy, CopytoRange:=Sheet1.Range("H1"), Unique:=True
For loopcount = 2 To Sheet1.Cells(Rows.Count, "H").End(xlUp).Row
Sheet1.Range("A1:F1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H" & loopcount).Value
Set .wkb = Workbooks.Add
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
wkb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
wkb.Sheets(1).Name = Sheet1.Range("H" & loopcount).Value
wkb.SaveAs "\\File\dateosrt \" & Sheet1.Range("H" & loopcount).Value & ".Xlsx"
wkb.Close
You have written Set .wkb = workbooks.add this is wrong
Write
Set wkb = workbooks.add
Sub Split_Master()
Dim iRow As Integer
Dim loopcounter As Integer
Dim Rng As Range
Dim wkb As Workbook
iRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Sheet1.Range("B1:B" & iRow)
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range("M1"), Unique:=True
For loopcounter = 2 To Sheet1.Cells(Rows.Count, "M").End(xlUp).Row
Sheet1.Range("A1:E1").AutoFilter Field:=2, Criterial:=Sheet1.Range("M" & loopcounter).Value
Set wkb = Workbooks.Add
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
wkb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
wkb.Sheets(1).Name = Sheet1.Range("OK" & loopcounter).Value
wkb.SaveAs "B:\test\" & Sheet1.Range("OK" & loopcounter).Value & ".xlsx"
wkb.Close
Application.CutCopyMode = False
Next loopcounter
Sheet1.AutoFilterMode = False
End Sub
Named Argument not found error is coming on line
Sheet1.Range("A1:E1").AutoFilter Field:=2, Criterial:=Sheet1.Range("M" & loopcounter).Value
Write Criteria1 instead of Criterial
Sub Split_Master()
Dim iRow As Integer
Dim loopcounter As Integer
Dim Rng As Range
Dim wkb As Workbook
iRow = Sheet1.Cells(Rows.Count, "D").End(xlUp).Row
Set Rng = Sheet1.Range("D1:D" & iRow)
Rng.AdvancedFilter Action:=xlFilterCopy, copytoRange:=Sheet1.Range("I1"), Unique:=True
For loopcounter = 2 To Sheet1.Cells(Rows.Count, "I").End(xlUp).Row
Sheet1.Range("A1:G1").AutoFilter field:=4, criteria1:=Sheet1.Range("I" & loopcounter).Value
Set wkb = Workbooks.Add
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
wkb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = Flase
wkb.Sheets(1).Name = Sheet1.Range("I" & loopcounter).Value
wkb.SaveAs "C:\Users\Country\Country 2\" & Sheet1.Range("I" & loopcounter).Value & ".xlsx"
wkb.Close
Next loopcounter
Sheet1.AutoFilterMode = False
End Sub
Run time error '1004'
Method 'SaveAs' of Object '_Workbook' Failed,
please help on this, this is my first time in VBA
Please run this code Step by step and let me know on which line you are getting an error.
wkb.SaveAs "C:\Users\Country\Country 2\" & Sheet1.Range("I" & loopcounter).Value & ".xlsx"
this line im getting error
@@nadimkhan-lm3ki ok, check value of sheet name
@@avitguru Thanks for your help :), i need one more help like i have total 700 worksheets and i want date wise like AU-AUS 19-04-2022, so here i need to update the date
wkb.Sheets(1).Name = Sheet1.Range("I" & loopcounter).Value
@@nadimkhan-lm3ki It will work.