Copying 'Current' Sheet to a Separate Workbook

[KhanofTarkir]

Hi everyone,

My VBA is rather basic - I was wondering if there is a way to program a macro to copy the currently selected sheet (whatever sheet you are on) and paste it into a separate workbook?

I came up with this code to paste sheet A from the current workbook (workbook 1) to workbook 2, but I don't think it works with sheet B (if you execute the macro while on sheet B, it just takes sheet A and copies it to workbook 2). I am trying to create a flexible macro that allows me to just copy the current sheet from the current workbook (not necessarily workbook 1) and paste it into another open workbook (not necessarily workbook 2).

Sub PasteSheetOtherWB()'

' CopyPasteSheettoOtherWB Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Sheets("Sheet A").Select
    Sheets("Sheet A").Copy Before:=Workbooks("Workbook2").Sheets( _
        1)
End Sub

Any help would be greatly appreciated - thanks!

 

[My Aswer Is This]

Will check back.

 

[My Aswer Is This]

Will this work for you?

ActiveWorkbook.ActiveSheet.Copy Before:=Workbooks("Book2").Sheets(1)

 

[My Aswer Is This]

Or if you want the workbook name to be a variable use this:

Sub PasteSheetOtherWB() '
Dim Answer
On Error GoTo Errhandler
Answer = InputBox("What Workbook do we want to copy this to?")
ActiveWorkbook.ActiveSheet.Copy Before:=Workbooks(Answer).Sheets(1)
On Error GoTo 0
Exit Sub
Errhandler:
      ' If an error occurs, display a message and end the macro.
      MsgBox "No such Workbook exist. The macro will end."
   
End Sub

 

[KhanofTarkir]

Hi My Name is This,

Thanks a lot for the code - it works perfectly. Am I correct in assuming that the macro will copy before the first sheet in the specified destination workbook regardless of what the name of the first sheet is in the destination workbook?

In addition, would it be possible to code in a function that would allow you to browse and search for the workbook you want to copy the current active sheet to? The issue is that many of the destination workbooks I work with have long convoluted file names and it would be a tremendous help if I would just specify the destination workbook by just browsing and clicking/selecting it and having excel open up the destination workbook and copy the source sheet to it. Is this functionality possible?

Thank you, I really appreciate your help!

 

[My Aswer Is This]

Khan:
The script does not care about the name of the sheet. It's looking at the active sheet. And you do realize both workbooks have to be open. Your request about "a function that would allow you to browse...". I will have to see if I can make such a script. I will get back with you on this.

 

[KhanofTarkir]

Hi My Aswer Is This,

Thank you for the clarification - I did not realize that both workbooks have to be open but looking at the code it makes sense now.

It would be very interesting to see if there is a script that would allow one to browse for the destination workbook - I really appreciate you looking into this.

Thanks!

 

[My Aswer Is This]

Try this:

Sub Openme()
Dim MyFile As String
 MyFile = Application.GetOpenFilename()
  Workbooks.Open (MyFile)
 End Sub

Now the question would be do you want both these scripts combined or kept separate?

 

[KhanofTarkir]

Ah thats great! Yes it would be good to have these scripts both combined - thank you very much

 

[My Aswer Is This]

So how are we going to do this combining?</SPAN>
1. You’re going to manually open the Master file.</SPAN>
2. Run the script.</SPAN>
3. Then you will get an open command to open the file you want.</SPAN>
4. Then what ever file you open we will execute the copy command. You will not have to answer the question what file because we already know what file you just opened.</SPAN>

Or do you have another plan.</SPAN>