Solved: pasting with formulas - problem with macro

[noobie]

hi,

Could anybody help me with this code. There is some problem with one line and i've tried almost everything but it still can't work.

Pls look through. :bow:



Sub abc()

ActiveSheet.Unprotect

Dim lrow As Long

With ActiveSheet

lrow = .Range("H" & Rows.Count).End(xlUp).Row + 1
.Range("A1:o12").copy .Range("A" & lrow)
.Range("A" & lrow + 1).Resize(10, 15).Select
On Error Resume Next
Selection.SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error GoTo 0
End With
MsgBox "Please fill in the necessary information in the yellow filled colour cells."

End Sub

[Simon Lloyd]

As you didn't state which line you are having a problem with i will take a wild guess and say its this one :


.Range("A1:o12").copy .Range("A" & lrow)

what is it exactly that you are trying to do? is your copied range supposed to pasted in the region
.Range("A" & lrow), a quick look at your workbook shows you have merged cells too, so the copy and paste method youre are trying to use won't happen as excel requires you to unmerge or for the merged cells to be exactly the same size....I think!

Regards,
Simon

[noobie]

Hi Simon,

Thanks for replying. It worked fine for the other sheets. It only showed an error on this page.
After debugging, it seemed that this line has some problems.

Thanks



.Range("A" & lrow + 1).Resize(10, 15).Select

[Charlize]

Where are the other sheets that worked ? .Range("A1:o12").copy .Range("A" & lrow) Gives me errorcode 1004 = You can't change a part of a merged cell ...

Charlize

[Simon Lloyd]

Charlize, thats what i couldnt understand! just one sheet with code that would never work!

I suppose he could unmerge, paste then merge but would probably mess his formatting up!

Regards,
Simon

[mdmackillop]

try
Option Explicit

Sub abc()

Dim lrow As Long
With ActiveSheet
.Unprotect
lrow = .Range("H" & Rows.Count).End(xlUp).Row + 3
.Range("A1:O12").Copy .Range("A" & lrow)
On Error Resume Next
.Range("A" & lrow + 1).Resize(10, 15).SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error GoTo 0
End With
MsgBox "Please fill in the necessary information in the yellow filled colour cells."

End Sub

[noobie]

hi,

Thanks for all your replies. I know merged cells are a hassle. But thankfully, mdmackillop's code solved my problem.

Once again.. thanks for all your response! :)