• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy DATA from 1 sheet to another VBA

shahzeb

New Member
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.
 

Attachments

vletm

Excel Ninja
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

New Member
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

New Member
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

Excel Ninja
A VBA demonstration to paste a cell value from a sheet to another one (as all is explained in VBA help, a must read !) :​
Code:
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

New Member
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

New Member
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

Code:
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

Excel Ninja
only not going into next row
Wrong as the variable R is the next row index …​

Code:
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
 
Last edited:
Top