excel - Paste Values From One Worksheet To Another If X = True -

excel - Paste Values From One Worksheet To Another If X = True -

i'm trying create macro that, if column f in 5th worksheet = 3, pastes values left of 3 (columns through e) sheet (sheet 1). can't seem started. when run macro nil happens. i'm sure i've made bunch of dumb mistakes.

thanks in advance help!

jack

sub movevalues() dim q integer, w integer w = 7 q = 1 1000 if activeworkbook.worksheets("taxable accounts import").cells(q, 6).value = 3 range(cells(q, 1), cells(q, 5)).select selection.copy worksheets(1).select range(22, w).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false worksheets(5).select w = w + 1 end if next q end sub

i think issue here you're copying 5 cells in row worksheets(5), incrementing w 1 on each loop. if goal add together same row on worksheets(1), need increment w 5 instead... nice, easy prepare haha:

w = w + 5

that beingness said, you're looping 1000 times, means there potentially 1000 matches, populate 1000 columns (or 5000 columns if by-5-correction accurate). that's lot! if intention instead start @ row 7, column 22, , increment rows there, might utilize next strategy. (heavily commented explain what's going on...)

option explicit sub movevaluesrev2() dim q long, w long dim tai worksheet, sheetone worksheet, _ sheetfive worksheet dim source range, target range 'set references up-front w = 7 set tai = thisworkbook.worksheets("taxable accounts import") set sheetone = thisworkbook.worksheets(1) set sheetfive = thisworkbook.worksheets(5) 'loop through cells in question q = 1 1000 if tai.cells(q, 6).value = 3 'store left-of-the-found-value-3 cells in range sheetfive set source = .range(.cells(q, 1), .cells(q, 5)) end 'set target range in row w, col 22 sheetone set target = .cells(w, 22) end 'the next 2 lines re-create , paste step source.copy target.pastespecial (xlpastevalues) 'increment w w = w + 1 end if next q end sub

excel vba excel-vba paste