Unwanted loop

**LukasK** · 07-21-2013, 06:33 PM

Hey!

Let's say you have two workbooks opened and you want to run a macro everytime the "workbook 2" is deactivated. However, the macro should make some changes to this deactivated "workbook 2" and then deactivate it again. But this scenario obviously creates an infinite loop.

My "workbook 2" code looks like this:

Private Sub Workbook_Deactivate()
    Windows("Workbook2.xlsm").Activate
    Call MyMacro
End Sub

My Macro:

Sub MyMacro()
    Range("G29:G120").Copy
    Range("AU29").PasteSpecial
    Application.CutCopyMode = False
    ActiveWindow.Visible = False
End Sub

Is it possible to make it work without this unwanted loop?

Thanks for any advice!

Lukas

**Richard Buttrey** · 07-21-2013, 06:54 PM

Hi,

If you want to make changes to workbook 2 why are you first wanting to deactivate it only to reactivate it? Is there not some other event that you could use to make the change?

But since you're not using Paste Special Values why not simply point AU29 to G29 with =G29, and similarly for AU30:AU120

You may find the Application.EnableEvents = False instruction of some use in the appropriate place, followed later by an =True instruction.

**LukasK** · 07-21-2013, 07:18 PM

Thanks for the reply!

To better understand what I'm trying to accomplish with this, please take a look at my older thread:
http://www.excelforum.com/excel-gene...33#post3328333

So, I have two workbooks opened. The first one is a database and the second one is a workbook, where I'm using some comboboxes to select the values from the database. However, everytime I sort some of the database columns, my comboboxes selections are messed up. Because of this, I would like to run this COPY/PASTE macro everytime I switch from the second workbook to this database workbook, so I have those comboboxes selections stored in other cells, in case I would like to sort anything in the database, and later I want to run another macro to paste them back after I sort something in the database workbook.

Does it make sense?

Thanks in advance!

**AlphaFrog** · 07-21-2013, 09:30 PM

Not sure I follow why you are doing what you are doing, but you don't have to Activate a workbook to do something with it. Use a full range reference (Workbook.Sheet.Range) and it doesn't matter if the workbook is active or not.

Maybe try something like this...

Private Sub Workbook_Deactivate()
    ' ThisWorkbook keyword refers to the workbook this code is located in.
    ' Change the "Sheet1" sheet name to suit
    With ThisWorkbook.Sheets("Sheet1")
        .Range("AU29:AU120").Value = .Range("G29:G120").Value
    End With
End Sub

Don't use the .PasteSpecial method as that will reactivate the workbook and create an endless loop.

Unwanted loop

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Unwanted loop

Re: Unwanted loop

Re: Unwanted loop

Re: Unwanted loop

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

Why did an inner loop variable start overwriting the outer loop range suddenly?

Macro Loop Broken. Detects Max but doesn't continue loop

Macro to loop through all sheets and delete unwanted columns

Loop a column on Sheet1 and loop a row on Sheet2 to find a match

Bookmarks

Bookmarks

Posting Permissions