Copying a worksheet to another workbook using VBA

Question

I am trying to copy a worksheet from the workbook that the VBA is in (ThisWorkbook) and past it into another workbook that the user has open (ActiveWorkbook). I have written a function but I cannot get it to work. I have done something similar before and I have searched the internet but I cannot find a solution or why it is failing me. What am I doing wrong?

 Function workBooks() As String
    aWbkName = ActiveWorkbook.Name
    tWbkName = ThisWorkbook.Name

    Dim wbk1 As Workbook
    Dim wbk2 As Workbook

    Set wbk1 = tWbkName
    Set wbk2 = aWbkName

    wbk1.Sheet2.Copy After:=wbk2.Sheets(7)

End Function

`Workbook` <> `String` , use `Set wbk1 = Workbooks(tWbkName)` — cyboashu, Apr 06 '17 at 22:20
More examples here http://stackoverflow.com/a/34886033/4539709 — 0m3r, Apr 06 '17 at 22:32

score 4 · Accepted Answer · answered Apr 07 '17 at 03:12

Try this and see if it works. It will copy Sheet2 in ThisWorkbook and paste it after Sheet1 in the ActiveWorkbook

Option Explicit

Public Sub copy_sheet()

    Dim source_worksheet As Worksheet
    Set source_worksheet = ThisWorkbook.Worksheets("Sheet2")

    Dim target_worksheet As Worksheet
    Set target_worksheet = ActiveWorkbook.Worksheets("Sheet1")

    source_worksheet.Copy After:=target_worksheet

End Sub

score 2 · Answer 2 · answered Apr 06 '17 at 22:21

2

you don't need all that variable dimming and assigning:

Sub workBooks()
    ThisWorkbook.Sheet2.Copy After:=ActiveWorkbook.Sheets(7)
End Sub

answered Apr 06 '17 at 22:21

user3598756

28,893
4
18
28

3

I'd appreciate the shy downvoter explain how can I improve my answer. thank you – user3598756 Apr 07 '17 at 12:26

Copying a worksheet to another workbook using VBA

2 Answers2