Copying a worksheet to another workbook using VBA

1
votes

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
2
vbaexcel
Workbook <> String , use Set wbk1 = Workbooks(tWbkName) – cyboashu
More examples here stackoverflow.com/a/34886033/4539709 – 0m3r

2 Answers

4
votes

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
2
votes

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

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