multiple cell copy

[kalpeshpatel]

my problem multiple cell copy in other sheet


sheet 1 look like below


i use only 4 cells to enter data eg.

B5, E7, C12, F14


B4 E6 C11 F13

Billno Date Description Value


Sheet 2 Contain follwing Heading


A1 B1 C1 D1

Billno Date Description Value


now when i click command button on sheet 1

4 cells value (B5,E7,C12,F14) should be copy & paste in sheet 2 exact below heading eg.(A2, B2, C2, D2)


again when i enter new data in sheet 1 in same cells eg. (B5, E7, C12, F14) should be copy & paste in sheet 2 exact earlier paste data E.g (A3, B3, C3, D3)


how it possible give vba code or macro

 

[vijaySharma]

@Kalpesh,


Use the below code to get the output

Sub CopyData()

Dim strBillNo As String

Dim dtDate As Date

Dim strDescription As String

Dim lngValue As Long


    Sheets("Sheet1").Select

strBillNo = Range("B5").Value

dtDate = Range("E7").Value

strDescription = Range("C12").Value

lngValue = Range("F14").Value


    Sheets("Sheet2").Select

Range("A1").Select

Do While ActiveCell.Value <> ""

ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Offset(0, 0) = strBillNo

ActiveCell.Offset(0, 1) = dtDate

ActiveCell.Offset(0, 2) = strDescription

ActiveCell.Offset(0, 3) = lngValue


    strBillNo = ""

dtDate = 0

strDescription = ""

lngValue = 0

Sheets("Sheet1").Select

End Sub

~VijaySharma

 

[shajan]

very good and useful.

Thanks,