Copy and paste data in other sheet with macro

**anuj_sethi1043** · 10-26-2010, 03:11 AM

On click of button i want to copy and paste data from sheet 1 into specific cell of sheet 2

for example contents of cell b1 goes into sheet 2 in d14
and then for many others
please help

**blane245** · 10-26-2010, 12:19 PM

How's this

' copy the contents of sheet1 to sheet2
Sub CopyContents()

    With Sheet2
        Cells(2, 2).Copy .Cells(14, 4)
        Cells(3, 2).Copy .Cells(15, 4)
        Cells(4, 2).Copy .Cells(16, 4)
        Cells(5, 2).Copy .Cells(17, 4)
        Cells(6, 2).Copy .Cells(18, 4)
        Cells(7, 2).Copy .Cells(6, 4)
        Cells(8, 2).Copy .Cells(9, 4)
        Cells(9, 2).Copy .Cells(10, 4)
        Cells(10, 2).Copy .Cells(11, 4)

    End With
End Sub

**JBeaucaire** · 10-26-2010, 01:06 PM

Since many of the cells are adjacent in both sheets, you can do them as a group:

Sub CopyData()
    
    With Sheets("Sheet1")
        Sheets("Sheet2").Range("D14:D18").Value = .Range("B2:B6").Value
        Sheets("Sheet2").Range("D6").Value = .Range("B7").Value
        Sheets("Sheet2").Range("D9:D11").Value = .Range("B8:B10").Value
    End With
    
End Sub