check box to copy and paste a merged cell range to the first

[Lowell In the south]

Here is the code I have picked up on the board. It appears to be working to find the first blank cell in a visible range of cells. It appears to be copying the correct range which is currently merged in an effort to ensure all the text will be desplayed when a 2nd copy paste takes place to the same range. My problem is after it copies and pastes for the first time. The next time it tells me " This operation requires the Merged cells must be identicaly sized" refrencing the first paste line. I know Merged cell are BAD. But I am trying to figure out to automaticaly fill in the top area of a sheet with blocks of Text that are selected by using check boxes. The text boxes are between 2 and 6 cells long and 2 cells tall, and are to the right of the check boxes. You check the box and the cells are automaticaly copied and pasted to the first blank cell in the top range of the document. (make sence?)

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then

Range("AI2:AL3").Select
Selection.Copy
Dim Rg As Range
'// Filtered rg with header = A10:Z19
Set Rg = Range("A10:Z19")
'// Assumes Range is Filtered so now get the result
Set Rg = Rg.SpecialCells(xlCellTypeVisible)
Rg.SpecialCells(xlCellTypeBlanks)(1, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Else
Mymsg = "Not enough room on top of quotation. LRBII"
End If
End Sub

I think i am close but willing to try any thing!

_________________




The KING lives on at Graceland!
This message was edited by Lowell in the south on 2002-10-04 18:26

 

[rikrak]

Hi,

What about using 1 paste with Paste:=xlAll?

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False

 

[Lowell In the south]

That appears to nullify the effect of the part of the code that looks for the next blank cell. I think due to the fact that the range I am attempting to copy and paste is merged but i am just unsure how else to do what I am attemping. THANK YOU FOR YOUR RESPONCE!!!

 

[rikrak]

I think you have to figure out another way to find an empty cell, because "Rg.SpecialCells(xlCellTypeBlanks)(1, 1).Select" finds a cell in the merge area.

I think looping trough the selected range while checking cells .MergeArea.Columns.Count=1 is the best way to do this.

 

[Lowell In the south]

Kinda new at this could you give me an expample of where to put the code

 

[rikrak]

New code:
<pre>
Private Sub CheckBox1_Click()
Dim Rg As Range
Dim cl As Range
Dim found As Boolean
If CheckBox1.Value = True Then
Range("AI2:AL3").Select
Selection.Copy
'// Filtered rg with header = A10:Z19
Set Rg = Range("A10:Z19")
'// Assumes Range is Filtered so now get the result
Set Rg = Rg.SpecialCells(xlCellTypeVisible)
'// find an empty cell and not merged
For Each cl In Rg
If cl = "" And cl.MergeArea.Columns.Count = 1 Then
found = True
cl.Select
Exit For
End If
Next
If found Then
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Else
MsgBox "No empty cell to paste to"
End If
Else
Mymsg = "Not enough room on top of quotation. LRBII"
End If
End Sub
</pre>

 

[rikrak]

oops wrong threat
This message was edited by rikrak on 2002-10-06 04:40