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!

 

[KhanofTarkir]

Hi My Aswer Is This,

Thanks for the response - I believe I want to execute the following plan:

1) Open the file with the source worksheet that I want to copy to a destination workbook

2) Run the script, which would ask which destination workbook I would like to copy the source sheet to and I will choose the destination workbook by browsing for it

3) After I say which destination workbook I want to copy the worksheet to, the script will execute the copy command to the specified destination workbook and then the script will terminate. At this point there will be a copy of the source worksheet in the destination workbook (as the first sheet after copying).

4) At this point, two workbooks will be open, the workbook containing the source worksheet and then the destination workbook containing a copy of the source worksheet as the first worksheet.

I would like to stress that the user of the script will only need to open the workbook containing the source worksheet that will be copied. Then running the script will open the destination workbook after the user tells the script the location of the destination workbook.

I hope this clarifies things - thanks so much for your help, I really appreciate it!