flashing macro

[fastbuck]

Hi out there. I have this crazy macro that I use. There are ten of them that transfer info from one part of the sheet to another. When I active the macros there is a whole heap of flashing backwards and forwards as it does it’s transfer. Would there be a way of fixing it so it runs a bit smoother? Many thanks Narelle


Sub Result_R1()

ActiveSheet.Unprotect Password:=""


' Result_R1 Macro

Range("M6").Select

Selection.Copy

Range("AE2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("B28").Select

Application.CutCopyMode = False

Selection.Copy

Range("AF2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("A28").Select

Application.CutCopyMode = False

Selection.Copy

Range("AG2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("O1").Select

Application.CutCopyMode = False

Selection.Copy

Range("AH2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("E28").Select

Application.CutCopyMode = False

Selection.Copy

Range("AI2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("K3").Select

Application.CutCopyMode = False

Selection.Copy

Range("AJ2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("C28").Select

Application.CutCopyMode = False

Selection.Copy

Range("AK2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("F28").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ScrollColumn = 30

Range("AL2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("D28").Select

Application.CutCopyMode = False

Selection.Copy

Range("AM2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



Range("G28").Select

Application.CutCopyMode = False

Selection.Copy

Range("AN2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

' Result_clear Macro

Range("A28:G28").Select

Range("F28").Activate

Selection.ClearContents

Range("E28").Select

ActiveSheet.Protect Password:=""

End Sub

 

[fastbuck]

Thanks Howard. I'll take your advise. Before I do however, I think the problem is all related to my original query. I have just gone back to a saved copy before I change the codes as per michael's suggestion (refer start of thread). I also added the first and last lines of replacement code to my other codes
It makes the codes work really nice and smooth but I believe (only guessing) it may have caused the workbook to play up. Its not a problem as I can go back to the old workbook. Funny thing is when I open the old work book the one thats playing up opens up as well. Im a bit hesitant to delete the offending file however as Im not sure what is going on.
I have my husband sharpening that axe right now...lol.

 

[L. Howard]

If the workbook data is not of sensitive, can you post a lint to it.

I would take a look, for what ever that is worth.

The old opening the newer workbook is strange. Might be on to something there.

Howard

 

[fastbuck]

I wish i could send it but it does contain sensitive information. Another I've noticed is when I open the dodgy workbook the fan in the laptop fire up and works overtime.

 

[Michael M]

Did you you have any other codes in either workbook apart from the one originally posted ??
Also make sure the two copies are not "shared" workbooks.

Also, If you don't have too many sheets in the workbook, create a new workbook, with new named sheets.
Then go to each sheet and copy the cells ( not the sheets) across to the new sheets....to do this.
click on the Intersect of the row & column headers ( top left hand corner), that will highlight the entier sheet.
Click copy and then select A1 in the new sheet, and paste.

 

[fastbuck]

There's 20 or so different codes that run off buttons. Also formulas that transfer from another sheet, macro initiated downloaded data from a web site. Its all a bit complicated. Will all that copy over to a new workbook? How do i check if they are shared?

 

[Michael M]

Are any of the macros Worksheet_Events OR recalcualte events ?
Is the downloaded data done constantly ?

Have a look at Tools > Share Workbook
See if there are any names in the list apart from yours !!

 

[fastbuck]

Will have a look tonight when i get home from work anf get back to you

 

[fastbuck]

There's only my name in shared workbooks. But get this...now when I try to open any other spreadsheet or even a blank spreadsheet, the dodgy one opens. I'm now thinking exorcism!!! LOL
All other documents works fine including the other spreadsheets that open with the dodgy one.
I'm not sure what (Worksheet_Events OR recalcualte events) are.

 

[Michael M]

I'm not sure what (Worksheet_Events OR recalcualte events) are

These are macros that run when things change or are recalculated in worksheets. Press ALT +F8 and see if there is a list of macros in the dialog box

Sometimes a worksheet event will keep running because the sheet keeps changing....thus creating a loop.

If you close Excel and try to open a fresh worksheet, does it still play up ??

It's a real shame you can't share the workbook, because without it, I think we're at a bit of a crossroads.

 

[Michael M]

Narelle
Have you had a look at the support link at Microsoft.

There are a number of checks you can carry out, and then get back to us with results / feedback


Here:

Excel: How to Troubleshoot Crashing and Not Responding Issues with Excel

and here:

Excel not responding, hangs, freezes or stops working - Excel

The section about other programs running might be worth a read.