Macro hangs up often but sometimes works fine

Jeff · 06-12-2006, 01:45 AM

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 · 06-12-2006, 09:20 AM

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)

--
Don Guillett
SalesAid Software
[email protected]
"Jeff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Jeff · 06-13-2006, 01:10 AM

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" <[email protected]> wrote in message
news:%[email protected]...
> 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)
>
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Jeff" <[email protected]> wrote in message
> news:[email protected]...
>> 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 · 06-13-2006, 08:10 AM

For archival purposes, please post your final code here.

--
Don Guillett
SalesAid Software
[email protected]
"Jeff" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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)
>>
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Jeff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>>
>>
>>
>
>