Macro hangs up often but sometimes works fine

[Jeff]

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

 

[Don Guillett]

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'finds 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)

 

[Jeff]

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]

For archival purposes, please post your final code here.