Sendkeys Alt-Tab

willard8

New Member
Joined
Feb 5, 2009
Messages
34
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,218
Messages
6,177,198
Members
452,764
Latest member
Mark1963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top