Change to "SKIP BLANKS" when pasting?

[pingwin77]

I have the following code in a report I use. I need the paste command (highlighted in RED) updated so that it skips blanks when pasting. What changes need to be made?

Thanks!

Private Sub CommandButtonMMC_Click()
'Add MMC to report - 01-07-2009 by Mike Shadick

Sheets("Formulas").Range("C2:K7").Copy
Sheets("Data30 - Dev").Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(6, -1).Select
End Sub

[Simon Lloyd]

I have the following code in a report I use. I need the paste command (highlighted in RED) updated so that it skips blanks when pasting. What changes need to be made?

Thanks!

Private Sub CommandButtonMMC_Click()
'Add MMC to report - 01-07-2009 by Mike Shadick

Sheets("Formulas").Range("C2:K7").Copy
Sheets("Data30 - Dev").Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(6, -1).Select
End SubA couple of questions, do you want to skip blanks in the destination sheet or just eliminate blanks in what you have copied?, you are selecting the offset from the active cell, how can you be sure this will be the correct place, you would be better of specifying where you want it, lastly you do not need all those selects.

Why not record a macro of you performing the action but rather than just paste, right click choose paste special and then check the "Skip Blanks" check box.

[pingwin77]

I just want to skip the blanks in the destination sheet. There may already be some data in the cells that are blank in the copied range.

As for the offset being selected based on the active cell, I use this code a varying amount of times during the making of my report. I need it to go to different location on the destination sheet in different location and a different amount of times depending on my report requirements.

The code with all the "selects" in it is one that i am working on. I inherited this project because I slipped up and mentioned that I know how to use Excel macros. Apparently that is enough to get a position as our company excel guru. I am always up to learning better, cleaner ways to code. If you want to throw in a few tips that is great. I just ask that you explain the difference and why it it better so I can learn from that in the future. How do I do this with out all the "select" commands?

As for the macro, can I do that? I am using form buttons in my report and using the code in the VBA editor. I feel it is much easier to keep organized and make changes. Can I jsut copy the macro code into the VBA editor and assign it to a button? I thought I tried that once and it seemed to have some problems. Probably just opperator error. ;)

I hope this helps and makes sense. Thanks!