PDA

View Full Version : Solved: Copy/Paste within a Macro



abba92
12-20-2006, 01:17 PM
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:

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

Thanks for any help! :bug:

CBrine
12-20-2006, 01:56 PM
I would suggest some major changes. Just the difference between recording and building macro's.

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


HTH
Cal

abba92
12-20-2006, 02:08 PM
Thanks, Cal! The only thing it doesn't like is this line:

ps.Range("H35").PasteSpecial Paste:=xlpasteFormula

Thanks again!

CBrine
12-20-2006, 02:45 PM
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
12-20-2006, 02:48 PM
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
12-20-2006, 02:52 PM
What was the error your recieved?

CBrine
12-20-2006, 02:55 PM
Nevermind, I was able to recreate the error on my system. This should fix it up.


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"




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
12-20-2006, 02:58 PM
It was "PasteSpecial method of Range class failed."

CBrine
12-20-2006, 02:59 PM
Got that.

I actually had

xlpasteformula

Should be

xlpasteformulas

I posted the revised code above.
Cal

abba92
12-20-2006, 03:20 PM
Cal -

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

Thanks!

Jim