Copy Cell Range From One Workbook to Another

[Ark68]

I have code that opens up a second workbook. Since the second workbook is variable named, I use a formula to name the workbook name.

With wshT.Range("C2")
    sfname = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xls"
    Set wshcore_staff = Workbooks(sfname).Worksheets("STAFF")
End With

wshT is workbook1, C2 is user inputted date.
wshcore_staff is the staff worksheet of the workbook resulting from sfname. (This workbook has been previously opened in earlier code)

I need to copy the contents only of a range of cells (B4-B10) from wshcore_staff to wshT cell C5.

I think it's an easy solution, but I'm drawing a blank.
I wish to

 

[VoG]

Try

With wsht.Range("C2")
    sfname = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xls"
    Set wshcore_staff = Workbooks(sfname).Worksheets("STAFF")
End With
wshcore_staff.Range("B4:B10").Copy Destination:=wsht.Range("C5")

 

[Ark68]

Hi VoG, your simple suggestion did the trick, but didn't provide the full expected results.
The range of cells copied are formula based and I simply need the values copied. Currently, the results on the destination sheet are #REF.

 

[VoG]

Try

With wsht.Range("C2")
    sfname = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xls"
    Set wshcore_staff = Workbooks(sfname).Worksheets("STAFF")
End With
wshcore_staff.Range("B4:B10").Copy
wsht.Range("C5").PasteSpecial Paste:=xlPasteValues

 

[Ark68]

Hi VoG,

Thanks for your continued support. I am receiving a "PasteSpecial method of Range class failed." error on this line ...

Sub import_stafflist()
Application.EnableEvents = False

Dim wshT As Worksheet
Dim wshcore_staff As Worksheet
Dim sfname As String
Set wshT = Workbooks("worksheets.xls").Worksheets("Launch")
With wshT.Range("C2")
    sfname = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xls"
    Set wshcore_staff = Workbooks(sfname).Worksheets("STAFF")
End With
wshcore_staff.Range("H4:H16").Copy
ActiveSheet.Unprotect
wshT.Range("B8").PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
ActiveSheet.Protect
End Sub

 

[VoG]

I have a feeling that unprotecting a sheet may clear the clipboard. Try changing the code order

Sub import_stafflist()
Application.EnableEvents = False

Dim wshT As Worksheet
Dim wshcore_staff As Worksheet
Dim sfname As String
Set wshT = Workbooks("worksheets.xls").Worksheets("Launch")
ActiveSheet.Unprotect
With wshT.Range("C2")
    sfname = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xls"
    Set wshcore_staff = Workbooks(sfname).Worksheets("STAFF")
End With
wshcore_staff.Range("H4:H16").Copy
wshT.Range("B8").PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
ActiveSheet.Protect
End Sub

 

[Ark68]

LOL ... you answered before I had a chance to edit my post. You're quick!!

Yes ... that was the problem .. I put the unprotect line at the top of the code.