|
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 |
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 |
Macro hangs up often but sometimes works fine
For archival purposes, please post your final code here.
-- Don Guillett SalesAid Software "Jeff" wrote in message ... 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 |
| All times are GMT +1. The time now is 08:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com