| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro hangs up often but sometimes works fine
Hi,
I have a macro that sometimes hangs up and sometimes not. This is what I'm using:- Application.ScreenUpdating = False Sheets("Analysis").Select Range("C2").Select Selection.Copy Sheets("Report").Select myUnprotect 'Disable Report worksheet protection Sheets("Report").Select Range("A2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'IT HANGS UP HERE Sheets("Analysis").Select Range("E2").Select Selection.Copy Sheets("Report").Select Range("B2").Select 'Strength Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Sheets("Report").Select Range("A2:M2").Select Selection.Copy If Range("A3") = 0 Then 'it is empty Range("A3:M3").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If If Range("A3") < 0 Then ' Application.Goto Sh.Range("A1"), True 'won't work if I include this line Selection.End(xlDown).Activate 'IT HANGS UP HERE TOO ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False End If Range("A2:M2").Select Selection.ClearContents 'and I lose the formats set earlier! Range("A2").Select ++++++ (repetitons of copy / paste) +++++ Assistance greatfully accepted. Jeff |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro hangs up often but sometimes works fine
first, let's try to clean it up
Application.ScreenUpdating = False with sheets("Report") .unprotect .range("a2").value=sheets("analysis").range("c2") .range("b2").value=sheets("analysis").range("e2") '------------------------ 'starts to get fuzzy here as to what you want. 'insert a row or copy values down a row???? nextrow=.cells(rows.count,"a").end(xlup).row+1'fin ds next row range(cells(nextrow,"a"),cells(nextrow,"m").value= _ range(cells(2,"a"),cells(2,"m").value 'what is the more repetitions? Could be a loop?? '------------------------- .protect end with Sheets("Report").Select Range("A2:M2").Select Selection.Copy If Range("A3") = 0 Then 'it is empty Range("A3:M3").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If If Range("A3") < 0 Then ' Application.Goto Sh.Range("A1"), True 'won't work if I include this line Selection.End(xlDown).Activate 'IT HANGS UP HERE TOO ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False End If Range("A2:M2").Select Selection.ClearContents 'and I lose the formats set earlier! Range("A2").Select ++++++ (repetitons of copy / paste) -- Don Guillett SalesAid Software "Jeff" wrote in message ... Hi, I have a macro that sometimes hangs up and sometimes not. This is what I'm using:- Application.ScreenUpdating = False Sheets("Analysis").Select Range("C2").Select Selection.Copy Sheets("Report").Select myUnprotect 'Disable Report worksheet protection Sheets("Report").Select Range("A2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'IT HANGS UP HERE Sheets("Analysis").Select Range("E2").Select Selection.Copy Sheets("Report").Select Range("B2").Select 'Strength Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Sheets("Report").Select Range("A2:M2").Select Selection.Copy If Range("A3") = 0 Then 'it is empty Range("A3:M3").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If If Range("A3") < 0 Then ' Application.Goto Sh.Range("A1"), True 'won't work if I include this line Selection.End(xlDown).Activate 'IT HANGS UP HERE TOO ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False End If Range("A2:M2").Select Selection.ClearContents 'and I lose the formats set earlier! Range("A2").Select ++++++ (repetitons of copy / paste) +++++ Assistance greatfully accepted. Jeff |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro hangs up often but sometimes works fine
Don,
I'd like to thank you for taking the time to fix my problem. Your solution works fine and with a lot less code. Many thanks. Jeff "Don Guillett" wrote in message ... first, let's try to clean it up Application.ScreenUpdating = False with sheets("Report") .unprotect .range("a2").value=sheets("analysis").range("c2") .range("b2").value=sheets("analysis").range("e2") '------------------------ 'starts to get fuzzy here as to what you want. 'insert a row or copy values down a row???? nextrow=.cells(rows.count,"a").end(xlup).row+1'fin ds next row range(cells(nextrow,"a"),cells(nextrow,"m").value= _ range(cells(2,"a"),cells(2,"m").value 'what is the more repetitions? Could be a loop?? '------------------------- .protect end with Sheets("Report").Select Range("A2:M2").Select Selection.Copy If Range("A3") = 0 Then 'it is empty Range("A3:M3").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If If Range("A3") < 0 Then ' Application.Goto Sh.Range("A1"), True 'won't work if I include this line Selection.End(xlDown).Activate 'IT HANGS UP HERE TOO ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False End If Range("A2:M2").Select Selection.ClearContents 'and I lose the formats set earlier! Range("A2").Select ++++++ (repetitons of copy / paste) -- Don Guillett SalesAid Software "Jeff" wrote in message ... Hi, I have a macro that sometimes hangs up and sometimes not. This is what I'm using:- Application.ScreenUpdating = False Sheets("Analysis").Select Range("C2").Select Selection.Copy Sheets("Report").Select myUnprotect 'Disable Report worksheet protection Sheets("Report").Select Range("A2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'IT HANGS UP HERE Sheets("Analysis").Select Range("E2").Select Selection.Copy Sheets("Report").Select Range("B2").Select 'Strength Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Sheets("Report").Select Range("A2:M2").Select Selection.Copy If Range("A3") = 0 Then 'it is empty Range("A3:M3").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If If Range("A3") < 0 Then ' Application.Goto Sh.Range("A1"), True 'won't work if I include this line Selection.End(xlDown).Activate 'IT HANGS UP HERE TOO ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False End If Range("A2:M2").Select Selection.ClearContents 'and I lose the formats set earlier! Range("A2").Select ++++++ (repetitons of copy / paste) +++++ Assistance greatfully accepted. Jeff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro works but buttons don't | Excel Discussion (Misc queries) | |||
| Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
| Tab-naming macro only works once :-( | Excel Worksheet Functions | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Highlight Range - wrong macro, please edit. | Excel Worksheet Functions | |||
Linear Mode
