Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Paste method of Worksheet class failed 1

Status
Not open for further replies.
Andrzejek

Andrzejek

Programmer
Jan 10, 2006
8,502
US
In my Worksheet, I populate first Sheet with some data from Oracle. In column 14 (N) I have last names of Agents which I keep in an array [tt]strNames[/tt]. Then I filter Sheet1 for an Agent’s name, copy the data, add a Sheet for that Agent’s name, and paste the data to the new Sheet.

Code: 
For intA = LBound(strNames) To UBound(strNames)
    If strNames(intA) <> "" Then
        .Sheets(1).Select
        .ActiveSheet.Range("$A$1:$O$" & recTAC.RecordCount + 1).AutoFilter Field:=14, Criteria1:=strNames(intA)
        .Selection.Copy
        .Sheets(.Sheets.Count).Select
        .Sheets.Add
        .Sheets(.Sheets.Count - 1).Name = strNames(intA)
        [red]
        .ActiveSheet.Paste[/red]
        ...
    End IF
Next intA

Sheet1 has only 163 rows (and 16 columns) of data, so it is not that big. But at the RED line of code I get en Excel error: "Excel cannot complete this task with available resources. Choose less data or close other application" . At VB editor I gat: "Run-time error 1004 – Paste method of Worksheet class failed"

I don’t have anything else running on the computer, so all the computer’s resources are available.

(All of this is actually done from VB 6, that’s why all the . (dots) in the code for Excel Application object.)

What am I doing workng? It used to work...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Sort by date Sort by votes
Hi,

You can't paste a Sheet into a Sheet!

You can, however, paste the CONTENTS of a Sheet into a Sheet. But that's not what your code is attempting to do.

Rather, turn on your macro recorder, COPY a Sheet (right-click the Sheet tab for this operation) to see what code is generated. You'll name the new Sheet AFTER the Sheet.Copy operation has completed. You don't ADD anything!
 
Upvote 0 Downvote
Thenks Skip.

I am not trying to copy/"paste a Sheet into a Sheet", just the portion (filter) of the data from Sheet(1) into a new Sheet:

[tt].Sheets(1).Select[/tt] ' I make sure I am at the Sheet(1)
[tt]
.ActiveSheet.Range("$A$1:$O$" & recTAC.RecordCount + 1).AutoFilter Field:=14, Criteria1:=strNames(intA)[/tt] 'I filter here the data for one Agent's name

[tt].Selection.Copy[/tt] ' I Copy just the filtered data, not en entire sheet

I did try your suggestion and Copy Sheet1 to the end of my Workbook, but that copies all data in it, and I just want to have data for one Agent (per sheet).

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
You selected the Sheet. Then you copied the selection, which is the Sheet!

I do not recommend using the Select method in code.

Rather reference the object to copy...
Code: 
For intA = LBound(strNames) To UBound(strNames)
    If strNames(intA) <> "" Then
        With .Sheets(1).Range("$A$1:$O$" & recTAC.RecordCount + 1)
              .AutoFilter Field:=14, Criteria1:=strNames(intA)
              .Copy
        End With
        .Sheets(.Sheets.Count).Add
        
        .Sheets(.Sheets.Count - 1).Name = strNames(intA)
        
        .ActiveSheet.Paste
      '  ...
    End IF
Next intA 
[/copy]
 
Upvote 0 Downvote
BTW, the title of you thread told you that you were attempting to do what you expressly declared was different than what intended: to copy/paste a RANGE.
 
Upvote 0 Downvote
Thanks Skip, that worked like a charm.

I guess sometimes I see what I want to see in my code, which may or may not be what I want it to do.

And yes, I start work at 7:00am so I can leave work at 3:30pm (half an hour for lunch).

Enjoy your java in your sunroom :)


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
Andy, can you paste values + formats?

combo
 
Upvote 0 Downvote
One agent per sheet? Hmmmmmm seems like a report. Id use a pivot table or a query with an agent parameter, rather than x number of sheets.
 
Upvote 0 Downvote
It is a report. Grab data from Oracle, show it in Excel.
First Sheet – data for all Agents, then all data for an Agent in a separate sheet.
I guess it is too difficult for them to Filter the data on Sheet1 in Excel. (sarcasm here)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
I had the same "problem" at my very last company. You had to search thru dozens of sheets to get the one you wanted. Lots of overhead in space & time.

I became known a "the wizard" because I used dashboard-like workbooks with controls that were much easier to use and I taught them how to make better use of this fabulous spreadsheet tool.

In fact for your case, the user could be on the initial query sheet and use an Auto Filter to select an Agent! You ought to educate your users and bring them into the twenty first century of spreadsheet functionality!
 
Upvote 0 Downvote
I understand the reluctance you may face from your users. One of the ways I overcame such was to search for workbook applications that were currently being used and where I observed lots of wasted time & effort. I could envision a more efficient way, constructed a breadboard version, worked with a willing user to refine (sometimes referred to a RAD: Rapid Application Design) and turned over a better application. Of course you have to do some work on your own time in order to eventually win the day.
 
Upvote 0 Downvote
I would LOVE to bring (some of the) users into at least into XX Century (forget about XXI). But I have some obstacles to deal with. The Agents are on the road and seldom in the office. Teaching (some of them, not all) is an uphill battle. Ctrl-X, Ctrl-C, Ctrl-V is like a revolution to them. Plus other people ‘in charge’ are still under: “We have always done it that way” approach and I have to wait for them to retire in order to do any positive change.

I am getting tired of being a Don Quixote and fight with wind mills.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
I don't get it :-(

Remember Skip, now you are dealing with a guy who’s English is not his native language....

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
I see. :)
Or: I below STAND, or STAND above I, or would that be: STAND above ME? ;-)


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
A bit more complicated is an envelope addressed as this...

[pre]
WOOD
JOHN
MASS
[/pre]
...and delivered to...

[pre]
JOHN UNDERWOOD
ANDOVER, MASS
[/pre]
 
Upvote 0 Downvote
I hear they're always having a bad spell of whether in Andover, mass
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
308
combo
combo
kapok1
  • Locked
  • Question
how to use vba to forbid &quot;Worksheet Menu Bar&quot; being selected
Replies
6
Views
228
kapok1
kapok1
Katto
  • Locked
  • Question
VBA permutation of arrays
2
Replies
22
Views
428
Katto
Katto
IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
179
Andrzejek
Andrzejek
MattGreer
  • Locked
  • Question
Excel - Copy Filtered Rows from a table to a new blank worksheet
Replies
7
Views
196
combo
combo

Part and Inventory Search

Sponsor

Back
Top