Best Event for a situation. 3

[ItIsHardToProgram]

Hey every one,

I have a quick easy question, I want to place in a event the following code

Dim iWorksheetCount as integer

iWorksheetCount = Worksheets.Count

If iWorksheetCount > 15 Then
Event
End if

Whats is this for?

Prety darn simple, I have income statements that I copy in a excel file already containing the same income statement, I have a macro that replaces the value of the old income statements with the new ones freshly copied. After it deletes the new income statements.

The reason I proceed like this is that I don't want to lose the links that my cells have to the income statements (old).

What do I want to do?

As soon as I copy the income statements from one workbook to another I want the macro to test if the workbook has new income statements, and if so automaticaly paste/delete the new data.

This is a relativly easy concept and I only want to know what is the good event for what im doing, or if theres a better way of doing it?

Thank you very much,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[DaveInIowa]

DaFranker - I initally tried out the NewSheet event but it doesn't fire for me when pasting sheets from another workbook, only when I insert a new worksheet.

As to the endless loop, place a breakpoint on your While (Sheets(isheet).Name Like "* (#)") line. Also, you have a logic error by getting an initial sheet count outside your loop and modifying this count when you Delete sheet(s) inside the loop. Doesn't the general code example I posted above work?

 

[SkipVought]

So is it your intention that when ANY (*) sheet is activated, that ALL the sheets, as quallified above, go thru this assign & delete process. EVERY SWINGIN' SHEET?

Skip,

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

 

[ItIsHardToProgram]

Well at this point im passed the recursive problem, as you can see in the above code my problem is a end up in a never ending loop,

my for goes up to 27, then on 27 the sheet is copied so it enters the while.

It deletes the sheet that follows BUT sheet 27 is replaced with sheet 28, that is also a copy, and so on, so it stays in the while and never quits.... if u had any idea on how to fix that, it would help.

P.S. i tried adding an OR DoesWorkSheetExist
to the while


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

So is it your intention that when ANY (*) sheet is activated, that ALL the sheets, as quallified above, go thru this assign & delete process. EVERY SWINGIN' SHEET?

No skip, I would prefer to not do so, but I don't see how not besides placing a IF that tests if the selected sheet is a copy or not (wich I had remove for testing but that I will add in the end)

I still can't seem to fix that loop problem,


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim a, ws As Worksheet
    
    On Error Resume Next
    
    If (Not Sh Is Nothing) Then
        Application.DisplayAlerts = False
        
        For Each ws In Worksheets
            a = Split(Sh.Name, " ")
            If (ws.Name Like "* (#)") Then
                Sheets(a(0)).Range("B6:AF43").Value = ws.Range("B6:AF43").Value
                Sheets(a(0)).Range("B45:AF54").Value = ws.Range("B45:AF54").Value
                ws.Delete
            End If
        Next
        Application.DisplayAlerts = True
        
    End If
End Sub

Skip,

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

 

[ItIsHardToProgram]

For Each ws In Worksheets" Never fires... why would that be?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

NeverMind...



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

duh! Sorry!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim a, ws As Worksheet
    
    On Error Resume Next
    
    If (Not Sh Is Nothing) Then
        Application.DisplayAlerts = False
        
        For Each ws In Worksheets
            a = Split([b]ws[/b].Name, " ")
            If (ws.Name Like "* (#)") Then
                Sheets(a(0)).Range("B6:AF43").Value = ws.Range("B6:AF43").Value
                Sheets(a(0)).Range("B45:AF54").Value = ws.Range("B45:AF54").Value
                ws.Delete
            End If
        Next
        Application.DisplayAlerts = True
        
    End If
End Sub

Skip,

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

 

[ItIsHardToProgram]

Well thank you, I used part of your code and part of mine so that only the worksheets that I want be copied and delete, and any other worksheet wouldnt.

Thanks for your help

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim a, ws As Worksheet
    
    On Error Resume Next
    
    If (Not Sh Is Nothing) Then
        Application.DisplayAlerts = False
        
        For Each ws In Worksheets
            a = Split(ws.Name, " ")
            If (ws.Name Like "* (#)") Then
              select case a(0)
                case "Conso","Corpo","Atrium",'......etc
                Sheets(a(0)).Range("B6:AF43").Value = ws.Range("B6:AF43").Value
                Sheets(a(0)).Range("B45:AF54").Value = ws.Range("B45:AF54").Value
                ws.Delete
            End If
        Next
        Application.DisplayAlerts = True
        
    End If
End Sub

Skip,

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

 

[SkipVought]

Actually, I'd build a list of the Worksheet names I wanted to delete, rather than hard coding into the procedure.

And I STILL believe that the Workbook_SheetActivate is a bad idea. You're either going to select a sheet or push a button. What's the difference? The BUTTON PUSH is a much more explicit INTENT, than a SHEET ACTIVATE. At some point, the sheet activate, I am afraid, will yield some unintended, unforseen consequences. But it certainly is not the end of the world as we know it!


Skip,

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

 

[ItIsHardToProgram]

Im afraid that the Select case wouldnt let me fire the for each, for what ever reason that is....

I can't really explain it but my for wouldnt fire with a select case in it that was using ws.

Although I support everything you say because your talented and it is quite decent, I might not always be here and people should be able to understand what I did.

No one here can even do part of what im doing, wich is actualy just understanding stuff that I read or get thrown at, but still...

I understand that it is risky to use a Workbook imbeded event that fires up on each sheet selection since its something that people do alot in Excel.

But to the untrained eye, it looks better if it is all automatic.

Any way thank you very much, maybe you know why the select case wasnt working? either way you helped a bunch and everything is working as I intended now...

Strangely, when I tried using range("B#:AB#",B#:AB#") it messed up the data, maybe it is because of a filter in between the range of what im selecting (filter is the reason in teh first place i seperate my selection.) Its not masked from excel, but its masked from MSDE/SQL import from a SQL2005 server.

Either way you helped enough! thank you.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

BYW, I know that I know and I know that I don't know. I know that I am not ignorant of some things and I know that I am ignorant of other things.


Skip,

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

 

[ItIsHardToProgram]

And so you are partialy stupid and intelligent at some extent, but that is the best characteristic of a human being.



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.