MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

copy / paste problem

  Asked By: Daisy    Date: Aug 17    Category: MS Office    Views: 578

Firstly, sorry if this has been asked before, I've tried searching for
it inthe archive, but with no luck.


I'm trying to copy and paste special --> values from various
worksheets to various other worksheets.

It's all going fine apart from a few worksheets which have lots of
hidden rows and columns.

When I make my selection (either by range or by
specialcells(xlCellTypeVisible) and then try to copy the selection I
get a runtime error '1004' "That command cannot be used on multiple

It would be a slow process, but currently I'm thinking of checking
each column individually to see if it's hidden, if so, log it, unhide
the column. Then repeat for each row.
Then copy ->paste special ->values

then re-hide (or just delete) all the originally hidden columns.

If anyone knows a more efficient way of getting the job done It'd be

(the hidden columns / rows are in different positions on different
worksheets, so I need a generic, reusable solution).



4 Answers Found

Answer #1    Answered By: Bian Nguyen     Answered On: Aug 17

Unfortunately the error  message is correct. You can´t Copy-Paste multiple

I think you need to select each part and copy  it seperately

Is there anything unique in the column headers you can search for so you can
select each column in turn?

Answer #2    Answered By: Daniel Jones     Answered On: Aug 17

: Unfortunately the error  message is correct. You can't
: Copy-Paste multiple selections.
: I think you need to select each part and copy  it
: seperately

If you know the destination to be pasted, you could use
the Areas property of the range  to step through the
collection of areas it returns.

Answer #3    Answered By: Mercedes Andrews     Answered On: Aug 17

there are unique headers in the columns, and they (and the data) are
derived from a pivot table in a different worksheet.

(though some of the rows  are hidden as well).

I'm still pondering how best to do this, the process will only need to
be run once a month, over 15 to 20 repititions, so I might settle for
a CPU-intensive solution and run it over a weekend.

Not ideal, but will do as a kludge while I investigate something more

I'll post an update once I've decided upon a solution.

Answer #4    Answered By: Fuzairah Neeman     Answered On: Aug 17

I think I've got it to work ok now.

I'm just making a copy  of the entire worksheet into another workbook,
then renaming it and then using the ActiveWorkbook.BreakLink method to
generate the required values, leaving the formatting untouched.

Didn't find what you were looking for? Find more on copy / paste problem Or get search suggestion and latest updates.