Excel not responding

[shahdelsol]

I created a workbook 3 years ago and since then each month I have been adding a new tab. I currently have about 36 tabs. All tabs are identical size which they are from A1 to AP69. Not every cell has data but most of cells has something in it so I don't think it is necessarily a big file. Every month I have been copying last tab or one of tabs to create a new tab and add new data. There hasn't been any problem until recently when I am trying to copy new tab and add to the end of tabs I get " Excel not responding" like there is lack of memory or something and goes for ever. One time I didn't cancel and let it go and finally after a few hours it created a new tab. My question is : Is this lack of memory? To me This is not a big file and I have only 36 tabs and tabs/sheets are not big file.

I have also hidden the rest of cells beyond A1 to AP69 and I don't know if this matters or not. My Excel is Home and business 2016 version. ​

Does anyone know why it takes this long?

 

[shahdelsol]

I just tried 3 of my sheets and yes I ended up on

XFD1048576 on all 3. I have 36 sheets, it will take time if unhide all of them but seems they are going to be the same. Also I just tried to copy the the sheet that I cleared contents and althought it took a few mintues at least it did it copied okay. So certianly for some reasons there are hidden data all over the sheets perphas it is becucuse of that macro that I shared above. Any suggestion? ​

If you unhide all the rows and do Ctrl End on every sheet, do you always end up on XFD1048576 or do you end up at the "correct" last cell on some of them?

 

[Fluff]

In the copied version of the sheet does Ctrl End still take you to XFX1048576?
If so can you delete the blank rows?

 

[shahdelsol]

Yes it does take me to the last cell of the book. When I tried to delete, I got the same error.

In the copied version of the sheet does Ctrl End still take you to XFX1048576?
If so can you delete the blank rows?

 

[Fluff]

In that case the youl'd probably be best of creating a blank workbook with the correct number of sheets & then copy paste just the cells you want sheet by sheet.
Once that's done create a backup copy & try all an any macros & check which (if any) is causing the problem.

 

[shahdelsol]

So I copied and pasted the sheet without the macro that I shared on previous thread. With ctrl end it takes me to the right cell and copied new sheet no problem. Now I know it is the macro that somehow adding hidden data to the entire workbook. If you look at my macro, it is only supposed to add comments to O2:AC40. What do you see problem with code? Do you see this code can be re-written correctly?

In that case the youl'd probably be best of creating a blank workbook with the correct number of sheets & then copy paste just the cells you want sheet by sheet.
Once that's done create a backup copy & try all an any macros & check which (if any) is causing the problem.

 

[Fluff]

I see nothing in the code you posted that would cause the problems you've had.

 

[shahdelsol]

Thanks for all help. I am not sure what the issue is then.

I see nothing in the code you posted that would cause the problems you've had.

 

[Fluff]

Do you have any other macros?

 

[shahdelsol]

Yes I do but it is simple code to copy and create a new tab and I don't think it would cause any problem but here it is:

[FONT=Verdana]Sub NewTab()
'
' NewTab Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim MonEntry As Integer
 Dim Msg As String
 Const MinMonth As Integer = 1
 Const MaxMonth As Integer = 12
 Msg = "What month are your creating a new tab for?"
 Msg = Msg & vbNewLine
 Msg = Msg & "Please enter number for the month between " & MinMonth & " and " & MaxMonth
 Do
    MonEntry = InputBox(Msg)
    If IsNumeric(MonEntry) Then
     If MonEntry >= MinMonth And MonEntry <= MaxMonth Then Exit Do
     End If
     Msg = "Come on genius!"
     Msg = Msg & vbNewLine
     Msg = Msg & "Please enter a valid number for the month!"
     Msg = Msg & vbNewLine
     Msg = Msg & "The number for the month must be between " & MinMonth & " and " & MaxMonth
     
     Loop
     
     Worksheets("TMPSEP2018").Copy after:=Sheets(Worksheets.Count)
     'ActiveSheet.Name = MonthName(MonEntry, True) & " " & Format(Date, "YYYY")
      ActiveSheet.Name = Format(28 * MonEntry, "mmm ") & Year(Now)
     'ActiveSheet.Name = UCase(MonthName(MonEntry, True) & " " & Format(Date, "YYYY"))[/FONT]
[FONT=Verdana]    
End Sub[/FONT]

Do you have any other macros?

 

[Fluff]

That looks ok as well. So not sure what happened.
Best bet is keep a backup copy of the file, and regularly monitor the new working file to see if it happens again.