Solved Insert copied cell with formula and merged cell

July 20, 2017 at 22:05:15
Specs: Windows 10
Hello,

I have worksheet that has merged column:
C46:C49, (which contain Number Format Cell),
D46:E49,
F46:K49,
L46:M49 (which contain Currency Format Cell),
N46:R49,
S46:U49 (which contain Currency Format Cell),
V46:Y47,
V48:Y48 (which contain Date Stamp Format)
V49:Y49 (which contain Time Stamp Format)
Z46:Z49,
AA46:AA49

I wish to have a Active X Command Button that can copy all above cell and insert the copied cell in next down row with keeping the same format and merged cell and looping function.

Appreciate the help. Thank You.


See More: Insert copied cell with formula and merged cell

Reply ↓  Report •

#1
July 21, 2017 at 03:58:59
What do you mean by "keeping the....looping function"?

What looping function?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#2
July 21, 2017 at 08:46:17
Nevermind, forget the 'looping function' due to problem solved. Last time time used the code for Command Button it just appeared one time only and thats why i think its need the looping code too.

Mosts excel forum said its cannot be done to insert the copied merged cell using command button but I already found the codes altered by myself. Its work but without border and formula and always triggered the vba msg box said run-time error 404 and I found the code to ignore the run-time error too.

Here is my code. Need help how to keep the formula from original cell.How can I achieve that?
Appreciate the help. Thank You.

Private Sub CommandButton1_Click()
On Error Resume Next
Dim mySheets
Dim i As Long
mySheets = Array("sheet1")
For i = LBound(mySheets) To UBound(mySheets)
With Sheets(mySheets(i))
.Range("A46:AC49").EntireRow.Insert Shift:=xlDown
.Range("A46:A49,C46:C49,D46:E49,F46:K49,L46:M49,N46:R49,S46:U49,V46:Y47,V48:Y48,V49:Y49,Z46:Z49,AA46:AA49").Merge.Borders.Weight = xlThin
End With
Next i
End Sub

message edited by eltigre


Reply ↓  Report •

#3
July 21, 2017 at 09:41:07
✔ Best Answer
Instead of "ignoring" the error, you should correct it. With your method, you could be hiding other errors and never know it.

This is the line that is causing the error:

.Range("....").Merge.Borders.Weight = xlThin

Maybe I'm wrong, but I don't believe that you can combine the "merge" action and also apply borders with a single instruction.

In addition, I see a reference to "A46:A49" in your code, but I don't any mention of Column A in your original post. Was that merely an omission in the OP?

In any case, assuming that all formatting (merging, borders, etc) has already been applied to Rows 46:49, give the following code a try. Obviously, I don't know what formulas you are using, so I can't predict any #REF errors, but I was able copy the merging, the borders, the formats, the formulas, etc. with the following code.

Private Sub CommandButton1_Click()
Dim mySheets
Dim i As Long
mySheets = Array("sheet1")
  For i = LBound(mySheets) To UBound(mySheets)
    With Sheets(mySheets(i))
     Rows("46:49").Copy
      Range("A46:A49").EntireRow.Insert Shift:=xlDown
      Range("A46").PasteSpecial Paste:=xlPasteFormats
      Range("A46").PasteSpecial Paste:=xlPasteFormulas
    End With
  Next i
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
August 3, 2017 at 05:02:18
Hello DerbyDad03,

Sorry late reply, I am away from PC for a while....

I tried your code and its work.

Thank you so much..


Reply ↓  Report •

Ask Question