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

 

[Michael M]

@Paul.....note the 2nd last line in my code...

and while this is good practice, it isn't necessary, as it resets anyway !

@L.Howard
I note your code doesn't Unprotect the sheet at the beginning, that may be the issue !

 

[fastbuck]

Thanks for your reply L.Howard

It comes up with this line highlighted in yellow - Sheets("Sheet1").Range("AE2").Resize(columnsize:=myRng.Cells.Count) = myArr

But I'm having some other issues at present as well. The spread sheet is jumpy when I scroll and slow and keeps coming up with a NOT RESPONDING error.

Narelle

 

[L. Howard]

Hi fastbuck,

Per Michael's suggestion, try adding the password Unprotects lines And is the sheet name actually Sheet1? (In red text)

Sheets("Sheet1").Range("AE2").Resize(columnsize:=myRng.Cells.Count) = myArr

If not, change to your sheet name.

For the sluggishness, you might try selecting ALL the cells below your data and use Delete under the Home tab.

Then select ALL the cells to the right of your data and do the same. Save and close, then reopen your workbook.

May help.

Howard

 

[fastbuck]

Excellent, the macro is working now. Thanks for all your help.
However I'm still having problems with 2 of my spreadsheets going slow and jumpy when I scroll. The one with the macros on it and another one that I use to download data from a web sites and then transfer the data onto the macro one. The data I download is loaded with hyperlinks so I paste and then copy paste again and that does away with the hyperlinks. I tried pasting special (text) but will only paste into I column with isn't suitable. I'm just wondering if the hyperlinks have something to do with it, although it hasn't been a problem before.

 

[L. Howard]

On the sluggish sheets, hit ctrl + end. Is the cell it selects way, way past the range of you normal data? If so, select cells (below then right again to the right) > Home > Cells > Delete thing if you have not done so already, save, close, reopen.

Could be just a LARGE amount of data with links and formulas. If the links are not needed you can go to the Data tab > Edit links > break the links.

Howard

 

[fastbuck]

Tried all that and still no good. Other spreadsheets are working normal. I copies and pasted some data from the dodgy sheet onto another book that was working fine and now it is playing up!! All my other spreadsheets are fine and if I create a new one it works okay too.

 

[fastbuck]

Update on my frustrating problem When I scroll down with the mouse wheel it works ok. However if I try to move sideways with the bar at the bottom of the screen the page jumps sideways one column at a time. Is there a setting that I could have accidently altered? OMG it's driving me insane lol

 

[L. Howard]

A brief "may be the reason"...?

The file size of an Excel workbook may unexpectedly increase exponentially


Howard

 

[fastbuck]

Hi Howard, Just gave that a go and that's not it either. I'm the only one who uses the computer and the workbook. A couple of other things that happen are when I try to do the anything like type into a cell and hit enter, it does the (not responding) thingo for up to 30 seconds and then corrects itself. Also if it try to widen a column by dragging it does the same thing. Seems that any simple task sends it into a spack attack.
I've tried placing a large axe on the desk next to the laptop, but that didn't help either...LOL.

 

[L. Howard]

Since this thread started as a vba macro problem and now has evolved into a sluggish worksheet problem, you are not exposing the new problem to the vast Mr. Excel talent.

I would suggest you start a new thread named consistent with the new problem and list ALL the stuff you have tried. Include Excel version, your computer and capacity/memory and how many columns and how many rows of data, number of sheets and of course the links, and an example of the formulas and an estimate of the number of the formulas and links. Also you might include the vba codes you are running.

One last thing to try may be to set Calculation to MANUAL and do some of the things where the sluggishness has been shown and see if that makes any difference. If there is marked improvement then that should tell you something. If it does nothing, then include that as one of the things you tried in your new thread.

You should use Tags around the code, which makes it much easier to read. Use as below, but eliminate the spaces I included.

[ code ]

Your code here

[ /code ]


Howard