Please help a beginner with a macro

whale

New Member
Joined
Dec 8, 2005
Messages
4
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, whale
Welcome to the Board !!!!!

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

anyway try this
Code:
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
Code:
'defining the ranges
Set rng1 = Range("H4:I64")
Set rng2 = Range("F4").Resize(rng1.Rows.Count, rng1.Columns.Count)

kind regards,
Erik
 

whale

New Member
Joined
Dec 8, 2005
Messages
4
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

New Member
Joined
Dec 8, 2005
Messages
4
I'm sorry I meant reset the conditional formatting for these cell?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
whale said:
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"
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,025
Messages
5,569,727
Members
412,289
Latest member
Kingchaos64
Top