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]

to this point I've been assuming that you're copying FROM the book that you opened
Workbooks.Open Filename:="F:\Daily updates\Test Macro\" & Filename
Is that correct?

And the sheet named "Deals - Grouped per Reportgrid" is within that book that was opened
Is that correct?

 

[Hugopellegrini]

****... how is that possible?
So this due to my excel workbooks. is it possible that there is too much data to copy?

 

[Jonmo1]

****... how is that possible?
So this due to my excel workbooks. is it possible that there is too much data to copy?

Don't get sidetracked...
Norie was only saying that the book he tested with on his own computer didn't have 5 worksheets, so it wasn't possble to add a sheet after the 5th sheet (because the 5th sheet didn't exist).
So he had to add some sheets so that a 5th sheet actually existed (in HIS book)

 

[Norie]

I just tested with a basic setup and made some assumptions.

For example, I assumed that the worksheet 'Deals - Grouped per Reportgrid' is in the workbook being opened.

Perhaps if you explain in words what the code is meant to do it might help.

 

[Hugopellegrini]

Yea Jonmo, I understand that. It actually happened to me as well when I first tried the macro.

But how is it possible that its working on Norie's computer and not on mine?

Btw Jonmo: I'm copying data from the workbook Ive just opened (Filename) and the data is in the sheet (Deals reportgrid) in that same workbook

 

[Hugopellegrini]

The code is supposed to create, in the workbook (Macro WIP) a new worksheet (Date of yesterday except if monday, then friday).
Then copy data from the Workbook (Filename) worksheet (Deal reportgrid) into this newly created worksheets. I need to open the workbook (Filename) so that I can copy the data

 

[Jonmo1]

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("[COLOR=#ff0000][B]Deals - Grouped per Reportgrid[/B][/COLOR]")
sourceSheet.Range("B2:BF1000").Copy Destination:=wksNew.Range("A1")
 
 

End Sub

Yea Jonmo, I understand that. It actually happened to me as well when I first tried the macro.

But how is it possible that its working on Norie's computer and not on mine?

Btw Jonmo: I'm copying data from the workbook Ive just opened (Filename) and the data is in the sheet (Deals reportgrid) in that same workbook

Which is it?
"Deals reportgrid"
or
"Deals - Grouped per Reportgrid"



In this instance, "subscript out of range" simply means the sheet that was referenced does not exist.
Simple as that.

 

[Hugopellegrini]

It is Deals - Grouped per Reportgrid
Sorry I didnt write properly in the second post

 

[Hugopellegrini]

But what I dont understnad in my code is that when I copy, the workbook Filename is active. So how does excel know where to paste it if I just say paste it into wksnew. I should tell it to paste it into workbooks micro wip worksheet wksnew, no?

 

[Jonmo1]

Try

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
Dim SrcBk As Workbook

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"

Set SrcBk = Workbooks.Open(Filename:="F:\Daily updates\Test Macro\" & Filename)
Set sourceSheet = SrcBk.Worksheets("Deals - Grouped per Reportgrid")

sourceSheet.Range("B2:BF1000").Copy Destination:=wksNew.Range("A1")
End Sub