Send Key function help

[yasarayhanka]

Hello,
I am trying to do subtotals to ranges without head line
bc of this it is giving this warning message
"microsoft excel can not determine which row in your list or selection contains column labels , which are required for this command"

and I have to hit enter everytime this comes up so my code will keep working

this is the code I am using and I tried putting the sendkey but it does not work

I do not want to turn off the warning messages bc, that is how my code knows to go to the next sheet.

On Error GoTo yasar
For n = 3 To 7
Sheets(n).Select

Range("b1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select

Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True



Do
SendKeys "~", True

Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True


Else
SendKeys "~", True
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True


End If

Loop
Next
yasar:
Resume Next

End Sub

thanks in advance for the help,
Yasar

[Norie]

Yasar

Don't use SendKeys.:)

And I don't see how you are using the warning message to goto another sheet.:confused:

[yasarayhanka]

Yasar

Don't use SendKeys.:)

And I don't see how you are using the warning message to goto another sheet.:confused:


Ok :)
the loop function I use does not stop untill the end of the sheet and than it gives an error mesage when that comes in the on error resume next kicks in and it goes to the next sheet.

so if I do not use the send key how do I stop the warning message, now I have to click "ok" like 100 times so it can go through the sheets I have.

yasar

[Norie]

yasar

Try this.

Application.DisplayAlerts = False
' your code
Application.DisplayAlerts = True
By the way you really don't need all that selecting and you should avoid using Selection and On Error.

What is the purpose of the code?

[yasarayhanka]

yasar

Try this.

Application.DisplayAlerts = False
' your code
Application.DisplayAlerts = True
By the way you really don't need all that selecting and you should avoid using Selection and On Error.

What is the purpose of the code?

The purpose of the code is to subtotal groups of ranges listed beneath each other. what I am doing with the code is:



On Error Goto yasar
For n = 3 To 7
Sheets(n).Select


Range("b1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
'until here Code is going to the right down corner of my first range
ActiveCell.Offset(-1, 0).Select
'the bottom corner of the first range is grand total bc I already have used the subtotal function once on this data, so with this I avoid the grand total at the bottom


Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
'I select the range I need the subtotal of
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True




Do
SendKeys "~", True


Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
'Until here I am trying to go to the bottom right corner of each range I have and offfset one row up


If IsEmpty(ActiveCell.Offset(-1, 0)) Then
'there are some ranges only with one item so I do not need to select up
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
'with this code here I am trying to select one row addition on top of my 'range so the subtotals will not use my first row of data as heading
'this is the point where I get the error


Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True



Else
SendKeys "~", True
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True



End If


Loop
'The code loops all the ranges I might have
Next
'code goes to the next sheet
yasar:
Resume Next


End Sub


I ended up using the code you gave it works fine I had to write a loop until function to stop the error message I get at the end of the sheet when it could not find a range to subtotal,

Thanks for the Help : pray2:

So, what do you advise me to use instead of "Selects"

Yasar