Object Required

[SBF12345]

Greetings,

I am receiving an object required error on the following line:

COM.Worksheets(AC).Range(Cells(j, Column.End(xlToRight) + 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Other applicable lines include the following:

AC = ws.Name

Dim AC As String

Set COM = Workbooks("COMM_COMBINED.xls")

j is an integer.

I am not concerned with the column value used to identify the range object so long as the final result allows for the selection of a cell all the way to the right of the consumed adjacent row cells plus one more column over.

 

[MARK858]

Deleted as confused by the OP's syntax and not looking at the other posts to try and work it out.

 

[SBF12345]

This post is unrelated in terms of subject matter and does not violate the rules as I am understanding them. I believe it is correct to start a new thread, though I may be wrong. See Rules #12 https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

In trying to run the script I am now receiving an "invalid qualifier" error on the following line changes included:

COM.Worksheets(AC).Range(Cells(j.End(xlToRight) + 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[StephenCrump]

SBF12345, can you please post sufficient code so that we can see what you're trying to do?

It's not at all clear from the incorrect syntax of the one line that's causing problems.

 

[SBF12345]

I ended up writing it differently. It runs slower, but it works and time isn't a huge factor here. This is what I came up with:

COM.Worksheets(AC).Activate            Cells(j, 1).Select
            Selection.End(xlToRight).Offset(0, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

@ StephenCrump the invalid qualifier error was due to the contents of the Cell property and the j there was an integer variable of the j = j+1 loop type. The chunk is part of a loop structure.

 

[StephenCrump]

I am glad you got your syntax sorted.

Your code will run faster if you avoid .Activate and .Select:

COM.Worksheets(AC).Cells(j, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Paste:=xlPasteValues

 

[SBF12345]

Thanks for the alternate script...time does count for something!