Forum Discussion

PatDools's avatar
PatDools
Brass Contributor
Feb 20, 2023
Solved

Append data from same-named worksheets in multiple excel workbooks to new excel workbook

Hello - I have multiple worksheets of the same name in multiple workbooks.  For example, each of Workbooks #1, #2, and #3, I have Worksheets named 'Sheet1', 'Sheet2', 'Sheet3'.  Each Worksheet contai...
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Feb 21, 2023

    PatDools 

    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.

PatDools's avatar
PatDools
Brass Contributor
Feb 21, 2023

HansVogelaar 

 

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!

HansVogelaar's avatar
HansVogelaar
MVP
Feb 21, 2023

PatDools 

Do all workbooks in the folder that you select have exactly the same number worksheets, with the same names?

Resources