Paste Special issue with Macro

**pingwin77** · 04-14-2011, 11:17 AM

Sub MMC_Hole()
'Add MMC HOLE to report - 01-07-2009 by Mike Shadick
    
    Sheets("Formulas").Range("B2:K7").Copy
    Sheets("Data30 - Dev").Select
    ActiveCell.Offset(0, 0).Select
    ActiveSheet.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    ActiveCell.Offset(6, 0).Select
End Sub

I am trying to use the above code to copy data from one sheet and paste special/skip blanks on a second sheet. I want all the formatting and formulas, but I want to skip the blank cells so it does not replace the data I have already entered. For some reason, I keep getting an error and the debugger highlights the Activesheet.Pastespecial part of the code. What am I doing wrong here?

**Andy Pope** · 04-14-2011, 11:36 AM

try activecell rather than activesheet

    Activecell.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False

also check the help on PasteSpecial.

**pingwin77** · 04-14-2011, 01:39 PM

So I got the code to skip blanks, the issue I now have is that It only pastes the cells with stuff in them. I have blank cells with formatting (Color, borders) that I need to copy as well. Should I add a separate line of code that pastes formats or can this be done with the same line of code that skips blanks?