Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste Loop | Excel Programming | |||
copy paste loop---new to vba | Excel Programming | |||
Copy and Paste using a loop | Excel Programming | |||
Copy/Paste Loop | Excel Programming | |||
copy and paste loop | Excel Programming |