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.
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!
PatDools You have to look at the Watch window while the code is paused - click Debug in the error message window.
Hi Hans - that was it! Thank you for reviewing the code and catching that - greatly appreciated! This code is doing exactly what I need it to do. Thanks again!
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 for the tip on pausing the Code. So I can run this script 100% successfully when there is only one file in the Source directory - it creates the new Workbook and writes each tab out perfectly. The Method 'range' of object '_Worksheet' failed error only occurs when there is more than one Workbook in the source directory. And it doesn't matter which source Workbook it is, as long as it is the only Workbook in the source directory, your code executes perfectly. Does that provide a better clue as to why I'm getting this error message? Also, how do I tell which Worksheet is erroring out in the Watch Window while debugging?