RTE 1004- Can't be used on multiple Selections (Selection.Copy

[catielady]

I have a workbook with 7 similarly formatted sheets. I wish to consolidate the same 4 non-consecutive columns (1 column is hidden) on each of these 7 sheets into a "master sheet". However, I want the 4 columns from each sheet to paste below each other on the "Master Sheet."

When I run the macro the first time it works. However, once I run it again, I get the following error: Run-Time Error '1004' That command cannot be used on multiple selections.

The debugger shows that the problem is with the first "Selection.Copy" instruction listed.

Believing it is because this line is repeated several times, I changed it to just copy the desired range versus copying the selection. When I did this it worked again the first time and then highlighted the line as an error the second time. What do I need to change to the below macro?

Please Login or Register  to view this content.

[Aardigspook]

Hi,

I think it's that you're disabling CutCopyMode at the end of the macro, but not enabling it at the start.

Regards,
Aardigspook

[catielady]

Hello Aardigspook,

Thank you for the suggestion. I had disabled CutCopyMode as a solution to clear the clipboard at the end of the macro running thinking that was why it would not run the macro the second time. However, I get the same error when I remove the line completely. Any other ideas?

[Aardigspook]

I'm still learning VBA myself, so I'm guessing a bit here - apologies if I suggest something obvious you've already tried.

- Rather than taking out Application.CutCopyMode = False at the end, try putting Application.CutCopyMode = True at the beginning?

- Try using .Copy instead of .Select then Selection.Copy:
Application.Union(Range("B" & FR & ":C" & LR), Range("S" & SFR & ":T" & TLR)).Copy

- If you replace the first selection (Application.Union(Range("B" & FR & ":C" & LR), Range("S" & SFR & ":T" & TLR)).Select in 'Adam') with a specified range instead (i.e. using something like ActiveSheet.Range(range you want).Select) does it still fail on the first Selection.Copy or does it now fail on the second? If the first now works ok, try adding the sheet name to each reference in the Range, which I think will look like this:
Application.Union(Range("Adam!" & "B" & FR & ":C" & LR), Range("Adam!" & "S" & SFR & ":T" & TLR)).Select


If none of those work, then I think I'm out of ideas, sorry. Good luck...


Regards,
Aardigspook

ps could you surround the code with CODE tags to make it easier to read? (edit your post, select the code, then press the # icon)

[TMS]

I don't think it's anything to do with CutCopyMode. As far as I am aware, that's just the state after a range has been selected and copied ... identified by the "dancing ants". Setting CutCopyMode = False just stops the ants dancing but the copied range is still selected.

I'd suggest that an easier way than copying non-contiguous ranges is to copy the entire used range from each sheet, or at least up to the last column required, and then delete the columns you don't want from the consolidated sheet. However, if you do it that way, you'd need to be a little more careful if you need to run the macro again.

You don't need to select objects to work with them.

Maybe you could post a sample workbook so that we have something to work with and test solutions (and perhaps see where and why it fails)

Regards, TMS

[Aardigspook]

My mistake about CutCopyMode. I know there's a way to disable cut/copy in VBA, because it's disabled on a file at work, so I just assumed this was it. Having looked into it a bit more, this command clears the clipboard so that you don't get the 'There is a large amount of information on the Clipboard...' warning.
Every day is a learning day...
Sorry for semi-hijacking your thread.

[catielady]

You both were correct that CutCopyMode wasn't the culprit. I don't think the error has to do with the non-contiguous ranges as I tried that solution and receive the same error. I tried some of the other suggestions and what I uncovered was that the macro does not like the repeated instruction of Activesheet.Paste after the first run. So I went back and changed the macro to a different paste command for each sheet (Adam, Amanda, Yee, etc.). However, I ran out of alternatives to Activesheet.Paste after the fourth sheet. The macro then started to have a problem with the copy command being repeated.

Here is the revised macro, which unfortunately is not pretty. You'll notice that it references a different destination in regards to sheet and column so I could try out TMS' suggestion of deleting the unnecessary rows.

Please Login or Register  to view this content.

I have also provided an Example workbook to test out some solutions. Example.xlsm

Thank you all for the help!

[skywriter]

This might help you. I'm guessing that I got the right sub since you didn't include the title line. I don't know where you want to get some of the variables you have defined. I guessed they were from each sheet you are copying from. See if this works for you and let me know if I can help you work out any further issues.

Please Login or Register  to view this content.

[Aardigspook]

You might be able to simplify the copy and paste sections by getting rid of select, using this sort of format:

Please Login or Register  to view this content.

This would replace your:

Please Login or Register  to view this content.

and should be amendable for the other sections of your macro too.

Regards,
Aardigspook

Edit: skywriter's solution looks a lot more efficient though

[catielady]

How wonderful! Thank you so much for showing me how to do an array. I now know how to use LBound and UBound.

This seems to solve the issue of the macro failing after multiple attempts. I hope you don't mind, but I changed it a bit to achieve paste special and to first clear out Sheet 2 so I can run the macro multiple times. There is only one issue that I ran into that I can't seem to explain. When selecting the columns to copy on each sheet, you'll notice I start with row 16. However, the data I want captured starts at row 18. For whatever reason I have to set the macro 2 rows above the row I want the range to start copying at.

When it comes to Sheet("Mark"), columns B:C starts copying at row 20 rather than row 18. Oddly, columns S and T copy just fine. Is there a way I can brake out Sheet("Mark") and change the FR and LR variables to B14 to capture the rows I want? I tried and I get the error again with the debugger highlighting the first command after 'Sheets("Mark").Select'

Would you have any suggestions?

Thanks again for all of the guidance.

Please Login or Register  to view this content.

[skywriter]

With this code:

Please Login or Register  to view this content.

Always try and avoid selecting sheets and ranges, it's very slow and unnecessary. If you refer to the sheet and the range then you don't need to select it.

Please Login or Register  to view this content.

With code such as the following:

Please Login or Register  to view this content.

Since we have the with line we know that everything with just a . in front of it refers to what we mentioned in the with line.
so .range refers to Worksheets(arrSheetsNames(j)), so whatever worksheet we are talking about in the array that is the .range we are referring to.

You notice in the code:

Please Login or Register  to view this content.

I am using ws2.Range because if I put .range without specifying the worksheet it will refer to the worksheet we are talking about using the With statement. Make sense?
No selecting sheets or ranges necessary. Imagine that manually you copy some cells on a sheet and then you select another sheet, then select a range and then choose paste, how fast is that? Now imagine just being able to say computer on sheet 2 copy range x and paste it to sheet 3 range y. That's the speed difference in the code.

Now notice the code you added below.

Please Login or Register  to view this content.

What sheet is the Range you are referring to on? What sheet did you intend to use? If you do not specify a sheet then it will use the sheet that is active at the time these lines of code are run. I would suggest you specify the sheet you want these numbers to come from in the code. No need to activate the sheet or select the range, just specify the sheet name and range as I have shown you elsewhere in the code.

I apologize for not replying to you, I was no longer watching this thread. I am now, so please make changes and use any knowledge you may have gained from this post to try and solve your problem.
Let me know if you need any further assistance.

Thanks for the rep. points, that's how I knew to come back here and look at this thread.

[catielady]

I had given up hope but apparently you hadn't given up for me. Thanks for the reply. Whether late or not, it was very helpful in understanding how I was weighing down the code AND leaving out valuable information.

I made the changes you suggested however the underlying problem seems to be with me telling the code to cut and copy twice within a task. I keep getting the "Run-Time Error '1004' That command cannot be used on multiple selections error. The debugger points to the line underlined below where I instruct Excel to copy the data from Sheets("Mark"). Is there a way to somehow include the variables I have created specifically for Sheets("Mark") within the For j = LBound(arrSheetsNames) To UBound(arrSheetsNames)instructions so the code is only doing 1 copy and paste?

Please Login or Register  to view this content.

Thanks again,
Catie