Copy DATA from 1 sheet to another VBA

[shahzeb]

I want to copy fixed specific field onto another sheet every time i click the button

I am attaching sheet, i want sheet one data copied to sheet 2 by button. i have copied the heading to sheet 2 which data i want to be copied.

It should always paste in new line when i click the button and also the there should be auto serial no as well.

 

[vletm]

shahzeb
#1 I want to copy fixed specific field onto another sheet every time i click the button
Which specific field and with which button?
#2 Where is Your named other button?
#3 There has not used heading BILL AMOUNT with Sheet1.
#4 Which of above button should copy something in new line?

 

[shahzeb]

In sheet 2 there are the heading mentioned that needs to be extracted from sheet 1
like , first heading in sheet 2 is L/C# & issue Date.
it needs to be extracted from sheet 1 "D14" is fields .

 

[shahzeb]

3 - Bill amount in in sheet 1 is "F24"
4 - i need one macro code button so that the specific required fields should get copied in sheet 2 .

Understand this, Sheet 1 is a form that will be filled for 1 trade bill and what i want is i can auto create MIS of that bill. in one day some times i have 100 bills so what i want to do is fill the form and click the button so it will be copied into sheet 2 and then i will fill the form again and click the button so that data will be copied in sheet 2 below the above line and then again and again.

at end of day i'll auto created the MIS of all bills

PLEASE HELP

 

[Marc L]

A VBA demonstration to paste a cell value from a sheet to another one (as all is explained in VBA help, a must read !) :​

Sub Demo1()
    Dim R&
        R = Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Row
        Sheet2.Cells(R, 1).Value = Sheet1.[F24].Value
        
End Sub

 

[shahzeb]

Thank you for this code it is copying the data from 1 sheet to another.
but i need to know one thing that there is no path mentioned where to be copied this code is copying in the first available column.

Can you please identify the path how it will be edited in this code. "F24" is copied then i need it to be pasted on "L3". But whenever i paste click the button it should be pasted in above the line.

Moreover, if it is possible i need serial code as well that it goes every time 1 2 3 4 and so on

 

[shahzeb]

I've edited the code to my use and it is working fine. However, only one problem remains that it pasting the data in 1 row only not going into next row

Sub Button1_Click()
    Dim R&
        R = Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Row
        Sheet2.Cells(R, 12).Value = Sheet1.[F24].Value
          Sheet2.Cells(R, 2).Value = Sheet1.[D14].Value
          Sheet2.Cells(R, 3).Value = Sheet1.[D15].Value
          Sheet2.Cells(R, 4).Value = Sheet1.[D16].Value
          Sheet2.Cells(R, 5).Value = Sheet1.[D20].Value
          Sheet2.Cells(R, 5).Value = Sheet1.[D20].Value
          Sheet2.Cells(R, 6).Value = Sheet1.[D21].Value
          Sheet2.Cells(R, 7).Value = Sheet1.[M1].Value
          Sheet2.Cells(R, 8).Value = Sheet1.[D5].Value
          Sheet2.Cells(R, 9).Value = Sheet1.[G15].Valu
          Sheet2.Cells(R, 10).Value = Sheet1.[G16].Value
          Sheet2.Cells(R, 11).Value = Sheet1.[G19].Value
          Sheet2.Cells(R, 13).Value = Sheet1.[G43].Value
          Sheet2.Cells(R, 14).Value = Sheet1.[G44].Valu
          Sheet2.Cells(R, 15).Value = Sheet1.[G45].Value
          Sheet2.Cells(R, 16).Value = Sheet1.[G46].Value
          Sheet2.Cells(R, 17).Value = Sheet1.[D35].Value
End Sub

 

[Marc L]

only not going into next row

Wrong as the variable R is the next row index …​

Sub Button1_Click()
    With Sheet2.Cells(.Rows.Count, 1).End(xlUp)(2)
        .Cells(1, 2).Value = Sheet1.[D14].Value
        .Cells(1, 3).Value = Sheet1.[D15].Value
        .Cells(1, 4).Value = Sheet1.[D16].Value
        .Cells(1, 5).Value = Sheet1.[D20].Value
        .Cells(1, 6).Value = Sheet1.[D21].Value
        .Cells(1, 7).Value = Sheet1.[M1].Value
        .Cells(1, 8).Value = Sheet1.[D5].Value
        .Cells(1, 9).Value = Sheet1.[G15].Value
       .Cells(1, 10).Value = Sheet1.[G16].Value
       .Cells(1, 11).Value = Sheet1.[G19].Value
       .Cells(1, 12).Value = Sheet1.[F24].Value
       .Cells(1, 13).Value = Sheet1.[G43].Value
       .Cells(1, 14).Value = Sheet1.[G44].Value
       .Cells(1, 15).Value = Sheet1.[G45].Value
       .Cells(1, 16).Value = Sheet1.[G46].Value
       .Cells(1, 17).Value = Sheet1.[D35].Value
    End With
End Sub