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!

 

[Norie]

jonmo

I've already posted code that used a workbook reference to the workbook being opened.

The OP said it didn't work.

 

[Jonmo1]

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?

Because this line

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

The Object Variable wksNew is automatically associated with whichever book was active at the time that line executed.

so no matter which other books are activated during the execution of your code,
VBA is smart enough to know that wksNew is referring to the book that was active when that variable was Set.

 

[Jonmo1]

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

jonmo

I've already posted code that used a workbook reference to the workbook being opened.

The OP said it didn't work.

It didn't work because it has some syntax errors.
It was trying to reference a book within a book
Set sourceSheet = wbOpen.Workbooks(Filename).Worksheets("Deals - Grouped per Reportgrid")
wbOpen is already a workbook object, so you can't get another workbook from it.

 

[Hugopellegrini]

It worked!!!! Youpi, thank you so much mate
What was different in your version?

 

[Jonmo1]

The basic difference is what Norie tried to do in Post#8

First set a Workbook Object variable to the book you're opening
Set SrcBk = Workbooks.Open (Filename:="F:\Daily updates\Test Macro\" & Filename)

THEN set a SHEET object within that Book Object
Set sourceSheet = SrcBk.Worksheets("Deals - Grouped per Reportgrid")


Previous attampts were assuming the Book you opened became the active workbook for the Set line


It would seem that the book you're opening has some code in it that for one reason or another activates some other book.

 

[Norie]

Oops, never noticed that little typo.

 

[Hugopellegrini]

Just a quickie while we're still on this macro
If i then want to select column K of that newly pasted data and replace " " by " " (2 spaces by 1 space), why does

ActiveSheet.Columns("K:K").Select
    Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

doesn't work?

 

[Norie]

One reason could be because the code isn't running on the correct worksheet.

Try replacing ActiveSheet with a reference to the worksheet you want the code to work on.

You can lose the Select/Selection too.

I've used wks in the following code but change it if needed.

wks.Columns("K:K").Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Jonmo1]

Probably for the same reason this thread went on for 4 pages.
It's assuming which book/sheet is currently Active.

Don't depend on which book/sheet is active.
Explicitly Name your book/sheet as we did in the final code that worked.
And no need to select it either..

Try

wksNew.Range("K:K").Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Hugopellegrini]

What's wks? Should I write wksNew instead?