Need help with copy and paste VBA code

[Jocote46]

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

Please Login or Register  to view this content.

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

[humdingaling]

assuming you are running the code while file "A" is open and active
you can replace Workbooks("04.10.17 SHELBY.xlsx") with thisworkbook
ie

Please Login or Register  to view this content.

[Jocote46]

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.

[Jocote46]

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.

Please Login or Register  to view this content.

Thanks again for your help.

THANKS AG

[humdingaling]

ThisWorkbook = the workbook in which has the code under it (which i assumed has the code baked into the file but now i noticed it is xlsx so this cant be)

Try ActiveWorkbook instead of thisworkbook
"active" means the workbook which has focus at the time of running the code

are you overwriting the data in consolidated template or are you adding to the bottom?

[Jocote46]

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[Jocote46]

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.

[humdingaling]

Please Login or Register  to view this content.

this will only paste values and not formats
if you want formatting as well
you can try

Please Login or Register  to view this content.