Need help with copy and paste VBA code

I have the following code which I got to work, copying a range from one workbook to another.

Sub ValuePaste()

    Workbooks("04.10.17 SHELBY.xlsx").Worksheets("Summary $500-$10K").Range("B8:F21").Copy
    Workbooks("Consolidated_template_new1_WA.xlsx").Worksheets("Invoice Threshold $500-$10K").Range("B8:F21").PasteSpecial Paste:=xlPasteValues

End Sub

the problem I have is that I have to copy the same range from file "A (Shelby). ex:04.11.17 Shelby.xlsx, 04.12.17 Shelby.xlsx, etc) into file "B" Consolidated into a new range.

is there a way to copy the Range from file "A" (B8;F21) to the new desire Range?

File A Is a file that I have to open from the cloud (not on my local drive) but one file is open I got the code to work. just need to know how to copy same range to my consolidated file.

Let me know if you need sample of file.

thank you

Hi humdingaling,

I replied but I don't why it didn't post. anyhow, I tried using replacing "Thisworkbook.Worksheets("Summary $500-$10K").Range("B8:F21").Copy" as you suggested but is not working. when I try using it I get a Subscript out of range. the other challenge will be when I open a new workbook and try copying the same range to a different range (which will be added to the data already copied from prior day).

thank you again for your help.

I modified my previous code to try to make it easier when copying data into a table format, so that when I copy the data to the last row used. I got the following code partially working although it needs to be modified. So that I can copy the same range "B8:B21" from an Active Workbook to my destination Workbook.

Sub ValuePaste()
    
    Workbooks("04.17.17 SHELBY.xlsx").Worksheets("Summary $500-$10K").Range("B8:F21").Copy  
    Workbooks("Consolidated_template_new1_WA.xlsx").Worksheets("test").Range("C4").PasteSpecial Paste:=xlPasteValues
End Sub

Thanks again for your help.

THANKS AG

for some reason the format was lost when i was clearing up the data to enter the data for new week. once i formatted the cells as number , data is coming over correctly. Thank you again for all your help.

i made some changes and is working however I don't know why the first number is coming over as a date. see code below:

Sub Valuepaste()
Dim loLastRow As Long

'Workbooks("04.18.17 SHELBY.xlsx").Worksheets("Summary $500-$10K").Range("B8:F21").Copy
ActiveWorkbook.Worksheets("Summary $500-$10K").Range("B8:F21").Copy
With Workbooks("Consolidated_template_new1_WA.xlsx").Worksheets("test")
loLastRow = .Cells(Rows.Count, 3).End(xlUp).Row + 1
.Range("C" & loLastRow).PasteSpecial Paste:=xlPasteValues
End With

'Workbooks("Consolidated_template_new1_WA.xlsx").Worksheets("test").Range("C4").PasteSpecial Paste:=xlPasteValues

End Sub