Hi forum,
Put your reading glasses on!
I've somehow run into the error "The command cannot be used on multiple selections"...
After doing some research, I understand this error relates to a range being an odd shape, and excel not knowing how to handle it during a copy-paste function? I'm sure the range I am trying to copy-paste is a standard rectangle, so I don't know why I'm getting this error. My VBA skillset is underdeveloped to say the least (non-existent would be more accurate), so please excuse my lack of, and incorrect use of terminology from here on.
I've created a series of check sheets, and now I'm trying to create an automated summary of these using VBA. There are five check sheets, and one summary sheet (6 separate worksheets, all in the one workbook) in total. Each check sheet is made up of one column listing checks to complete, two columns representing Marlett check boxes (for checking a 'pass' or 'fail'), and one column to list any comments associated with each check.
I've created named ranges out of all of the fail check boxes as they belong to different categories, and as such are not continuous (and they are what I want in the summary). Using VBA I've consolidated all of the named ranges on each individual sheet to form a "union-ed range" of fail checks for each respective sheet. The code then goes on to assess each cell in each "union-ed range" to see if it contains the text "a" (this a represents a "fail" marlett check box that has been ticked).
After finding a failed check, a nested "do while" loop first assesses each cell in the "fail" marlett checkbox column. It does this specifically in an upwards direction, so as to find the cell containing the title which relates to the failed check. It then repeats this process a second time (second part of "do while nest") to find another title further up the page. Both titles are located according to their unique cell fill colour.
Once the failed check has been located, along with its two associated titles, the code adds all three results to a range (at this point I will note that all three results are always of the same horizontal cell dimension, column location, and do not contain any merged cells). This range is then built on via the union function, as the code goes through and assesses the rest of the "union-ed" range of fail checks.
So........ This is where things have been a bit shaky. Once the code has checked the whole worksheet, the range containing all of the failed checks with their associated titles is copy-pasted to a specific location on the summary worksheet. Prior to pasting, a value is returned to nominate how many rows will need to be inserted on the summary sheet to fit the "results range". Up to here I've had this working (believe it or not!).
This portion of code is where my error emerges from - "The command cannot be used on multiple selections". The debugger highlights the copy-paste line of code at the very end (highlighted in red). I've removed the code that adds the to the "results range" (highlighted in orange), and substituted it for messages boxes (highlighted in green) to display the cell address of each result. This was to confirm that the code was in fact retrieving the expected cell addresses, in the correct order. Surprisingly it was.
Leaving the code in the same order, and simply enabling/disabling a line at a time I did some fault finding. If the code pastes only "title a" to the summary sheet, it works, and even pastes the correct results! I can even have the code paste the failed check, as well as "title a" or "title b", and it does this correctly too! But when I put all three together, the fun stops...
I apologise for the long winded explanation, I just wanted to make sure the whole process was understood. I also apologise for the scattered code layout - I've mentioned in previous threads that my knowledge extends no further than that of a copy-paste soldier. So it goes without saying I don't follow any protocol as such. I'm amazed I've made it this far to be honest...
I've received so much help from people here at MrExcel, I can't thank the community here enough!
As always, I'm very grateful for any assistance on this thread.
Cheers,
Dan
Put your reading glasses on!
I've somehow run into the error "The command cannot be used on multiple selections"...
After doing some research, I understand this error relates to a range being an odd shape, and excel not knowing how to handle it during a copy-paste function? I'm sure the range I am trying to copy-paste is a standard rectangle, so I don't know why I'm getting this error. My VBA skillset is underdeveloped to say the least (non-existent would be more accurate), so please excuse my lack of, and incorrect use of terminology from here on.
I've created a series of check sheets, and now I'm trying to create an automated summary of these using VBA. There are five check sheets, and one summary sheet (6 separate worksheets, all in the one workbook) in total. Each check sheet is made up of one column listing checks to complete, two columns representing Marlett check boxes (for checking a 'pass' or 'fail'), and one column to list any comments associated with each check.
I've created named ranges out of all of the fail check boxes as they belong to different categories, and as such are not continuous (and they are what I want in the summary). Using VBA I've consolidated all of the named ranges on each individual sheet to form a "union-ed range" of fail checks for each respective sheet. The code then goes on to assess each cell in each "union-ed range" to see if it contains the text "a" (this a represents a "fail" marlett check box that has been ticked).
After finding a failed check, a nested "do while" loop first assesses each cell in the "fail" marlett checkbox column. It does this specifically in an upwards direction, so as to find the cell containing the title which relates to the failed check. It then repeats this process a second time (second part of "do while nest") to find another title further up the page. Both titles are located according to their unique cell fill colour.
Once the failed check has been located, along with its two associated titles, the code adds all three results to a range (at this point I will note that all three results are always of the same horizontal cell dimension, column location, and do not contain any merged cells). This range is then built on via the union function, as the code goes through and assesses the rest of the "union-ed" range of fail checks.
So........ This is where things have been a bit shaky. Once the code has checked the whole worksheet, the range containing all of the failed checks with their associated titles is copy-pasted to a specific location on the summary worksheet. Prior to pasting, a value is returned to nominate how many rows will need to be inserted on the summary sheet to fit the "results range". Up to here I've had this working (believe it or not!).
This portion of code is where my error emerges from - "The command cannot be used on multiple selections". The debugger highlights the copy-paste line of code at the very end (highlighted in red). I've removed the code that adds the to the "results range" (highlighted in orange), and substituted it for messages boxes (highlighted in green) to display the cell address of each result. This was to confirm that the code was in fact retrieving the expected cell addresses, in the correct order. Surprisingly it was.
Leaving the code in the same order, and simply enabling/disabling a line at a time I did some fault finding. If the code pastes only "title a" to the summary sheet, it works, and even pastes the correct results! I can even have the code paste the failed check, as well as "title a" or "title b", and it does this correctly too! But when I put all three together, the fun stops...
I apologise for the long winded explanation, I just wanted to make sure the whole process was understood. I also apologise for the scattered code layout - I've mentioned in previous threads that my knowledge extends no further than that of a copy-paste soldier. So it goes without saying I don't follow any protocol as such. I'm amazed I've made it this far to be honest...
I've received so much help from people here at MrExcel, I can't thank the community here enough!
As always, I'm very grateful for any assistance on this thread.Cheers,
Dan
Code:
Set Basement_Range = Union(Range("Lots_of_named_ranges..."), Range("etc1"), Range("etc2"), Range("etc3"))
For Each Cell In Basement_Range
If Cell.Text = "a" Then
i = Cell.Row
Do Until i = 1
If Sheets("Basement").Range("I" & i).Interior.Color = RGB(79, 129, 189) Then
[COLOR=#ff8c00] If Basement_No_Checks Is Nothing Then
Set Basement_No_Checks = Range(Sheets("Basement").Range("I" & i).Offset(-1, -8), Sheets("Basement").Range("I" & i).Offset(0, 12))
Else
Set Basement_No_Checks = Union(Basement_No_Checks, Range(Sheets("Basement").Range("I" & i).Offset(-1, -8), Sheets("Basement").Range("I" & i).Offset(0, 12)))
End If[/COLOR]
j = Cell.Row
Do Until j = 1
If Sheets("Basement").Range("I" & j).Interior.Color = RGB(220, 230, 241) Then
[COLOR=#ff8c00] Set Basement_No_Checks = Union(Basement_No_Checks, Range(Sheets("Basement").Range("I" & j).Offset(-2, -8), Sheets("Basement").Range("I" & j).Offset(0, 12)))
[/COLOR] [COLOR=#ff8c00]Set Basement_No_Checks = Union(Basement_No_Checks, Range(Cell.Offset(0, -8), Cell.Offset(0, 12)))[/COLOR]
[COLOR=#008000]' MsgBox "Found section title at address: " & Sheets("Basement").Range("I" & i).Address
' MsgBox "Found ROOM title at address: " & Sheets("Basement").Range("I" & j).Address[/COLOR]
Exit Do
End If
j = j - 1
Loop
Exit Do
End If
i = i - 1
Loop
End If
Next
If Basement_No_Checks Is Nothing Then
MsgBox "There are no failed checks in the Basement"
ElseIf (ActiveWorkbook.Sheets("Summary").Range("Summary_Basement").Offset(3, 0)) = "" Then
Number_of_Basement_Rows = ((Basement_No_Checks.Count) \ 20)
Application.Goto (ActiveWorkbook.Sheets("Summary").Range("Summary_Basement").Offset(3, 0))
ActiveCell.Resize(Number_of_Basement_Rows).EntireRow.Insert
[COLOR=#ff0000] Basement_No_Checks.Copy Destination:=(ActiveWorkbook.Sheets("Summary").Range("Summary_Basement").Offset(3, -1))[/COLOR]
End If
Last edited:
