Forum Discussion
Append data from same-named worksheets in multiple excel workbooks to new excel workbook
Aargh - you were right all the time. My code contains a stupid typo - my sincere apologies!
The line
r = wsT.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row = 1should be
r = wsT.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1with Row - 1 instead of Row = 1.
Hi Hans - Thank you so much for the code above. I'm getting the following error: Method 'Range' of object '_Worksheet' failed
I've tried launching the code from both a new Workbook in the same directory (and even tried naming all the Worksheets the same as they are named in the existing Workbooks), as well as launching from an existing Workbook, and I get the error either way. I do see that it opens the 1st and 2nd Workbooks in the folder (there are 4 total), once I pick the folder from the Dialogue Box that your code launches, but then the above error appears. I'm a bit of a rookie with VBA, and I'm not sure how to troubleshoot this error - although I do see that both the 'sFile' and 'sFolder' variables that I put a Watch on are empty at the time the error message is launched. Please let me know what might be happening with this error message. Thanks again!
Do all workbooks in the folder that you select have exactly the same number worksheets, with the same names?
Hi Hans - yes, all workbooks in the directory have exactly the same number worksheets, with the same names and there are no hidden worksheets. For starters, should I be running this code from a separate Workbook, or should I be running the code from one of the 'source' Workbooks in the directory? Also, the code opens the first 2 of the 4 Workbooks in the directory before it errors out. I just recreated the 3rd and 4th Workbooks, just to see if there was any corruption, but this is not the case - still seeing the error. I'm including a screenshot of the Worksheet names so you can see they are the same. What else might be happening here? Thank you!
The code was intended to be run from a workbook outside the folder. It will create the destination workbook at the end of the macro, so there shouldn't be a destination workbook in the folder, only source workbooks.
I ran the code from a Workbook in a different directory from the source Workbooks and got the same error (at the same point after the first 2 Workbooks are opened by the script). I put a Watch on the variables you defined and none of them are populated with any values:
Also, I looked at the length of the folder/sub-folder hierarchy, and it is only 85 characters long, so that shouldn't be an issue. Where else can I look for issues that may be causing this error?
Thank you!