excel vba - Copy data from sheet 1 to sheet 2 using VBA

I have an excel spreadsheet. There are two sheets in this.

• Sheet1:

  
  
  
  
  
  • Company
  
  
  • Department
  
  
  • Addressline1
  
  
  • Addressline2
  
  



• Sheet2:

  
  
  
  
  
  
  • Account
  
  
  • Department
  
  
  • Address
  
  

I need to :

• Check till the last non blank row in Company column.


• Copy the Company data values into Sheet2.Account, Sheet1.Department into Sheet2.Department.


• Concatenate Sheet1.Adddressline1 and Sheet2.Addressline2.


• Copy into Sheet2.Address on clicking a button using VBA Macros.

code:

Sub Button1_click()
 Worksheets("Sheet1").Range("A2").Copy _

    Destination:=Worksheets("Sheet2").Range("F2" & Rows.Count).End(xlUp).Offset(1)
 Worksheets("Sheet1").Range("B2").Copy _
    Destination:=Worksheets("Sheet2").Range("C2" & Rows.Count).End(xlUp).Offset(1)
 Worksheets("Sheet1").Range("F2").Copy _
    Destination:=Worksheets("Sheet2").Range("B2" & Rows.Count).End(xlUp).Offset(1)
  Worksheets("Sheet1").Range("H2").Copy _
    Destination:=Worksheets("Sheet2").Range("K2" & Rows.Count).End(xlUp).Offset(1)
 Worksheets("Sheet1").Range("I2").Copy _
    Destination:=Worksheets("Sheet2").Range("K2:K200" & Rows.Count).End(xlUp).Offset(1)
  Worksheets("Sheet1").Range("L2").Copy _

    Destination:=Worksheets("Sheet2").Range("M2" & Rows.Count).End(xlUp).Offset(1)
Worksheets("Sheet1").Range("AO2").Copy _
    Destination:=Worksheets("Sheet2").Range("I2" & Rows.Count).End(xlUp).Offset(1)
 Worksheets("Sheet1").Range("BF2").Copy _
    Destination:=Worksheets("Sheet2").Range("J2" & Rows.Count).End(xlUp).Offset(1)
End Sub