|
Subject:
|
How to avoid printing "1" after Sendkeys
|
|
Posted By:
|
font45
|
Post Date:
|
8/18/2006 7:51:47 AM
|
Hi,
I have an Excel2003 VBA program that selects and displays columns from another sheet which contains the whole database. The following code snippet sums the selected columns, but always leaves a 1 in the last selected cell. Is there a way to avoid this?
'sum columns F,K, L
'Define the range
Set rng = Range("F2:F" & r)
'Go to cell that should contain the sum
Sheet4.Cells(r + 2, 6).Select
'Calculate the sum & make addresses not absolute so can copy for each column
ActiveCell.Formula = "=SUM(" & rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
'Use copy method
ActiveCell.Copy
Sheet4.Cells(r + 2, 9).Select
Sheet4.Paste
Sheet4.Cells(r + 2, 11).Select
Sheet4.Paste
Sheet4.Cells(r + 2, 12).Select
Sheet4.Paste
Sheet4.Cells(r + 3, 13).Select 'unused cell
'Get rid of active copy activity
Application.SendKeys (xlEscKey)
'Rename the sheet tab
Sheet4.Name = ComboBox3.Text
'Autofit columns
Sheet4.Columns("A:M").AutoFit
The reason for the sendkeys line is to turn off the annoying activity around the original "copy from" cell. Otherwise the user must hit the "Esc" key. But now a "1" appears in the last selected cell. Any ideas? I know I can do the sums by "brute force" with loops, but think there is probably a way to get around the "1" problem.
Thanks,
Font.
|
|
Reply By:
|
KALOR55
|
Reply Date:
|
8/18/2006 3:07:38 PM
|
Change Application.SendKeys (xlEscKey) on Application.CutCopyMode = False
|
|
Reply By:
|
font45
|
Reply Date:
|
8/18/2006 5:21:30 PM
|
Thanks so much. It worked great!
You saved me a lot of searching.
Font.
|
|
