Copy cells to a row specified in another cell

  • Thread starter Thread starter paul
  • Start date Start date
P

paul

I have a sheet (sheet 1) which has data like this:-

cell-1 cell-2 cell-3 cell-4
365 Brian 45 car

and I want to run a macro etc that copies the data in
cells 2,3 & 4 to a row in sheet 2 specified by cell1 (i.e.
to row 365 of sheet 2).

How do I do this? Can someone send me the script?

Paul
 
Perhaps something like this:

Option Explicit
Dim CellRef As Double
Sub CopyCells()
Worksheets("Sheet1").Activate
CellRef = Cells(1, 1)
Worksheets("Sheet2").Cells(CellRef, 1) = Cells(1, 2)
Worksheets("Sheet2").Cells(CellRef, 2) = Cells(1, 3)
Worksheets("Sheet2").Cells(CellRef, 3) = Cells(1, 4)
End Sub

It will copy B1, C1 and D1 from Sheet1 to columns A, B and
C in Sheet2 based on the row number in A1 on Sheet1.
 
Perhaps this non-macro approach may suffice ..

Assuming your table in Sheet1
is in cols A to D, data from row2 down, and that
col A's numbers would range from: 1-65536
*without* any duplicates

In Sheet2
-------------
Put in A1:

=IF(ISNA(MATCH(ROW(),Sheet1!$A:$A,0)),"",OFFSET(Sheet1!$A$1,MATCH(ROW(),Shee
t1!$A:$A,0)-1,COLUMN(A1)))

Copy across to C1, then copy down to cover
the max likely number in col A in Sheet1

Cols A to C in Sheet2 will return what's in cols B to D of Sheet1
according to the row number specified in col A of Sheet1
 
Back
Top