Copy Worksheet to another Workbook (unopened) [Solved/Closed]

Josh - Apr 2, 2012 at 05:08 PM - Latest reply:  Josh
- Apr 5, 2012 at 03:10 PM
Hello,

I want to copy a portion (A1:F54) of my current worksheet that I am working on, over to another workbook, that is not open. I want to use a button to do this. I am sure VBA is necessary and there would probably need to be a call to open the other workbook. There might also be another obstacle : the workbook that is to be copied to is in a shared online folder.

Any assistance in this issue would be greatly appreciated. Any time that you have taken to read or investigate into this question is greatly appreciated.

Thank you,
Josh

See more 

6 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 2, 2012 at 06:26 PM
0
Thank you
Well you nailed both things. Yes you need a macro and yes the macro would need to open that workbook before it can paste data there

You can get the macro by simply running the macro recorder and record your action of copying data, opening file, pasting data and saving
riz:
Thank you for pointing me in the right direction. But now I am having a bit of a problem with running the macro. For some reason it isn't executing the code properly when I try to run it with the shortcut key, but it will run if I "Step Into" the code, then click the run button while viewing the macro. Here is the code. Any ideas as to why the shortcut doesn't work?
----------------------------------------------------------------------------
Sub CopyOpenItems()
'
' CopyOpenItems Macro
' Copy open items to sheet.
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Workbooks.Open Filename:="C:\filepath\Tester.xlsx"
Range("A1:M51").Select
Selection.ClearContents
Range("A1").Select
Windows("Sheet.xlsm").Activate
Range("A12:M62").Select
Selection.Copy
Windows("Tester.xlsx").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("Sheet.xlsm").Activate
End Sub
-------------------------------------------------------------------------------

Thank you for all the help,
Josh
After a little more testing I have found issue that I need to figure out. The macro is apparently currently configured to run for whatever sheet I am currently in and copies to just the one workbook "Tester.xlsx". I need it to copy the contents of the current sheet to a specific workbook. Ex : Sheet442 will be copied to Workbook442.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 4, 2012 at 06:00 PM
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 4, 2012 at 06:06 PM
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