The error I get when I run my code is "Run-time error '438': Object doesn't support this property or method" on the "sheetCount = Sheets(Sheets.Count)" line. I thought posting a small portion of code would be easier than posting the entire macro, around one thousand lines of code.
I'm exporting data from Crystal Reports into Excel and it could be 4000 lines, could be 50000 lines, could be 300000 lines on multiple sheets. What I'd like my end result to be is have a macro that opens the .xls export from crystal, saves it as a .xlsx so it can handle more than 65536 lines, close the file, reopen the new .xlsx file, then IF there is a Sheet2, select Sheet2, copy it all, go back to Sheet1 and paste it at the bottom, then IF there is a Sheet3, do the same and continue in that way.
The current way I would do that is exactly how it sounds, select a sheet, copy/paste, but if I have code to select sheet2 but there is no sheet2, it will give me an error.
The idea with my posted code was to find the number of sheets and once I know the number, I can have code built around that value. So if the sheet count is 3, I can run code to select sheet2, copy/paste in sheet1, then select sheet3 and copy/paste in sheet1. This way I would not get any errors and wouldn't have any missed data since I know how many sheets there are.
Some example code I would use would be something like:
If sheetCount = 3 Then
ElseIf sheetCount = 2 Then
macroA/B/C would be the code to copy/paste from other sheets to the first sheet.
Does this make more sense?