CODEWITHSUNDEEP
vbaexcel
Dinesh Goel
Dinesh Goel

Reputation: 975

copy a worksheet into another workbook using macro

With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2002-03", "*.xls", 1
    .Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
    .AllowMultiSelect = False
    .Show



    If .SelectedItems.Count > 0 Then
        Workbooks.Open .SelectedItems(1)
        Set wkbSourceBook = ActiveWorkbook
        Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)
        wkbCrntWorkBook.Activate
        Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
        rngSourceRange.Copy rngDestination
        rngDestination.CurrentRegion.EntireColumn.AutoFit
        wkbSourceBook.Close False
    End If

End With

I am creating a dialog box to take the file name as input and then need to copy a complete sheet to another workbook instead of selecting the range and stuff. how to do that

Upvotes: 1

Views: 3584

Answers (1)

Nick Perkins
Nick Perkins

Reputation: 1327

You can use the Copy method of the Worksheet object.

wkbSourceBook.Sheets(1).Copy After:=wkbCrntWorkBook.Worksheets(1)

You could choose whichever sheet you wanted by name. It will copy with the existing name, and if it already exists will become "Name (2)". You can then rename it from there.

Upvotes: 1

Related Questions