Do not select sheet and clear contents of copied cells

[Chris Akens]

I have finally done all the coding for my macro and I am just trying
to to tidy it up a little bit. I have two issues...

1) When I run my macro it will copy the data and then switch (select?)
to the sheet it is pasting to. I would like to be done in the
backround. I have played around with AutoUpdate = False, but with no
success. I am just not sure on how to accomplish this.

2) After the copy, I would like excel to automatically clear a range
of cells (not the copied cells, as they are calculations from a
different range). I am presently researching this, but have no idea on
how to do it.

Thank You for all of your help!

Chris


Here is my code....


Sub Player1()

Dim r As Integer
Dim c As Integer
Dim x As Integer


r = Sheets("hidden").Range("a1")
c = 1
x = 5

If IsEmpty(Sheets("ScoreCard").Range("c14")) Then

Else


If IsEmpty(Sheets("Player1").Cells(r, c)) Then

Sheets("ScoreCard").Range("AA21:AA25").Select
Selection.Copy
Sheets("Player1").Select
Cells(r, c).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Sheets("ScoreCard").Activate
Sheets("ScoreCard").Range("AD21:AD25").Select
Selection.Copy
Sheets("Player1").Select
Cells(r, x).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

With Sheets("hidden").Range("A1")
.Value = .Value + 1
If .Value = 31 Then .Value = 1
End With




Else

With Sheets("hidden").Range("A1")
.Value = .Value + 1
If .Value = 31 Then .Value = 1
End With

Sheets("ScoreCard").Range("AA21:AA25").Select
Selection.Copy
Sheets("Player1").Select
Cells(r, c).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Sheets("ScoreCard").Activate
Sheets("ScoreCard").Range("AD21:AD25").Select
Selection.Copy
Sheets("Player1").Select
Cells(r, x).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True



End If
End If

End Sub

 

[Tom Ogilvy]

The easiest fix would be to put

Application.ScreenUpdating = False ' at the top

Application.ScrennUpdating = True ' at the bottom

of your code.

Worksheets("ScoreCard").Range("A1:A10").ClearContents

will clear the contents of A1:A10 on worksheet Scorecard.

 

[Bob Phillips]

Chris,

You can normally do

source.Copy Destination:= target

rather than
source_sheet.select
source_range.select
selection.copy
target_sheet.select
target_range.select
selection.paste

but when using pastespecial you have to have the target sheet active
(AFAIK). What you can do is turn off screenupdating, and switch invisibly,
like

Application.ScreenUpdating = False
Sheets("ScoreCard").Range("AA21:AA25").Copy
Sheets("Player1").Select
Cells(r, c).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
... do some more
Sheets("Scorecard").ACtivate
Application .ScreenUpdating = True

To clear a range, use

Sheets("Scorecard").Range("A1:H100").Clear

Clear clears everytuhng, Clearcontents leaves the format.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

For the range form of Pastespecial, which is shown, you don't have to have
the destination sheet active. (however, on a slower machine, you will see
that Excel activates it and then switches back - at least it does in xl97. )