VBA copy paste between two books. 104 Paste Special method

[KirillKU]

Hi, I found a number of similar topics, however it did not help my code. Range from opened workbook should be copied into new opened file test and saved.
This is a piece of code:


Set Sheet = ThisWorkbook.Sheets("store data")
Sheet.Range(Cells(i, 4), Cells(i + 365, 19)).Copy
'Set wSource = ActiveWorkbook
Set MasterSource = Workbooks.Open("test.xlsx")
MasterSource.Worksheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial xlPasteValues

paste special row returns an error. Please help

 

[sastoka]

Hi
Try replace

MasterSource.Worksheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial xlPasteValues

by this

Workbooks("test.xlsx").Sheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial xlPasteValues

regards

 

[KirillKU]

Hi
Try replace

MasterSource.Worksheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial xlPasteValues

by this

Workbooks("test.xlsx").Sheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial xlPasteValues

regards

Sorry, it is out of range now.

 

[sastoka]

can you show us all the code and tell us the exact error you're getting ?

 

[KirillKU]

Sure,

Dim MasterSource As Workbook
Sheets("Store data").Select
For i = 1820 To 6000
If Sheet.Cells(i, 1).Value = StoreNumber Then
Application.ScreenUpdating = True
Set Sheet = ThisWorkbook.Sheets("store data")

Sheet.Range(Cells(i, 4), Cells(i + 365, 19)).Copy
Set MasterSource = Workbooks.Open("\\140fp-9260\cafegroup$\test.xlsx")
MasterSource.Worksheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial xlPasteValues

' MasterSource.Sheets("store data").Range("D" & i).Value = wSource.Sheets("store data").Range("D" & i&).Value
Application.CutCopyMode = False
MasterSource.Close SaveChanges:=True

Exit For
Else: End If
Next i

Thank you


The error is "1004" PasteSpecial method of range class failed.
In case if I use full workbook name as you adviced, I am getting Error "9" Subscript out of range at the same line

 

[sastoka]

try this.

MasterSource.Worksheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial xlPasteValues

replace by

MasterSource.Worksheets("store data").Range(Cells(i, 4), Cells(i + 365, 19)).PasteSpecial Paste:=xlPasteValues

 

[KirillKU]

Hi, I figured out what VBA did not like in my code. It required sheet name before the cell in this part Range(sheet.Cells(i, 4), sheet.Cells(i + 365, 19)). It works now

Can somebody please what is the best practice to switch between workbooks/sheets properly?
Thank you