Loop copy/paste for x columns
 

Hi, I've got a macro that copies formulae in column W, pastes in column X,
then copies this and pastes values (it's probably very uncouth however).
What I'd like to to is tie this in to a loop so that it repeats along to
column CC, without having to copy in the below formulae 58 times. Any help
would be appreciated.

Columns("W:W").Select
Selection.Copy
Columns("X:X").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Loop copy/paste for x columns
 

Andrew, this will perform the same opeartaiuon as you subrountine
Sub TryMe()
Application.ScreenUpdating = False
mylast = Cells(Cells.Rows.Count, 23).End(xlUp).Row
For j = 1 To mylast
Cells(j, 24) = Cells(j, 23).Value
Next j
Application.ScreenUpdating = True
End Sub

We can expand on this by putting the main part within another loop
For K =23 to 60
mylast = Cells(Cells.Rows.Count, K).End(xlUp).Row
For j = 1 To mylast
Cells(j, K+1) = Cells(j, K).Value
Next j
next k

However, I am unclear what is needed. Surely you do not want to coly column
X to Y, etc
Please clarify
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Andrew" wrote in message
...
Hi, I've got a macro that copies formulae in column W, pastes in column X,
then copies this and pastes values (it's probably very uncouth however).
What I'd like to to is tie this in to a loop so that it repeats along to
column CC, without having to copy in the below formulae 58 times. Any
help
would be appreciated.

Columns("W:W").Select
Selection.Copy
Columns("X:X").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Loop copy/paste for x columns
 

Andrew

What happens after we paste the values from "W:W into X:X?

Is it Y:Y into Z:Z or something else?

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Andrew" wrote:

Hi, I've got a macro that copies formulae in column W, pastes in column X,
then copies this and pastes values (it's probably very uncouth however).
What I'd like to to is tie this in to a loop so that it repeats along to
column CC, without having to copy in the below formulae 58 times. Any help
would be appreciated.

Columns("W:W").Select
Selection.Copy
Columns("X:X").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Loop copy/paste for x columns
 

On Feb 23, 8:43*am, Andrew wrote:
Hi, I've got a macro that copies formulae in column W, pastes in column X,
then copies this and pastes values (it's probably very uncouth however). *
What I'd like to to is tie this in to a loop so that it repeats along to
column CC, without having to copy in the below formulae 58 times. *Any help
would be appreciated.

You'll find it easier to read (and faster to run) if you avoid
Selecting the source range before using the Copy action. Likewise you
can simply specify your destination range as part of the Paste action.
The following two lines copies the results of the formulas in column W
to every column from X to CC which sounds like what you want?

Sub test()
Columns("W:W").Copy
Columns("X:CC").PasteSpecial Paste:=xlPasteValues
End Sub