Solved: Copy/Paste within a Macro

[abba92]

Hello!

I'm working in Excel 2003 and have created a macro. This macro is supposed to do a few different functions; unhide some columns, hide some columns as well as copy the contents of a range of cells in a workbook and paste the fomulas into the same range of cells in a different worksheet.

Everything works fine except the formulas don't actually paste into the second worksheet. I've unprotected it, just in case that was the issue, but it wasn't.

The code is below:

[VBA]Sub CasepackStyle()
'
' CasepackStyle Macro
' Macro recorded 12/20/2006 by xxx
'

'
ActiveWorkbook.Unprotect Password:="3581479"
ActiveSheet.Unprotect Password:="3581479"
Columns("CJ: DK").Select (had to add a space or I got a smiley)
Selection.EntireColumn.Hidden = False
Range("H35").Select
Sheets("Master StyleColor").Visible = True
Range("H35:AB448").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("StyleColor 1 Order by Size").Select
Range("H35:AB448").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("G36").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Master StyleColor").Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("StyleColor 1 Order by Size").Select
Range("E36").Select
ActiveSheet.Protect Password:="3581479"
ActiveWorkbook.Protect Password:="3581479"
End Sub[/VBA]

Thanks for any help!

[CBrine]

I would suggest some major changes. Just the difference between recording and building macro's.
[vba]
Sub CasepackStyle()
Dim wb As Workbook, ws As Worksheet, ps As Worksheet
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Master StyleColor")
Set ps = wb.Sheets("StypleColor 1 Order by Size")
wb.Unprotect "3581479"
ps.Unprotect "3581479"
ws.Visible = True
ws.Range("H35:AB448").Copy
ps.Range("H35").PasteSpecial Paste:=xlpasteFormula
ws.Visible = False
ps.Activate
ps.Range("E36").Activate
wb.Protect "3581479"
ps.Protect "3581479"
End Sub
[/vba]

HTH
Cal

[abba92]

Thanks, Cal! The only thing it doesn't like is this line:

[VBA]ps.Range("H35").PasteSpecial Paste:=xlpasteFormula[/VBA]

Thanks again!

[CBrine]

I think I typo'ed on this line.

Set ps = wb.Sheets("StypleColor 1 Order by Size")

should be
Set ps = wb.Sheets("StyleColor 1 Order by Size")

Fix this and the other line should work.

Cal

PS-If you already fixed this, let me know what error your are recieving?

[abba92]

Cal -

Yes, I caught that earlier and fixed it to what you have listed above. Once I did that, it's the Paste.Special line that stopped it.

Thanks again. I've learned so much already (love the 'Set' feature!).

[CBrine]

What was the error your recieved?

[CBrine]

Nevermind, I was able to recreate the error on my system. This should fix it up.

[vba]
Dim wb As Workbook, ws As Worksheet, ps As Worksheet
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Master StyleColor")
Set ps = wb.Sheets("StyleColor 1 Order by Size")
wb.Unprotect "3581479"
ps.Unprotect "3581479"
ws.Visible = True
ws.Range("H35:AB448").Copy
ps.Activate
ps.Range("H35").PasteSpecial xlPasteFormulas
ws.Visible = False
ps.Activate
ps.Range("E36").Activate
wb.Protect "3581479"
ps.Protect "3581479"
[/vba]

Thanks again. I've learned so much already (love the 'Set' feature!).

I would suggest you always set references to your workbooks and sheets. The context sensitive menu's make life so much easier(the menu that appears when you type ws. ). It also keeps things less confusing for you.

[abba92]

It was "PasteSpecial method of Range class failed."

[CBrine]

Got that.

I actually had

xlpasteformula

Should be

xlpasteformulas

I posted the revised code above.
Cal

[abba92]

Cal -

THANK YOU THANK YOU THANK YOU! That worked! Oh my gosh, that's AWSOME!

Thanks!

Jim