Object does not support this property or method

[Hugopellegrini]

Hi guys,
While trying to write a macro copy pasting data from one workbook to another, I get this error :

"object does not support this property or method"

I can see that it copies the data, but it can't seem to paste it.
Here's my code

Workbooks.Open Filename:="F:\Daily updates\Test Macro\" & Filename
Set sourceSheet = Workbooks(Filename).Worksheets("Deals")
sourceSheet.Activate
sourceSheet.Range("B2:BA1000").Select
Selection.Copy
Set destSheet = Workbooks("Macro WIP.xls").wksNew
destSheet.Activate
destSheet.Cells.Select
destSheet.Paste
End Sub

where wksNew is previously defined in the macro (Set wksNew = Worksheets.Add(after:=Sheets(5)) wksNew.Name = sBASE_NAME & sDateName)


Do you see what can be wrong?

Thanks a lot!

 

[Jonmo1]

When this line runs
Set wksNew = Worksheets.Add(after:=Sheets(5))
Then wksNew is an object that is ALREADY attached to a workbook object (whichever book was active when that line executed).

Try

'previous code you mentioned setting wksNew..
Set wksNew = Worksheets.Add(after:=Sheets(5))
wksNew.Name = sBASE_NAME & sDateName

Workbooks.Open Filename:="F:\Daily updates\Test Macro\" & Filename
'When a book is opened, it automatically becomes the active book, so you don't need to specify it in the next line.
Set sourceSheet = Worksheets("Deals")

'Adjust B2 here to the top left cell of the range you want the data pasted TO.
sourceSheet.Range("B2:BA1000").Copy Destination:= wksNew.Range("B2")

Hope that helps.

 

[Hugopellegrini]

Thanks for the quick answer!

I tried what you suggested but now it's giving me subscript out of range
Moreover, shoudln't I activate the workbook I want to paste into ? Like that

orkbooks.Open Filename:="F:\Daily updates\Test Macro\" & Filename
Set sourceSheet = Worksheets("Deals - Grouped per Reportgrid")
sourceSheet.Range("B2:BF1000").Copy Destination:=Workbooks("Macro WIP.xls").wksNew("A1")

Anyways, it qlso gives me the subscript out of range

 

[Jonmo1]

No, you don't have to activate the book, because of the reason I stated in last post.
When you SET a Sheet object to a specific sheet, it ALREADY contains a reference to that Sheet's Parent Book.
So you only need to refer to the variable that you set (wksNew) and the range... wksNew.Range("A1")

Try
Workbooks.Open Filename:="F:\Daily updates\Test Macro\" & Filename
Set sourceSheet = Worksheets("Deals - Grouped per Reportgrid")
sourceSheet.Range("B2:BF1000").Copy Destination:= wksNew.Range("A1")

 

[Hugopellegrini]

I wrote that

Workbooks.Open Filename:="F:\Daily updates\Test Macro\" & Filename
Set sourceSheet = Workbooks(Filename).Worksheets("Deals - Grouped per Reportgrid")
sourceSheet.Range("B2:BF1000").Copy
Workbooks("Macro WIP.xls").Worksheets(wksNew).Activate
Ativesheet.Range("A1").Paste
End Sub
[CODE]

And it gives me "Type mismatch" error

 

[Jonmo1]

Because Worksheets(...) expects a TEXT string, wksNew is a Worksheet Object.
perhaps use wksNew.Name

 

[Hugopellegrini]

Sorry about that, I hadn't seen that you replied to me.

I tried what you suggested but it still gives me the Subscript out of range error. I was suggesting to activate the worksheet I'm copying data from (eventhough I just opened it) because when I do so, it at leasts copy the data before giving me an error (I can see the dotted line around the data) whereas it doesnt when I dont activate it.

 

[Norie]

Which line of code do you get the subscript out of.. error?

Does this work?

Set wbOpen = Workbooks.Open (Filename:="F:\Daily updates\Test Macro\" & Filename)

Set sourceSheet = wbOpen.Workbooks(Filename).Worksheets("Deals - Grouped per Reportgrid")
sourceSheet.Range("B2:BF1000").Copy wksNew.Range("A1")

End Sub

 

[Jonmo1]

did you actually try my suggestion in Post# 4 ?
Don't change it at all, try it exactly as it's posted.

 

[Norie]

The code I posted is basically what jonmo posted in #4 with some minor changes/additions.