Hi Anand, First of all thanks for making this video it is very helpful. Can you help me to provide a code where i have 4 excel workbook and each workbook has 4 or some have 5 sheets i want to create a macro into which i want an only specific sheet data not complete data. Like from 1st workbook i need only 2 sheets and from second workbook i need 1 sheet and so no . How can i achieve this task ?
Hi Ajay, Thanks for the video its useful. Could you please tell - How we can change the name of all copied sheets as the name of file or workbook name i.e. "Sheet (1)" "Sheet(2)" etc. to Co. A, Co. B .....
Hi Anand sir, When the macro is completed the macro updating shee1 2, 3 and so on. How it can be made as file names as sheet name. The same question some one had asked long back, the reason is, in the video the file name has been updated in the sheet name. When we perform the same process the sheet name is not updating. Please help us to correct it.
Hi Ajay Anand. Thanks for this upload. I am just starting out with VBA and this macro is beyond anything I could have done myself. However, I noticed in the line "Sheet.Copy After:=ThisWorkbook.Sheets(1)", this adds each sheet after the first, and so the sheet order is reversed. To maintain the order of sheets, I have altered the above code to: "Sheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)".
Hi - What adjustment can be made if I want to transfer only specific sheets from various workbooks. Example I have 5 workbooks and I want to combine all sheets named "2" in those workbooks to one file. Thank you
Hi . I want all the data to copy in one sheet it self. For instance: i have 10 workbook and each workbook has data in two sheets. So macro should copy the sheet 1 and past in sheet 1 and the data which is in sheet 2 should copy past in sheet 2. This has to been done for all the 10 work books. Request you to respond. Thanks in advance
Somehow its not working for me here is code which I copied Error is - Can't Execute Code In Break Mode , What Mistake I made here Sub CombineWorkBooks2() Dim FName As String, FPath As String Dim Sheet As Worksheet
Dim FDialog As FileDialog
Set FDialog = Application.FileDialog(msoFileDialogFilePicker)
![TEEDEE TADA - DIAMOND NARAKORN X LEGO LYKN [Special Dance Performance]](http://i.ytimg.com/vi/QgtZedFeumA/mqdefault.jpg)
Thank you for wonderful video! But how we can process if we want collated file from specific sheet from all active workbook. Can you please assist?
Thank you for this! My code finally worked! 😭
Very informative and clearly explained.
Thank you very much..🙂
How can we consolidate that entire data into single sheet ?
Better to use Power Query for this task. I have done a video on this topic.
Hi Anand, First of all thanks for making this video it is very helpful. Can you help me to provide a code where i have 4 excel workbook and each workbook has 4 or some have 5 sheets i want to create a macro into which i want an only specific sheet data not complete data. Like from 1st workbook i need only 2 sheets and from second workbook i need 1 sheet and so no . How can i achieve this task ?
Hi Ajay, Thanks for the video its useful. Could you please tell - How we can change the name of all copied sheets as the name of file or workbook name i.e. "Sheet (1)" "Sheet(2)" etc. to Co. A, Co. B .....
I have done a few videos on Adding/Naming worksheets. Please go through the videos section of this channel to find them.
Thank you
You're welcome
Hi Anand sir,
When the macro is completed the macro updating shee1 2, 3 and so on. How it can be made as file names as sheet name.
The same question some one had asked long back, the reason is, in the video the file name has been updated in the sheet name. When we perform the same process the sheet name is not updating.
Please help us to correct it.
Thanks again
My pleasure and thank you for letting me know
Hi Ajay Anand. Thanks for this upload. I am just starting out with VBA and this macro is beyond anything I could have done myself.
However, I noticed in the line "Sheet.Copy After:=ThisWorkbook.Sheets(1)", this adds each sheet after the first, and so the sheet order is reversed.
To maintain the order of sheets, I have altered the above code to: "Sheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)".
Hi - What adjustment can be made if I want to transfer only specific sheets from various workbooks. Example I have 5 workbooks and I want to combine all sheets named "2" in those workbooks to one file. Thank you
Hello, hello ! Who is the CHAMP again !!😊😊😊😘😘😘
Like always, You ☺️
@@AjayAnandXLnCAD That is so transferable !
You transferred championsity:-) from you to me !!
Thank you, Ay !!💖💖💖
How to arrange the sheets in consolidated sheet the way we want?
Nice sir
Thanks and welcome
Hi .
I want all the data to copy in one sheet it self.
For instance: i have 10 workbook and each workbook has data in two sheets. So macro should copy the sheet 1 and past in sheet 1 and the data which is in sheet 2 should copy past in sheet 2. This has to been done for all the 10 work books.
Request you to respond.
Thanks in advance
I suggest Power Query for this task. Please refer to my articles on this topic.
If you give the code on description it will use to us
Please visit my website for the VBA code shown here.
What if I dont want it to appear in multiple sheets? I need a data in 1 sheet combined from all sheets, this isnt useful then.
Your video it is good, but very fast to watch! Please explain slowly! Thanks a lot for your ideas!
Thank you for the suggestion. Will make necessary improvements in the videos to come.
If you hit the 'gear' next to the closed caption symbol on the video you can slow the playback speed,
Nice
I have done everything and no error showing.. stil it's not working
Can you please share the code here?
Somehow its not working for me here is code which I copied
Error is - Can't Execute Code In Break Mode , What Mistake I made here
Sub CombineWorkBooks2()
Dim FName As String, FPath As String
Dim Sheet As Worksheet
Dim FDialog As FileDialog
Set FDialog = Application.FileDialog(msoFileDialogFilePicker)
If FDialog.Show = -1 Then
Worksheets("Combine Sheets").Cells(2, 2).Value = FDialog.SelectedItems(1)
End If
Application.ScreenUpdating = False
FPath = Worksheets("Combine Sheets").Cells(2, 2).Value
FName = Dir(FPath & "\*.xls*")
Do While Name ""
Workbooks.Open Filename:=FPath & "\" & Name, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(FName).Close
FName = Dir()
Loop
Application.ScreenUpdating = True
End Sub