Sendkeys Alt-Tab

[willard8]

Below is the code I've put together. I seem to be getting hung up on the Sendkeys portion. What I'm trying to accomplish is...

I have one excel sheet that generates my report: "Test Invoice Report". Using the data generated from the report I would like my macro to allow the user to enter a Billing Date which would become part of the filename for the newly created sheet. The file name would then become "Test Billing Report 07 01 09". I would like to switch back to the report and copy rows 10 and down which contain data and paste them in to this newly created sheet. The sheet's name will always be different and there for I was trying to use the ALT-Tab method to get around that. Please let me know if I need to clarify anyting.

If anyone has a suggestion or better way to accomplish this it would be greatly appreciated.

Thanks in advance for the help!

Sub Save()
'
'
Dim Filename As String

Workbooks.Add

ChDir "S:\Test"

Filename = InputBox("Please input billing date:", "Filename")
ActiveWorkbook.SaveAs Filename:="S:\Test\Test Billing Report " & Filename, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
'
'
Windows("Test Invoice Report.xls").Activate

Rows("10:10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.SendKeys "%{TAB}"

Range("A1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
'
'
End Sub

Will Lewis

 

[GTO]

Greetings,

It appears that you want to find the last row that has data in any column, and copy the entire rows from 10 to that "last row". Is that correct?

Also, you mention "will always be a different sheet...". Do you mean the sheet getting data copied from it (the source sheet) may be one of many in the source workbook (the book w/the code)?

Mark

 

[willard8]

Hi Mark,

The source book will remain the same, but the workbook the data will be copied to will always be different based on the billing date. This report will only be run once per week so each week the destination workbook file name would look like: "Test Billing Report 07 01 09" the next weeks would look like "Test Billing Report 07 08 09"...etc. Hope this helps to clarify.

"It appears that you want to find the last row that has data in any column, and copy the entire rows from 10 to that "last row". Is that correct?" Yes that is correct.

Thanks for the response.

Will Lewis

 

[GTO]

I wasn't quite sure if the same sheet in the source wb was always to be used, so used active sheet; change to suit.

Not tested, but In a Standard Module:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> SaveData()<br><SPAN style="color:#00007F">Dim</SPAN> strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> wbNew <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> rngLRow <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rngCopy <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#007F00">'ChDir "S:\Test"</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Get the user's input for filename...//</SPAN><br>    strFileName = InputBox("Please input billing date:", "Filename")<br>    <SPAN style="color:#007F00">'// ...if cancelled or left blank, we bail now//</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> strFileName = vbNullString <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Change to suit if source sheet will be the same//</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> sht = ThisWorkbook.ActiveSheet<br>    <br>    <SPAN style="color:#00007F">With</SPAN> sht<br>        <SPAN style="color:#007F00">'// Find the lowermost cell with data.//</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rngLRow = .Cells.Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious)<br>        <br>        <SPAN style="color:#007F00">'// Check to make sure there was data found (and a reference set)...//</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngLRow <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'// ...and that its at least at row 11 or farther down.//</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngLRow.Row < 11 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">'// Then we can add the new wb, save it and copy the data over//</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> wbNew = Workbooks.Add(xlWorksheet)<br>                wbNew.SaveAs Filename:="S:\Test\Test Billing Report " & Filename<br>                    <br>                <SPAN style="color:#00007F">Set</SPAN> rngCopy = .Rows("10:" & rngLRow.Row)<br>                rngCopy.Copy wbNew.Sheets(1).Range("A1")<br>            <SPAN style="color:#00007F">Else</SPAN><br>                <SPAN style="color:#00007F">GoTo</SPAN> MyExit<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">GoTo</SPAN> MyExit<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>MyExit:<br>    MsgBox "Nothing to copy"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope this helps,

Mark

 

[willard8]

I did need to make one small change:

wbNew.SaveAs Filename:="S:\Test\Test Billing Report " & Filename

to

wbNew.SaveAs Filename:="S:\Test\Test Billing Report " & strFilename

in case anyone else may need to use this. Other than that works perfectly, I really appreciate the help Mark!

Will Lewis

 

[GTO]

You bet and thanks for catching the glitch. I hope the commenting in the code was helpful. In general, you would like to stay away from activating or selecting, but rather, set stuff to the proper type of object whenever possible. Much more control over what actually happens that way; and sendkeys can be spotty. (not saying I've never 'crutched' w/it).

Have a great day,

mark