How to Vba macro copy&paste another workbook?

horizon2

New Member
Joined
Mar 3, 2021
Messages
1
This code copies and pastes the worksheet I'm looking at: to selected excel file (include macro run button)

VBA Code:
 Set ww = ThisWorkbook.ActiveSheet 
            Set closedBook = Workbooks.Open(ReturnStr)
    
        ww.Copy After:=closedBook.Sheets(Sheets.Count)
        closedBook.Close SaveChanges:=True


I wanted the macro to work on the pasted worksheet,So I put this variable in the code.

But ThisWorkbook is refers to the original file from which the macro was created.

And I found it. Module was not pasted.

How can I fix it?

I've read how to copy and paste myself,I want it to work automatically.

VBA Code:
Sub worksheet_export()

Dim FDG As FileDialog
Dim Selected As Integer: Dim i As Integer
Dim ReturnStr As String: Dim tempStr As Variant

'SelectAgain:

Set FDG = Application.FileDialog(msoFileDialogFilePicker)

With FDG
    .Title = "select file"
    .Filters.Add "excel file", "*.xls,*.xlsx;*.xlsm;*.xlsb"
    .InitialView = msoFileDialogViewList
    .InitialFileName = "C:\Users\Admin\Google drive\Myfiles"
    .AllowMultiSelect = False
    Selected = .Show
    
    If Selected = 0 Then
        MsgBox "cancel"
        'GoTo SelectAgain:
    ElseIf Selected = -1 Then
        ReturnStr = .SelectedItems(1)
        Application.ScreenUpdating = False

    Set ww = ThisWorkbook.ActiveSheet
    Set closedBook = Workbooks.Open(ReturnStr)

    ww.Copy After:=closedBook.Sheets(Sheets.Count)
    closedBook.Close SaveChanges:=True
    
    End If
    

End With
 
End Sub[CODE=vba]
[/CODE]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

julhs

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
Hope this helps.

If your macro code is in a ‘Module’ in your Active Workbook then that is not copied across to a different Workbook, you will have to do that separately. Or put the code in a Module in your ‘Personal.XLSB’ book so it is available for ‘All Workbooks’

Have you tried; right clicking on the Button that did copy over and then click on “Assign macro”.
You will see all the macros available, highlight the one you use for the Button, then on the right, click ‘Edit’

That should open the ‘Developer tab’, you will see the code for that macro in the main window.
In the list on the left is all your Sheets and Modules etc in that Workbook, one in the list will be highlighted and that is where your code resides.

You can view the code of any Module or Sheet by right click it and then click ‘View code’.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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
Top