Copy Worksheet to another Workbook (unopened)

The macro recorder kind of give you a good template. There is usually some work one need to do make it "one size - fit all" type macro

Since you have said that workbook and sheet from where the data is being copied is same
in this case you have to make the workbook name "assembled" at run time. In your macro you need
Instead of

        Workbooks.Open Filename:="C:\filepath\Tester.xlsx" 

this

       Workbooks.Open Filename:="C:\filepath\" & ActiveSheet.Name & ".xlsx"

Here is a little modified version

Sub CopyOpenItems()
   '
   ' CopyOpenItems Macro
   ' Copy open items to sheet.
   '
   ' Keyboard Shortcut: Ctrl+Shift+O
   '
   Dim wbTarget            As Workbook 'workbook where the data is to be pasted
   Dim wbThis              As Workbook 'workbook from where the data is to copied
   Dim strName             As String   'name of the source sheet/ target workbook
   
   'set to the current active workbook (the source book)
   Set wbThis = ActiveWorkbook
   
   'get the active sheetname of the book
   strName = ActiveSheet.Name
   
   'open a workbook that has same name as the sheet name
   Set wbTarget = Workbooks.Open("C:\filepath\" & strName & ".xlsx")
   
   'select cell A1 on the target book
   wbTarget.Range("A1").Select
   
   'clear existing values form target book
   wbTarget.Range("A1:M51").ClearContents

   'activate the source book
   wbThis.Activate
   
   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False
   
   'copy the range from source book
   wbThis.Range("A12:M62").Copy
   
   'paste the data on the target book
   wbTarget.Range("A1").PasteSpecial
   
   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False
   
   'save the target book
   wbTarget.Save
   
   'close the workbook
   wbTarget.Close

   'activate the source book again
   wbThis.Activate
   
   'clear memory
   Set wbTarget = Nothing
   Set wbThis = Nothing
   
End Sub

About the short cut, you have to set the shortcut.
I am presuming you have excel 2007 or higher
So in that case, DEVELOPER TAB might be hidden. Make it visible. do Google search for the your version of excel.
If you go to developer tab, you will see a button that says macro. Click on that. Goto options and select the short cut you want to have

rizvisa1:

Thank you very much for the assistance with this project. I never knew about the macro recorder function and it is awesome! I decided to use an ActiveX Control Button to activate the copy function. But, just to clear up the shortcut key thing above. I had it in place, but it wouldn't execute the macro properly, whereas running the macro from the code screen would be just fine. Anyways, I evolved the project a bit more since our last discussion. It will now read a name and use in the filepath, then it will check to see if the workbook exists. If it doesn't exist, it creates a workbook and then saves it as a particular file name and copies the information to the workbook. If it does exists, then it copies the information to that workbook. And it saves the workbook. The code is below if you want to check it out.

___________________________________________________________________________
Function FileExists(FullFileName As String) As Boolean
' returns TRUE if the file exists
FileExists = Len(Dir(FullFileName)) > 0
End Function

Sub CopyOpenItems()
'
' CopyOpenItems Macro
' Copy open items to dropbox.

Dim FolderName, ProjectManager As String

If Range("D2") = "John" Then
ProjectManager = "John"
ElseIf Range("D2") = "Lorri" Then
ProjectManager = "Lorri"
ElseIf Range("D2") = "Chris" Then
ProjectManager = "Chris"
End If

FolderName = InputBox("What is the name of the folder in dropbox?")

If FileExists("C:\Users\" & ProjectManager & "\filepath\" & FolderName & "\OpenItems.xlsm") = False Then
Workbooks.Add
Range("A1").Select
ActiveWorkbook.SaveAs Filename:="C:\Users\" & ProjectManager & "\filepath\" & FolderName & "\OpenItems.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' Application.Goto Reference:="SaveAs"
Else
Workbooks.Open Filename:="C:\Users\" & ProjectManager & "\filepath\" & FolderName & "\OpenItems.xlsm"
End If

Range("A1").Select
Range("A1:M51").ClearContents
Windows("Projects.xlsm").Activate
' Application.CutCopyMode = False
Range("A12:M62").Select
Selection.Copy
Windows("OpenItems.xlsm").Activate
Range("A1").PasteSpecial
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("Projects.xlsm").Activate

End Sub
_________________________________________________________________________

My next project for this is to take that information that I had copied to the other workbook, and copy it to a master sheet within the original workbook, if certain criteria are met.

If this thread can be marked as solved, then I would appreciate it.

Thank you very much,
Josh