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!

 

[Hugopellegrini]

Ok guys, thank you very much for your help. So this where we're standing:

Norie, your suggestion gives me "object doesnt support this property or method"

Jonmoe, this solution

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") [CODE] 
gives me "subscript out of range"

...

 

[Hugopellegrini]

So guys, thank you for your help and quick answers. This is where Im standing:

Norie: when I try what you suggested I get "Object doesnt support this property or method"

Jonmo, when I 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") [CODE]
I get "subscript out of range"

 

[Hugopellegrini]

Oops, sorry for the double post...

 

[Jonmo1]

Which line is highlighted when you get that error?

I would guess it's this line
Set sourceSheet = Worksheets("Deals - Grouped per Reportgrid")

And that means there is no sheet named "Deals - Grouped per Reportgrid" within the book you just opened.
Check for exact spelling and extra spaces.

 

[Hugopellegrini]

Jonmo, nothing gets higlighted when the error comes up. Is that something that I must change in the settings? Anyways, Grouped reportgrid is written correctly, I checked twice...

 

[Norie]

Can you post all the code you have, including any variable declarations?

 

[Hugopellegrini]

Yea sure

Sub DailyUpdate()
Const sBASE_NAME = "Deals "
Const sPipeline = "Pipeline"
Const sDaily = "Daily Reporting"

Dim sDateName As String
Dim wksNew As Worksheet
Dim Filename As String
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet

If WorksheetFunction.Weekday(Now(), 2) = 1 Then
  sDateName = Format(Now() - 3, "dd-mm-yyyy")
Else
  sDateName = Format(Now() - 1, "dd-mm-yyyy")
End If
Set wksNew = Worksheets.Add(after:=Sheets(5))
wksNew.Name = sBASE_NAME & sDateName
Filename = sPipeline & " " & sDateName & ".xls"
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")
 
 

End Sub

 

[Jonmo1]

Jonmo, nothing gets higlighted when the error comes up. Is that something that I must change in the settings? Anyways, Grouped reportgrid is written correctly, I checked twice...

Check it in both places, in the Code, and on the Sheet's Tab itself. Are they EXACTLY the same?
One way to be sure is to right click the sheet's tab, Raname
Highlight the text of the name and copy it.
Cancel the rename.
Then paste it into your code.

 

[Hugopellegrini]

Yea, this is what I've done. Copy pasting the name...

 

[Norie]

That code works perfectly fine for me.

The only time I got a subscript out of range was here:

Set wksNew = Worksheets.Add(after:=Sheets(5))

I just added extra worksheets to deal with that.