copying from one sheet to another

[Patriot2879]

Hi Please can you help, I have the code below where for example in cell BK1 I have the word "ETA" and I want to copy this over in A@ and down to last row. and then I want to copy the data from "FMUI" sheet into "New" sheet, but I keep get errors. please can you help.
[/CODE]Private Sub CommandButton1_Click()
Range("BK1").Copy Range("A2").End(xlDown)).SelectWorksheets(“FMUI”). Range("C2", Range("C2").End(xlDown)).NumberFormat= "0" = Worksheets(“New”).Range(“B2”).Value
Worksheets(“FMUI”). Range("E2", Range("E2").End(xlDown)).Copy= Worksheets(“New”).Range(“G2”).Value
Worksheets(“FMUI”). Range("F2", Range("F2").End(xlDown)).Copy= Worksheets(“New”).Range(“AH2”).Value
Worksheets(“FMUI”). Range("F2", Range("F2").End(xlDown)).Copy= Worksheets(“New”).Range(“AI2”).Value
Worksheets(“FMUI”). Range("L2", Range("L2").End(xlDown)).Copy= Worksheets(“New”).Range(“H2”).Value
Worksheets(“FMUI”). Range("M2", Range("M2").End(xlDown)).Copy= Worksheets(“New”).Range(“S2”).Value
Worksheets(“FMUI”). Range("S2", Range("S2").End(xlDown)).Copy= Worksheets(“New”).Range(“AG2”).Value
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("C2:C" & lr)
.Formula ="=VLOOKUP(B2,Old!B:C,2,FALSE)"
.Value = .Value
End With
With Range("R2:R" & lr)
.Formula ="=VLOOKUP(B2,Old!B:R,17,FALSE)"
.Value = .Value
End With
With Range("AJ2:AJ" & lr)
.Formula ="=VLOOKUP(LEFT(AI2,LEN(AI2)-2),PC!A:B,2,FALSE)"
.Value = .Value
End With
With Range("E2:E" & lr)
.Formula = "=VLOOKUP(B2,Old!B:E,4,FALSE)"
.Value = .Value
End With
End Sub
[/CODE]

 

[sadboy309]

I don't understand everything, so I fixed somewhat.

Private Sub CommandButton1_Click()
Dim shFMUI As Worksheet


Set shFMUI = Worksheets("FMUI")


Range("A2").End(xlDown).Offset(1) = Range("BK1").Value
Worksheets(“FMUI”).Range("C2", Range("C2").End(xlDown)).NumberFormat = "#0"


With Worksheets("New")


.Range("B2:B" & shFMUI.Range("C2").End(xlDown).Row) = shFMUI.Range("C2", shFMUI.Range("C2").End(xlDown)).Value
.Range("G2:G" & shFMUI.Range("E2").End(xlDown).Row) = shFMUI.Range("E2", shFMUI.Range("E2").End(xlDown)).Value
.Range("AH2:AH" & shFMUI.Range("F2").End(xlDown).Row) = shFMUI.Range("F2", shFMUI.Range("F2").End(xlDown)).Value
.Range("AI2:AI" & shFMUI.Range("F2").End(xlDown).Row) = shFMUI.Range("F2", shFMUI.Range("F2").End(xlDown)).Value
.Range("H2:H" & shFMUI.Range("L2").End(xlDown).Row) = shFMUI.Range("L2", shFMUI.Range("L2").End(xlDown)).Value
.Range("S2:S" & shFMUI.Range("M2").End(xlDown).Row) = shFMUI.Range("M2", shFMUI.Range("M2").End(xlDown)).Value
.Range("AG2:AG" & shFMUI.Range("S2").End(xlDown).Row) = shFMUI.Range("S2", shFMUI.Range("S2").End(xlDown)).Value


End With


Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("C2:C" & lr)
.Formula = "=VLOOKUP(B2,Old!B:C,2,FALSE)"
.Value = .Value
End With
With Range("R2:R" & lr)
.Formula = "=VLOOKUP(B2,Old!B:R,17,FALSE)"
.Value = .Value
End With
With Range("AJ2:AJ" & lr)
.Formula = "=VLOOKUP(LEFT(AI2,LEN(AI2)-2),PC!A:B,2,FALSE)"
.Value = .Value
End With
With Range("E2:E" & lr)
.Formula = "=VLOOKUP(B2,Old!B:E,4,FALSE)"
.Value = .Value
End With
End Sub

 

[Patriot2879]

Hi thanks for your help I am still getting errors on e below code. on the BK1 cell there is a word called ETA I want to copy this into A2 ll the way down to last row, and in FMUI I want to copy t and paste into New

Range("A2").End(xlDown).Offset(1) = Range("BK1").Value
Worksheets("FMUI").Range("C2", Range("C2").End(xlDown)).NumberFormat = "#0"