Macro to delete all data before pasting in new stuff

[jontyoc]

Hi All

I am using Excel 2007 and have a simple enough macro to paste data in to another sheet - simple is what I do best.

Sub Macro1()
'
' Macro1 Macro
With Sheets("data")
.Range("B3").PasteSpecial xlPasteAll

End With
End Sub


I am wanting to change this so that before it pastes any data into B3 it will delete all data from B3:AE22.
I have had a look around and found plenty to help me delete full rows or worksheets but not just to delete the data in those cells.
Hope this makes sense.

Thanks

 

[Andrew Poulsom]

Clearing the contents of or deleting a range on the worksheet will clear the clipboard, so PasteSpecial will fail, I'm afraid.

 

[jontyoc]

darn n blast. of course it does!
is there no work around other than manually doing before I PasteSpecial?

 

[Andrew Poulsom]

Doing it manually would also clear the clipboard.

 

[jontyoc]

I currently do it this way without even thinking about it:
1 - delete the data in spreadsheet
2 - copy the data to be pasted
3 - hit my macro button and paste it.

So since I have feeling my team leaders wont always follow this by email, if I was to have a step by step instructions:
1. Press this button (macro to delete)
2. Copy data from source
3. Press this button (macro to paste)

Then it should do. I think asking them to press the two buttons may be fine and even the laziest will follow (hopefully!), whereas asking them to find and then delete the range from 8 different worksheets may not be.

 

[jsotola]

try this

select the data, then hit button

[COLOR=#333333]Sub Macro1()[/COLOR]
[COLOR=#333333]
     Sheets("data").Cells.Clear
     Selection.Copy
     Sheets("data")[/COLOR][COLOR=#333333].Range("B3").PasteSpecial xlPasteAll[/COLOR]
     Application.CutCopyMode = False



[COLOR=#333333]End Sub[/COLOR]

 

[Rick Rothstein]

try this

select the data, then hit button

[COLOR=#333333]Sub Macro1()[/COLOR]
[COLOR=#333333]     Sheets("data").Cells.Clear
     Selection.Copy
     Sheets("data")[/COLOR][COLOR=#333333].Range("B3").PasteSpecial xlPasteAll[/COLOR]
     Application.CutCopyMode = False
[COLOR=#333333]End Sub[/COLOR]

This should also work (note clear only the cells the OP mentioned in Message #1)...

Sub Macro1()
     Sheets("data").Range("B3:AE22").Clear
     Selection.Copy Sheets("data").Range("B3")
End Sub

 

[jsotola]

my bad

 

[jontyoc]

Thanks people. Rick that works a treat.