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 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?
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 = 1
should be
r = wsT.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
with Row - 1 instead of Row = 1.
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!