MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copy specific cells from a row in 1 sheet to different cells in 1 row in another sheet


Posted by Steve on November 04, 2000 2:04 AM

I am trying create a macro to copy cells b,e,(u-aa) from sheet 1 (all in the same row) to cells b,c,(d)in the same row of sheet 2. The range of u-aa in sheet one can have the values all combined in cell d of sheet2 but separated with commas. Or I could just change sheet2 to have the same sort of range of cells from d-j to accomodate cell for cell copy. The main concern here is I don't want any other cells in each row copied to the second sheet. the copy should produce the following:
SHEET1 Cell = SHEET2 Cell
b = b
e = c
u-aa = d-j

(this is all on a per row basis)


Posted by Loren on November 06, 2000 12:36 PM


use linking formulas & point to the cells you want

Posted by Tim Francis-Wright on November 06, 2000 1:22 PM


If you want the cells on Sheet2 to have the values
of the cells on Sheet1, try this:

Sub SteveCopy()
Dim i As Integer
For i = 1 To Sheets(1).UsedRange.Rows.Count
With Sheets(1)
Sheets(2).Cells(i, 2) = .Cells(i, 2)
Sheets(2).Cells(i, 3) = .Cells(i, 5)
Sheets(2).Cells(i, 4) = .Cells(i, 21) & ", " & .Cells(i, 22) & ", " _
& .Cells(i, 23) & ", " & .Cells(i, 24) & ", " & .Cells(i, 25) _
& ", " & .Cells(i, 26) & ", " & .Cells(i, 27)
End With
Next i
End Sub

HTH!