That Old "Paste In Next Column" Problem - Again! 1

[MeGustaXL]

I run a macro that produces a column of data named "Cumulative_Reliability". I want to paste this column into a seperate sheet called "DataTable". Next time I run the macro, I want to paste the new "Cumulative_Reliability" dataset into "DataTable, in the next column to the right, thus producing a record of all the results from up to about 100 runs.

 Sheets("Simulation").Range("Cumulative_Reliability").Copy
    Sheets("DataTable").Range("A1").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

If column A is blank, this pastes the data in column B, then the next lot overwrites column B

If column A contains data, it pastes in B <Yaayyy!> then the next run pastes in....B

What's wrong with my code?

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[GlennUK]

Nothing wrong with your code ... it's your logic. Your code would work fine if you have entries in column A and column B ... it would paste into column C. BUT, you have a blank column A, so End(xlToRight) from A1 goes to the entry in column B ( End of Blank Area that A1 is in ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MeGustaXL]

Hi Glenn,

Yeah I thought it might be me - but if there's data in A, it puts the next lot in B (very Good), and the next set in B, overwriting what's there (Bad) then each successive run just pastes in B, then B, B, B...(very Bad indeed)

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[GlennUK]

Is there anything in cell A1? ( I know you said column A has data, but your code is specifically using cell A1 )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MeGustaXL]

Yes, there's either a column of data in A:A or I can type something eg. "TEST" in A1.

Now here's a funny thing:

If the code segment reads

Sheets("DataTable").Range("A1").End(xlTo[b]Left[/b]).Offset(0, 1)

it behaves as I describe above, ie A:A totally blank = always paste in B:B, but if there is anything at all in A:A always paste in B;B

But if the code goes:

Sheets("DataTable").Range("A1").End(xlTo[b][red]Right[/red][/b]).Offset(0, 1)

It don't paste nuffink, ever. Nowt. Nada. Nowhere.

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[MeGustaXL]

OK, back to basics. This works:

Public Sub Cop()
Sheets("Sheet1").Range("A1").Copy
    Sheets("Sheet2").Range("A1").Paste
    Application.CutCopyMode = False
End Sub

and leaves Sheet2 selected.

This works the first time:

Public Sub Cop()
Sheets("Sheet1").Range("A1").Copy
    Sheets("Sheet2").Range("A1").End(xlToRight).Offset(0, 1).Paste
    Application.CutCopyMode = False
End Sub

but the second and subsequent runs just paste into Sheet2 cell A1! Grrrr!

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[MeGustaXL]

Darn It!!

Even more basics - Why does this work:

Sheets("Sheet2").Select
    Range("A1:A10").Select

But this:

Sheets("Sheet2").Range("A1:A10").Select

gives a

Run-time error '1004':
Select method of Range class failed

and so does:

With Sheets("Sheet2")
.Range("A1:A10").Select
End With

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[xlbo]

a sheet has to be selected before you can select a range

In terms of your original error, it would be better to do it this way:

Range("IV1").end(xltoleft).Offset(0,1).pastespecial.....

ie start from the far right and go left to find the last column of data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MeGustaXL]

That's it Geoff! As always, your solutions are like Sammy Fox - simple and stunning!

It sticks the 1st iteration's results in column B, but Hey, who cares as long as it steps to the right, which it does - Result!

+ STAR for Geoff

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[xlbo]

cheers!!

simple test will solve the col B issue for you:

if Range("A1").value = "" then
Range("A1").pastespecial
else
Range("IV1").end(xltoleft).Offset(0,1).pastespecial
end if

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question