Excel 2010 macro: number of sheets as a variable 1

[DJWheezyWeez]

Below is my code that doesn't work but this is the idea that I'm trying to work out. The way my whole macro works, it takes .xls files and if there is more than one sheet, I need it to save as a .xlsx, close, and reopen the new file. Based on the number of sheets, the macro will continue in different ways.

Is there a way to this? Or is there a way around this? Any help is greatly appreciated.

Dim sheetCount As Long
    sheetCount = Sheets(Sheets.Count)

 

[SkipVought]

hi,

Below is my code that doesn't work

This statement is totally useless, as it conveys no significant information that would be helpful to someone having no knowledge of 1) the code to which you are referring or 2) the contents of mind that perceived the problem.

Please explain in detail, exactly WHY that does not work, WHAT the results were and WHAT you expected the result to be.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DJWheezyWeez]

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
   macroA
ElseIf sheetCount = 2 Then
   macroB
Else 
   macroC
End If

macroA/B/C would be the code to copy/paste from other sheets to the first sheet.

Does this make more sense?

 

[SkipVought]

Well you must properly reference WHICH workbook the Sheets object refers to.

You have woefully withheld vital information, like the actual code that this small snippet resides in.

I can only venture an educated guess that you have multiple workbooks opened, so...

with workbooks.open(SomeOtherWorkbookThatYouWantToOpen)
   select case .Sheets.Count
      case 3
         macroA
      case 2
         macroB
      case else
         macroC
   end select
end with

BTW, Sheets(Sheets.Count) returns a WORKSHEET, the LAST worksheet in the workbook

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}