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!
@AshutoshKumar-kg9cp
2 жыл бұрын
Thanku so much Avit sir you helped me a lot and you are very nice person god bless you sir.
@SomnathLondhe
Жыл бұрын
Excellent teaching, you saved my day...
@avitguru
Жыл бұрын
Thanks
@abdullahobd2408
3 жыл бұрын
Great brother....you explained in too simple way
@MohAboAbdo
6 ай бұрын
Thank you so much
@neanozone6392
2 жыл бұрын
This can't be applied to the selected range error aa rha hai Advancedfilter copy to range wale m Please solve it.
@avitguru
2 жыл бұрын
Can you share your code?
@neanozone6392
2 жыл бұрын
@@avitguru this code not run. Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("M1"), Unique:= True
@ramakantamuduli4869
Жыл бұрын
Sir aap wo video link dijiye , jisme ek hi workbook ki master sheet me jitne bhi ID ho, sabhi IDs ki ek ek karke individual sheets ban jaye aur bad me master sheet me jo bhi update ho wo sab data uske individual sheet me automatic update ho jaye. Apka sab video jo computer ka C na janta ho usko master bana dega. Excellent videos apka sir
@avitguru
Жыл бұрын
kzitem.info/news/bejne/0JyZxaesrouGf5g
@ramakantamuduli4869
Жыл бұрын
@@avitguru sir kya naya ID entry hone par us workbook me alag sa ek sheet apne apne add ho jayega ? Agar Han to Mera problem solved.
@avitguru
Жыл бұрын
@@ramakantamuduli4869 yes. But need to verify.
@damodarshanbhag6081
Жыл бұрын
Hi , Thank you so much for providing complete step by step details...Can you please share the scripted code...
@avitguru
Жыл бұрын
Sure
@aartitripathi2544
Жыл бұрын
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
@aartitripathi2544
Жыл бұрын
Please reply
@arockiadass6114
Жыл бұрын
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
@kazisazzad9257
2 жыл бұрын
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.
@avitguru
2 жыл бұрын
It is doable, Can you call me, my number is my website.
@ashishmehrotra7308
3 жыл бұрын
Thanks☺️ - good explained step by step Kindly explain us more project in hindi language
@avitguru
3 жыл бұрын
Sure
@kundanpathak
Жыл бұрын
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 !
@anuragbaldawa7242
9 ай бұрын
Is there solution to this
@pranavjagtap8426
Жыл бұрын
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!
@pavankumar6096
2 жыл бұрын
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
@avitguru
2 жыл бұрын
On which line?
@gyanjaiswal5029
2 жыл бұрын
Good 👍
@gouravgoyal9595
Жыл бұрын
Thanks for such a learning content. Can you share the code please
@avitguru
Жыл бұрын
Can you contact me.
@pradnyakarambelkar407
2 жыл бұрын
Hello, i used the same code, but the sheets that get created have only the header. How do i solve that problem
@avitguru
2 жыл бұрын
May be data is not visible while filtering. Please check field position in the data.
@ashishmehrotra7308
2 жыл бұрын
Good video - Thanks👍 Pls share more coding video explain in hindi language
@avitguru
2 жыл бұрын
Sure
@manaswinisahoo4555
3 жыл бұрын
Again thanks for the video ❤️
@niksvn4323
Жыл бұрын
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
@surbirsinghnegi2450
2 жыл бұрын
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
@vijaysahal4556
3 жыл бұрын
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
@billa19888
Жыл бұрын
Hello Sir can you add pivot table also in multiple workbook using vb
@sameerbhagat598
2 жыл бұрын
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
@avitguru
2 жыл бұрын
Yes I can. Please call me tomorrow.
@Keepstudying99
Жыл бұрын
Hi Avit, I have query. how can I connect with you?
@avitguru
Жыл бұрын
Avitwebtraining@gmail.com
@aartitripathi2544
Жыл бұрын
This macro coding will run for how many rows?
@avitguru
Жыл бұрын
Macro can run for 1048576 rows. But in that case we need to change data type for numerical values
@user-ix9hb7je4lGaurav
4 ай бұрын
I have questions Are you students of Future in hand Vaishali NCR
@ravindrabisht7372
2 жыл бұрын
Perfectly Worked. But Dear it is opening all exported files. This should not happen.
@avitguru
2 жыл бұрын
You need to close workbook after exporting. I used in the code
@ravindrabisht7372
2 жыл бұрын
****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
@ravindrabisht7372
2 жыл бұрын
But it will throw an error where data size is big. Error: 6
@avitguru
2 жыл бұрын
@@ravindrabisht7372 what is the data size
@ravindrabisht7372
2 жыл бұрын
@@avitguru yes same I have done, it is fixed...shared in code as well
@sudheerkumarhegdea8151
Жыл бұрын
Please provide video for merging separate excel files to one file for vba
@avitguru
Жыл бұрын
Already made this.
@avitguru
Жыл бұрын
kzitem.info/news/bejne/moGPvIJ-q4thlKg
@prashanthreddy4196
3 жыл бұрын
Hi sir, i want to split each and every row in excel as different workbooks can you pls help me?
@avitguru
3 жыл бұрын
Then you don't need to unique value from advanced filter.
@avitguru
3 жыл бұрын
Remove advanced filter from code
@nitesh.2019
3 жыл бұрын
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.
@avitguru
3 жыл бұрын
Ok
@nitesh.2019
3 жыл бұрын
@@avitguru kaise possible hoga uska coding bata sakte hai kya aap please
@avitguru
3 жыл бұрын
@@nitesh.2019 can you contact me? Mobile numbers is on my website.
@bskrmusic3932
Жыл бұрын
SIR ji, NIce please share us code !
@avitguru
Жыл бұрын
Share your email ID
@bskrmusic3932
Жыл бұрын
How to add Sub total after split Data .. please suggest us sir !
@avitguru
Жыл бұрын
@@bskrmusic3932 can you call me.
@k.k.sabariraj6484
2 жыл бұрын
I here I want to save my file in binary format what we do. ?
@avitguru
2 жыл бұрын
Record a macro and save as binary, use same code
@k.k.sabariraj6484
2 жыл бұрын
@@avitguru I will try. and update
@k.k.sabariraj6484
2 жыл бұрын
@@avitguru not working sir.
@avitguru
2 жыл бұрын
@@k.k.sabariraj6484 I will share code today
@anujafal1173
2 жыл бұрын
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
@avitguru
2 жыл бұрын
This will not work on selected range.
@anujafal1173
2 жыл бұрын
@@avitguru i have not selected any range..its the same code
@avitguru
2 жыл бұрын
@@anujafal1173 sorry for late reply, can you share me your code and error on my email
@hemanshuuttambhaitailor7184
7 ай бұрын
Want total also in same
@jaimajhi7227
2 жыл бұрын
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
Wkb.Sheets(1).Range("A").CurrentRegion.Columns.Autofit Use this code after pastespecial
@k.k.sabariraj6484
2 жыл бұрын
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!
@avitguru
2 жыл бұрын
I will share code today
@k.k.sabariraj6484
2 жыл бұрын
@@avitguru thks sir this viedo is not Available in youtube. here I m searching all channels.
@avitguru
2 жыл бұрын
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
@k.k.sabariraj6484
2 жыл бұрын
@@avitguru 😍😍😍😍😍😍😍😍code working thks sir
@k.k.sabariraj6484
2 жыл бұрын
@@avitguru I need your contact details
@artistdeepakmeena9591
2 жыл бұрын
My work book isn't splitting in different workbooks ... It is showing error... you can't make work book more than 31 characters
@avitguru
2 жыл бұрын
Then you need to set limit
@artistdeepakmeena9591
2 жыл бұрын
@@avitguru bhai apna number do
@avitguru
2 жыл бұрын
@@artistdeepakmeena9591 7042009885
@sanjuchandka6159
3 жыл бұрын
Wkb.saveas is showing error 91 Please help
@avitguru
3 жыл бұрын
Can you send me image of code
@latikeshbane7758
2 жыл бұрын
Hi sir 2 excel sheet spareat karna hai to kaise kare..
@avitguru
2 жыл бұрын
I have made video for also
@avitguru
2 жыл бұрын
Search in Playlist
@rahulyoutube209
3 жыл бұрын
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
3 жыл бұрын
Filter to header pe lagana hai na Header kis row me hai apka
@rahulyoutube209
3 жыл бұрын
@@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
@avitguru
3 жыл бұрын
@@rahulyoutube209 ok I call you tomorrow
@rahulyoutube209
3 жыл бұрын
@@avitguru thank you sir for your corporation.
@AshutoshKumar-kg9cp
2 жыл бұрын
Named argument not found ye error aa raha hai sir kya karu?
@satishmain3161
3 жыл бұрын
Not worked code.. Help me
@avitguru
3 жыл бұрын
What is the issue
@nittalpatel1206
3 жыл бұрын
Sir kya aap coding ka file share kar sakte ho ?
@avitguru
3 жыл бұрын
Yes. I can
@avitguru
3 жыл бұрын
You can copy the code from below url www.avitguru.com/blog/how-to-split-spreadsheet-into-separate-workbooks-using-excel-vba/
@nittalpatel1206
3 жыл бұрын
@@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
@nittalpatel1206
3 жыл бұрын
When I run this file I face error of dbug. How to solve it ? Pls help me
@avitguru
3 жыл бұрын
@@nittalpatel1206 can you share error with me in whatsapp
@ItsMeKhalid
Жыл бұрын
WHY IS THE TITLE OF THIS VIDEO IN ENGLISH ????
@mehboobali7543
3 жыл бұрын
Sir aik request hai please attached practice file with vedios
@avitguru
3 жыл бұрын
Ok I will upload file in drive and share links
@mehboobali7543
3 жыл бұрын
@@avitguru thanks sir jee I am waiting
@mehboobali7543
3 жыл бұрын
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
@avitguru
3 жыл бұрын
@@mehboobali7543 ok, mai upload Kar dunga or file ka path description me paste Kar dunga
@mehboobali7543
3 жыл бұрын
@@avitguru saada Khush haro sir I am waiting
@rupeshdubareya4771
Жыл бұрын
Hi
@avitguru
Жыл бұрын
Hi
@Benzo_Gaming0
2 жыл бұрын
pls code source
@avitguru
2 жыл бұрын
Please send me mail I will send the code in your mail id
@shafiqarutube
2 жыл бұрын
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
2 жыл бұрын
You have written Set .wkb = workbooks.add this is wrong Write Set wkb = workbooks.add
@r.s.krishna6187
9 ай бұрын
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
@avitguru
9 ай бұрын
Avitwebtraining@gmail.com
@princebhalodiya
2 жыл бұрын
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
@avitguru
2 жыл бұрын
Write Criteria1 instead of Criterial
@nadimkhan-lm3ki
2 жыл бұрын
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
@avitguru
2 жыл бұрын
Please run this code Step by step and let me know on which line you are getting an error.
@nadimkhan-lm3ki
2 жыл бұрын
wkb.SaveAs "C:\Users\Country\Country 2\" & Sheet1.Range("I" & loopcounter).Value & ".xlsx" this line im getting error
@avitguru
2 жыл бұрын
@@nadimkhan-lm3ki ok, check value of sheet name
@nadimkhan-lm3ki
2 жыл бұрын
@@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
Пікірлер: 150