Copy cell from different workbook into another

[Patriot2879]

hi all, good morning, hoping you can help , i have the code below where i am triung to copy cell C1 from Sheet1' (another workbook) and paste into 'ROTA' which is another workbook into cells B2 to B27, but its coming up with an errror, can you help me please

rivate Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lookuprng As Range

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Copy("G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\Rota\Rolling Rota 2017.xlsx")
Set lookuprng = wb2.Sheets("Sheet1").Range("C1")
Set ws1 = wb1.Sheets("ROTA")
ws1.Range("B2:B27").Paste
wb1.Activate
With ws1
i = 7
Do Until .Cells(i, 2) = ""
    Cells(i, 5).Value = Application.VLookup(Cells(i, 2).Value, lookuprng, 2, 0)
    i = i + 1
Loop
End With
End Sub

 

[alansidman]

When you run the Macro and get an error, what is the error message and when you run Debug, what line of code is highlighted? Important to solving your issue.

 

[Patriot2879]

Hi, the error i am getting is Compile error: Method or data member not found, and its highlighting in yellow Private Sub CommandButton1_Click() and also the .Copy part, hope this helps

 

[Patriot2879]

Any help will be greatly appreciated with this, i am still failry new at VBA and still learning

 

[Fluff]

Try

Set wb2 = Workbooks.[COLOR=#ff0000]Open[/COLOR]("G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\Rota\Rolling Rota 2017.xlsx")

 

[Patriot2879]

Hi thanks for your help, i have inputted the above code but i get a new error now on the below. The error says 'Run-time error 438, Object doesn't support this property or method. Please can you help me with this? thanks again

ws1.Range("B2:B27").Paste

 

[Fluff]

This should copy the data, but the vlookup will fail as the range was a single cell

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lookuprng As Range

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\Rota\Rolling Rota 2017.xlsx")
wb2.Sheets("Sheet1").Range("C1").Copy wb1.Sheets("ROTA").Range("B2:B27")
wb1.Activate
With wb1.Sheets("ROTA")
i = 7
Do Until .Cells(i, 2) = ""
    .Cells(i, 5).Value = Application.VLookup(.Cells(i, 2).Value, lookuprng, 2, 0)
    i = i + 1
Loop
End With
End Sub

 

[Patriot2879]

brill that works thank you, i just get 1 error now on the code below that comes up in yellow but i dont knopw what this code is supposed to do. can i delete this?

.Cells(i, 5).Value = Application.VLookup(.Cells(i, 2).Value, lookuprng, 2, 0)

 

[Fluff]

can i delete this?

Yes
I've no idea what it's meant to be doing either.

 

[Patriot2879]

great this is fantastic that works now thank you again