Please help a beginner with a macro

[whale]

I am using excel 2000 I am trying to create a macro using the
macro recorder.

I want to swap the contents of the cells in range E63:BM64 with E61:BM62
(the contents of the cells in E63:BM64 are pasted into E61:BM62
and content of the cells in E61:BM62 are pasted into E63a:BM64)
and then I swap the contents of the cells in range F4:G64 with H4:I64

Here are my exact steps:
Before I start recording I put the clipboard in view so I can select
from it then I start recording.
I copied E63:BM64 then I copied E61:BM62
I then pasted E63:BM64 at cell E61
I then pasted E61:BM62 at cell E63
I cleared the clipboard
I copied F4:G64 then I copied H4:I64
I then pasted F4:G64 at cell H4
I then pasted H4:I64 at cell F4
I cleared the clipboard
I stopped the recorder

When I run the macro I get.. run-time error'1004':Paste method of worksheet class failed

I thought the problem might stem from the fact that I used the clipboard
viewer , so I tried this:

I copied E63:BM64
I pasted to an unused area of the wooksheet
I copied E61:BM62
I pasted to E63
I copied E63:BM64 from the unused area of the worksheet that i moved it to
I pasted to E61

I copied F4:G64
I pasted to an unused area of the wooksheet
I copied H4:I64
I pasted to F4
I copied F4:G64 from the unused area of the worksheet that i moved it to
I pasted to H4

I stopped the recorder and the ran the macro and got the same error '1004'

Please take an attempt at trying to make this work. I appreciate the help.

 

[erik.van.geit]

Hello, whale
Welcome to the Board !!!!!

it would be good if you posted your code
we could make edits

anyway try this

Sub swapit()
'see helpfiles for declaring variables
Dim rng1 As Range
Dim rng2 As Range
Dim arr As Variant

'defining the ranges: must be equal in size !!
Set rng1 = Range("H4:I64")
Set rng2 = Range("F4:G64")

'storing values in memory
arr = rng1
'action "SWAP"
rng1.Value = rng2.Value
rng2 = arr

End Sub

or define the range this way

'defining the ranges
Set rng1 = Range("H4:I64")
Set rng2 = Range("F4").Resize(rng1.Rows.Count, rng1.Columns.Count)

kind regards,
Erik

 

[whale]

That is excellent !!! You are a genuis!!
I have been trying to get help on variuos forums for 4 days.

Now I must try to understand the code to the point where I can include
the second swap.

Another problem I have it that after the swap is made I loose my
conditional formatting is there any easy way to reset that in the code
for the cells that are effected ?

Thank you very much.

 

[whale]

I'm sorry I meant reset the conditional formatting for these cell?

 

[erik.van.geit]

I'm sorry I meant reset the conditional formatting for these cell?

pasting values doesn't influence the conditional formatting
can you clarify what the problem would be ?

this is another approach
when you understand how the code works, you can take it one step further calling a sub with two ranges as arguments (hope this is exact terminology in english)
run the "test"

Sub swapit(rng1 As Range, rng2 As Range)
Dim arr As Variant

arr = rng1
rng1.Value = rng2.Value
rng2 = arr

End Sub

Sub test()
Call swapit(Range("E61:BM62"), Range("E63:BM64"))
Call swapit(Range("H4:I64"), Range("F4:G64"))
End Sub

 

[whale]

Eric

Thanks again it works perfect.