Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Can we just take particular data from each file? If yes, how we can do that? (For example in this example, we just need column A and column D, can we do that?
Hi, This is great to use but I need little help with the code as my data has few missing count of files names can I skip them in the loop. E.g.: The file name is "Search Query Week 6.xlsx" , then Week 7 and 8 is missing then "Search Query Week 9.xlsx" it's one thing i want to know how to jump if the file is not exist. Another thing is my file name ends with 6 to 35, hence should I use "i" as shown in the video or need to do some changes with the code. These 2 queries. I must say the video was very useful. Thanks a lot for this.
Hi, your video is so helpful to me. But I stuck at one point in raw files after the header I have one black row and after that blank row I have data.. will you please tell me how to edit the code in this condition.. please guide. That will solve my entire problem
I m dealing with network alarms on daily basis. Getting data in 23 files for different zone. Need to append the data in single sheet for doing further analysis.
Hello. I recreated your Excel VBA to Combine Multiple Workbooks example shown here and I am receiving a Run-time error '1004': It's look for a file entitled "Day0.xlsx" but that file does not exist. Do you have any suggestion to fix this? Thanks
Hi Sir, Could you please help me on VBA code for below scenario..I have a folder which is having multiple files, in that few files having 15 columns of headers and few of files having more than 32 columns of headers,, so if file has 15 columns of headers need to copy as it is and paste into consolidated file, if file has 32 columns of headers need select required columns (up to 15 columns which are having same in 15 columns file but header name will be slight different ..example: if header name in 15 columns of the file has "Subject" but in different file header will be "SubjectID")which was align in between the headers, and paste into same consolidated file next available empty rows,, Thank you so much in advance...
hello sir thank you for this valuable lesson , but how can we precise automatically the path of the files and how can we import wokbooks with multiple sheets
Thanks for such video tutorials. Can you please describe how to set file location if details are not be same eg . Dpr -1,2,3... And second thing how to use columnoffset if there is column are not fixed in order.
thank you for the video. I have a question for Printer object and Printers object. is any Reference library required to install in the Tool->Reference before we can use Pinter and Printers object? I run below code, and get error "object is required" for line "For Each prt In Printers". Sub ListPrinters() Dim prt As Printer For Each prt In Printers Debug.Print Prt.deviceName Next prt End Sub
This is very helpful, since I am very new to VBA, quite difficult to understand. would it be possible to explain how we have defined the each excel by step by step.
Thank you for this video and it was really helpful. I am trying to achieve the same thing but instead of pasting everything in first sheet of master file, I want separate sheets of each excel data in the master file. How can I do that? Any help will be appreciated.
Dear Sir, Good Job !! i have a similar file, some invoices (same format) sheets in one excel file and i want to add one more sheet like report, specific cells data should be transfer from all invoices to this report sheet. please refer to me any of your video to know about the vba or shall I write to your email. please advise
Hey there... These codes are very useful. However, is there a possibility that you can tell me how to set up the code or what changes to make in this code shown in the video, if the file names are completely different.
Tq sir, how to combine worksheets of same structure which are in different folder with unique folder name as 2546-repo, 6756-repo,.... and so on pl give VBA code
In the video you had showed the combining of 5 different files, but what if you had an unknown number of files to combine? e.g. one week is 4 files, the next week is 5 files, the following week is 6 files etc. How do you modify the code for the "For i = 1 To 5 Step 1" part?
This is really a very helpful video. Although I have a doubt, unlike your case(Day1,2,3), what if the different files is not sequential like(Day 2,29,40)
I need that my file is shared on a network. All can update but i don't want that. I made an excel file with dashboard which is updated by different persons on network and all have their different file. Now i want that when they generate their report some particular information from their excel file should be appended in my file on network.
Hi Sir i have followed the same coding as u did and made some of the changes like I have 15 file to merge and all those 15 files were in csv format so I have applied changes wherever required, but I am getting Run time error-9. Subscript out of range. When I have clicked on debug it highlighted the line - With wb.Sheets("Sheet"). Kindly help me in this scenario.
Error-9 occurs, when excel doesn't find the object. As you are saying that error line is "With wb.Sheets("Sheet") then you should check if sheet name is correct and wb object is declared. Without looking at complete code, I can not say much, but I would suggest to research on error 9.
Thanks. Very helpfull. But how to pull data if the 1 or 2 workbooks are open? Is it necessary to close all the workbooks before running this program. What is the solution if the file is open? I have a situation where I can't close all the files. Please help.
you can use workbooks("name of workbook").worksheets("name of sheet"). ........this is how, you can be specific to any workbook or any worksheets. Let me know if it helps.
what if I have multiple workbooks with different file names. Each workbook has a sheet with different sheet names as well. My goal is to extract results from these sheets. The range also varies and I wanted to copy and paste them on the target sheet as values.
Sir its really help in consolidating data to master sheet. however it will be great if you can explain code in with some description to understand for e.g. me.range couldn't figure out what does it mean.
Good Morning Sir, please help, on how to transfer Data from multiple excel workbooks if all the workbooks are having different name for example instead of Day1 & Day2 it may be Prashant & Vijay than how to consolidate that all files into one Master File with easy VBA Code
Hi sir and thank you for the video explanation. On my case I want to be able to select the file to be copied. How can I adapt your code so a Dialogbox will open and I can select the file I need. The files to be copied are in “.csv” format. Thanks a lot.
You can use getopenfilename function........and store file name in a string and use it as per requirement........code is Dim filename As String filename = Application.GetOpenFilename()
I am not that expert in vba hence didn't understood the full code, will encourage you to take time and explain the code (make the audience understand about the code) instead of just reading what you about to type
I can not say anything without looking at your code. You can check your code carefully and compare it with my code..........or send me your file. I can look at your code. but only, when I will get time.
Hello sir how use if function in vba code Qty weight. Rate. Unit Amount 0. 250. 100. 10kg 25000 10. 0. 100. Nos. 1000 Will you show me how to write if condition in userform
We Need help for VBA Query : Query: I will make invoice every day, understand my invoice system “ i was make first invoice, next time make need next invoice this time i was move as copy sheet & rename sheet manually………. I will try to automatically get Name from active invoice sheet … Example : A11 = invoice Number A16 = Party Name We Need automatically rename sheet according invoice no & party name Ex: EXP-001_Swara Foods ( i will move as copy sheet & assign Invoice number and party name ) After change Automatically renamed sheet How it possible ?????? Please help sir !!!
This was excellent, thank you for sharing! It worked wonderfully and was easy to understand. I have a question: How do I merge into the master spreadsheet ONLY the rows of data where columns 23 or 24 are not null. I don't know how to make this modification to your vba script, please advise and thanks again!
Hi sir, I want to consolidate file if sheet name is different in each file, like file one has sheet name as India_Country, 2nd file has sheet name as China_Country, country is there in all file sheet name, then how to consolidate? Please help!!
Hello. Thanks for watching...in that case you can use sheet number instead of sheet names...and run loop for total number of sheets with code like worksheets.count...try it..if you face challenge, send me your file with detail at exceldestination@gmail.com, I will try to respond.
@@ExcelDestination hello sir, i want to make a dependent drop-down list in which if I select something in Column A then drop down will activate in column B and it should be non editable, but if Column A is blank then drop down shouldn't activate in Column B and the cell should be editable. Is it possible? Thank you, i am trying this since so long buy unable to do, please help. It's really very urgent for me.
Hi sir, I have consolidate sheets in different folders with different names ,how can I take all sheets data ..that script should work for all projects in every project sheet name is different
@@ExcelDestination hi boss, thank you for your reply. I have checked code again and it's exactly same as showing in your video. I sharing the code. Kindly check and Let me know what mistake i am doing here. Code is - Private Sub CommandButton1_Click() Dim i As Long Dim lcurrow As Long Dim lrow As Long Dim wb As Workbook For i = 1 To 5 Step 1 Set wb = Workbooks.Open("C:\Users\kumar.dhruv\Desktop\Data" & "\Day" & i & ".xlsx") With wb.Sheets("Sheet 1") If i = 1 Then lrow = 1 Else lrow = 2 End If Do Until .Range("A" & lrow).Value = vbNullString lcurrow = lcurrow + 1 For n = 0 To 3 Step 1 Me.Range("A" & lcurrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(columnoffset:=n).Value Next n lrow = lrow + 1 Loop End With wb.Close True Next i Set wb = Nothing End Sub
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would
like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
Ok if I will go with macro and doing the same things let's replaced the vba codes then what is the results? That will work?
Can we just take particular data from each file? If yes, how we can do that? (For example in this example, we just need column A and column D, can we do that?
Hi,
This is great to use but I need little help with the code as my data has few missing count of files names can I skip them in the loop.
E.g.: The file name is "Search Query Week 6.xlsx" , then Week 7 and 8 is missing then "Search Query Week 9.xlsx" it's one thing i want to know how to jump if the file is not exist.
Another thing is my file name ends with 6 to 35, hence should I use "i" as shown in the video or need to do some changes with the code.
These 2 queries.
I must say the video was very useful.
Thanks a lot for this.
Hi, your video is so helpful to me. But I stuck at one point in raw files after the header I have one black row and after that blank row I have data.. will you please tell me how to edit the code in this condition.. please guide. That will solve my entire problem
I m dealing with network alarms on daily basis.
Getting data in 23 files for different zone.
Need to append the data in single sheet for doing further analysis.
Hello. I recreated your Excel VBA to Combine Multiple Workbooks example shown here and I am receiving a Run-time error '1004': It's look for a file entitled "Day0.xlsx" but that file does not exist. Do you have any suggestion to fix this? Thanks
my file it is totally diff, mine is staffs salaries / taxes i need to run for the whole years to file govt tax how to do the run.
Can i fill the details in template through vba?
Hi sir, I wanted some information.. Like am building one macro.. Please help me for this..
What if file has xlxs and xlsm format and i need to open in whatever format file is to consolidate data
Hi Sir, Could you please help me on VBA code for below scenario..I have a folder which is having multiple files, in that few files having 15 columns of headers and few of files having more than 32 columns of headers,, so if file has 15 columns of headers need to copy as it is and paste into consolidated file, if file has 32 columns of headers need select required columns (up to 15 columns which are having same in 15 columns file but header name will be slight different ..example: if header name in 15 columns of the file has "Subject" but in different file header will be "SubjectID")which was align in between the headers, and paste into same consolidated file next available empty rows,, Thank you so much in advance...
hello sir thank you for this valuable lesson , but how can we precise automatically the path of the files and how can we import wokbooks with multiple sheets
Thanks for such video tutorials.
Can you please describe how to set file location if details are not be same eg . Dpr -1,2,3...
And second thing how to use columnoffset if there is column are not fixed in order.
I was going through comments the main problem is Me Keyword because of which the code is not working have you any solution for that or no.
What is the logic for files with inconsistent names?
How can I consolidate specific cells of multiple workbooks into one table in Excel using VBA?
Superb just what I need! Many thanks man!
welcome
thank you for the video. I have a question for Printer object and Printers object. is any Reference library required to install in the Tool->Reference before we can use Pinter and Printers object? I run below code, and get error "object is required" for line "For Each prt In Printers".
Sub ListPrinters()
Dim prt As Printer
For Each prt In Printers
Debug.Print Prt.deviceName
Next prt
End Sub
Sir can u help me with this same code just to add. How to work with password protected files
I use this code but very take time console. Please suggest any extra code because my time save & .XLS file not conso any idea.
Thanks for the video. It worked beautifully
You're welcome!
do you have the short way?
Worked like a beauty sir. ❤
Thank you very much for this great video.
Me.range getting error as invalid use of keywors..can u help to debug
Can you share VBA code in text file?
What to if I have to copy more sheet like sheet2 sheet3 and name of files in different name like date format
This is very helpful, since I am very new to VBA, quite difficult to understand. would it be possible to explain how we have defined the each excel by step by step.
How to copy full data from sheet 3 from file is different folder to master sheet.
Thank you for this video and it was really helpful.
I am trying to achieve the same thing but instead of pasting everything in first sheet of master file, I want separate sheets of each excel data in the master file.
How can I do that? Any help will be appreciated.
Glad it was helpful!
Can I get excel file along with vba code
Hey, the compiler is throwing an error for Me keyword stating Invalid use of me keyword. How can I fix this?
Dear Sir,
Good Job !!
i have a similar file, some invoices (same format) sheets in one excel file and i want to add one more sheet like report, specific cells data should be transfer from all invoices to this report sheet.
please refer to me any of your video to know about the vba or shall I write to your email.
please advise
Hey there... These codes are very useful. However, is there a possibility that you can tell me how to set up the code or what changes to make in this code shown in the video, if the file names are completely different.
you can give memory in "set" line accordingly. Try out at your end. If you need any help please send me email at EXCELDESTINATION@GMAIL.COM
Can you put it this vba code in the description link please
I'm very glad if you do this
Thanks..
What will be the code if the nunber of excel file is keep on adding in the folder, and it not fixed number of excel files. Please guide, thanks!
Hi ! Can you please provide the code for it
what is the initial value for lcurrrow?
Tq sir, how to combine worksheets of same structure which are in different folder with unique folder name as 2546-repo, 6756-repo,.... and so on pl give VBA code
In the video you had showed the combining of 5 different files, but what if you had an unknown number of files to combine? e.g. one week is 4 files, the next week is 5 files, the following week is 6 files etc. How do you modify the code for the "For i = 1 To 5 Step 1" part?
Use do while loop
@@hussainmeghani4809can u explain to me plzz
This is really a very helpful video. Although I have a doubt, unlike your case(Day1,2,3), what if the different files is not sequential like(Day 2,29,40)
There could be different logic based on data.
@@ExcelDestination Hi Sir, how about if the name of the file is month? example Jan, Feb, or March. or Location ex, Texas, California?
I have used same macros in my file. But whilw run is used it stope at Me,Range what is the problem
thanks for this video.. how to specify if i have more than 5 sheets in a folder. it would be great to know this for my work
Thanks for watching! you can take help from another tutorial at ruclips.net/video/aijlRxa35nw/видео.html
I need that my file is shared on a network. All can update but i don't want that. I made an excel file with dashboard which is updated by different persons on network and all have their different file. Now i want that when they generate their report some particular information from their excel file should be appended in my file on network.
My file name is according to date like "day - 05.01.2021". How to enter code to search file according to this video?
How to give path of files if names are different...?
Hi Sir i have followed the same coding as u did and made some of the changes like I have 15 file to merge and all those 15 files were in csv format so I have applied changes wherever required, but I am getting Run time error-9. Subscript out of range.
When I have clicked on debug it highlighted the line - With wb.Sheets("Sheet").
Kindly help me in this scenario.
Error-9 occurs, when excel doesn't find the object. As you are saying that error line is "With wb.Sheets("Sheet") then you should check if sheet name is correct and wb object is declared. Without looking at complete code, I can not say much, but I would suggest to research on error 9.
excellent. thank you so much for your valuable lesson. Subscribe and very much liked !
Thanks 😊
Thanks. Very helpfull. But how to pull data if the 1 or 2 workbooks are open? Is it necessary to close all the workbooks before running this program. What is the solution if the file is open? I have a situation where I can't close all the files. Please help.
you can use workbooks("name of workbook").worksheets("name of sheet"). ........this is how, you can be specific to any workbook or any worksheets. Let me know if it helps.
Hi , I used same macro, But i am getting error "vba compile error object required"
Sir i have one xl file in that different sheets
Sir please give VBA code in Description.............
Sir "ME.Range" isn't working
Hi! Many thanks, this video has been really useful! It would be helpful also to know how to keep table formatting?
Thanks :)
Thanks! not sure about keeping table formatting.......but you can write code for any formatting.
@@ExcelDestination Thanks. May I ask you how you define "me" ?
I feel, we don't need to define me.
If we add new file in a folder than what will be the code
Can you please provide excel files in description so that it is easy to practice us.
Why have you used L row and L current row.. why L any specific reason??
The code works, but it only pulls in the first column of data from each sheet. How do you pull in all columns from the sheets?
pls review your code again. I was able to consolidate all data from multiple workbooks
sir transfer to ho gaya lekin jis file se transfer karna hai uske shuru ke do row ki kuch column meger hai
may I have the code.
Hi,
Thanks for this tutorial........This helps in adding rows but what to do if we have to append coloumn wise......
some additional code
@@ExcelDestination and what would that look like
i got an error in this line (error 448)
Me.Range("A" & lcurrrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(culumoffset:=n).Value
Please check the variable name is correct
what if I have multiple workbooks with different file names. Each workbook has a sheet with different sheet names as well. My goal is to extract results from these sheets. The range also varies and I wanted to copy and paste them on the target sheet as values.
Also for each sheet I need to extract data from a defined cell ranges.
hi "invalid use of ME keyword"shows how to solve
Will it combine the excel cells with pi data link ?
I think so.
Thank you for this video. This is helpful.
Instead of VBA, is it possible to do this in Excel with Power Query?
Yes, definitely
sir please visit excelisfun channel for PowerQuary they have awesome stuff there !!!
Hi I deal with diff users data with no similar words... Please suggest me how I can simplify the code
Thanks for watching ! First step to consolidate data, we can check\ensure that data should be in same standard in all workbooks.
Sir its really help in consolidating data to master sheet. however it will be great if you can explain code in with some description to understand for e.g. me.range couldn't figure out what does it mean.
thanks for watching. let me try.
Can you please add this code in description.
but why keep asking save a copy. hmm which I feel very take time.
I am a biginer with VBA. Please suggest me some good book from where I can get step by step knowledge for writing VBA code.
Please use the following link. It has series of tutorials of excel vba programming from starting to advance.
ruclips.net/video/MwvQWpzuLLk/видео.html
Good Morning Sir,
please help, on how to transfer Data from multiple excel workbooks if all the workbooks are having different name for example instead of Day1 & Day2 it may be Prashant & Vijay than how to consolidate that all files into one Master File with easy VBA Code
for any real time project, please write to exceldestination@gmail.com. We are a freelancing team and provide you all vba support.
Why would mine open the first one and then give the error code "object does not support property or method"?
I would say check your code carefully again. You would definitely find the error.....
Hi sir and thank you for the video explanation.
On my case I want to be able to select the file to be copied. How can I adapt your code so a Dialogbox will open and I can select the file I need. The files to be copied are in “.csv” format.
Thanks a lot.
You can use getopenfilename function........and store file name in a string and use it as per requirement........code is Dim filename As String
filename = Application.GetOpenFilename()
I am not that expert in vba hence didn't understood the full code, will encourage you to take time and explain the code (make the audience understand about the code) instead of just reading what you about to type
lcurrrow is throwing error what to do
If file name is not defined like day1',day2,day3and day4 then wht should I do???
you can try to build logic, as explained in the example
sir in my case when i run your code it just opens all the files but do not copy its contents to the destined file. any help
I can not say anything without looking at your code. You can check your code carefully and compare it with my code..........or send me your file. I can look at your code. but only, when I will get time.
Your source data files need to be in Table formatting.
I have a file name with different dates on daily basis then how to get these run if don't want file to be save with name ending as 1 2 3
you need to use some logic for file naming
@@ExcelDestination what logic can you help me with it
What if the number of file is varying. You have 5 source files here, what if in daily basis we dont know how many it would be
so you can put validation in place
When executed, it says invalid use of Me keyword
Sir when i try this code error is coming at set wb = workbooks. Open
Pls check the name of workbook, you want to open
What is the command in the code that is actually copying and pasting the data?
the for loop section
Hello sir how use if function in vba code
Qty weight. Rate. Unit Amount
0. 250. 100. 10kg 25000
10. 0. 100. Nos. 1000
Will you show me how to write if condition in userform
after compile the data how will show a message box data is compiled
you can use msgbox("data is compiled")
what does this me stand for? I can't run the code
it means same file.
We Need help for VBA Query :
Query: I will make invoice every day, understand my invoice system
“ i was make first invoice, next time make need next invoice this time i was move as copy sheet & rename sheet manually……….
I will try to automatically get Name from active invoice sheet …
Example :
A11 = invoice Number
A16 = Party Name
We Need automatically rename sheet according invoice no & party name
Ex:
EXP-001_Swara Foods
( i will move as copy sheet & assign Invoice number and party name )
After change Automatically renamed sheet
How it possible ??????
Please help sir !!!
This was excellent, thank you for sharing! It worked wonderfully and was easy to understand. I have a question: How do I merge into the master spreadsheet ONLY the rows of data where columns 23 or 24 are not null. I don't know how to make this modification to your vba script, please advise and thanks again!
ض يص شض١١سس١
Hi sir, I want to consolidate file if sheet name is different in each file, like file one has sheet name as India_Country, 2nd file has sheet name as China_Country, country is there in all file sheet name, then how to consolidate? Please help!!
Hello. Thanks for watching...in that case you can use sheet number instead of sheet names...and run loop for total number of sheets with code like worksheets.count...try it..if you face challenge, send me your file with detail at exceldestination@gmail.com, I will try to respond.
@@ExcelDestination hello sir, i want to make a dependent drop-down list in which if I select something in Column A then drop down will activate in column B and it should be non editable, but if Column A is blank then drop down shouldn't activate in Column B and the cell should be editable. Is it possible? Thank you, i am trying this since so long buy unable to do, please help. It's really very urgent for me.
Hi sir, I have consolidate sheets in different folders with different names ,how can I take all sheets data ..that script should work for all projects in every project sheet name is different
sir if the workbook has a more then 2 sheets in that particular, how can I do this. sir, pls give me the solution
If it's different sheets..and if there are some unwanted file how can we pick that particular file
I go click get help , they give me this
Workbook.Add (X:=5, Y:=7) ' Function call without expression.
YourSub(5, 7) ' Sub invocation without Call.
PLEASE SHARE THE EXCEL FILES AND VBA CODE FOR PRACTICE.
It saves the time to do a practice.
let me check
How to combined data with selected header
im having problems with the "me.range" code line... nothingis happening.... huhuhuhu
I think he donsnt have any solution to that
In this..name of files are same ..Try to give code for files of different name and from different different folders we want to import
It was great vedio
I am new user for VBA would you please explain me lcurrrow an lrow
lrow means last row
Thank you
It's showing error that couldn't find the path however i am adding the correct path. What to do?
without looking at your code, can't say anything. try to review your code.
@@ExcelDestination hi boss, thank you for your reply. I have checked code again and it's exactly same as showing in your video. I sharing the code. Kindly check and Let me know what mistake i am doing here.
Code is -
Private Sub CommandButton1_Click()
Dim i As Long
Dim lcurrow As Long
Dim lrow As Long
Dim wb As Workbook
For i = 1 To 5 Step 1
Set wb = Workbooks.Open("C:\Users\kumar.dhruv\Desktop\Data" & "\Day" & i & ".xlsx")
With wb.Sheets("Sheet 1")
If i = 1 Then
lrow = 1
Else
lrow = 2
End If
Do Until .Range("A" & lrow).Value = vbNullString
lcurrow = lcurrow + 1
For n = 0 To 3 Step 1
Me.Range("A" & lcurrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(columnoffset:=n).Value
Next n
lrow = lrow + 1
Loop
End With
wb.Close True
Next i
Set wb = Nothing
End Sub
Hi Team,
Please share the update
getting an error on that "me"cmmnd hence unable to run it. please help
send me email at exceldestination@gmail.com, I will try to send you code.
I think he donsnt have any solution to that