VBA: Set Workbook, and Activate Workbook Error

Question

There is a simple task that I cannot figure out. I would like to Copy Value of wb1.ws1 and paste into wb2.ws2

This is what I have tried.

Dim wb1 as Workbook, wb2 as Workbook
Dim ws1 as Worksheet, ws2 as Worksheet

'Open wb1 and ws1 and set
Set wb1 = Workbooks.Open("R:\Workbook1.xlsx")
Set ws1 = Sheet("Sheet1")

'Open wb2 and ws2 and set
Set wb2 = Workbooks.Open("R:\Workbook2.xlsx")
Set ws2 = Sheet("Sheet2")

'Copy Value of wb1.ws1 and paste into wb2.ws2
wb1.ws1.Range("A1").Copy wb2.ws2.Range("A1")

Why does this not work? It seems like I cannot select a range by specifying wb1.ws1.Range(XYZ)

It forces me to separate the lines into

wb1.Activate
ws1.Select
Range("A1").Copy
wb2.Activate
ws2.Select
Range("A1").Paste

Seems terribly inefficient but I don't know how to eliminate the Activate-Select nightmare

Scott Craner Scott Craner · Accepted Answer · 2018-09-28T01:21:40

Once you set a worksheet the workbook parent is already defined. so declare the parent at the Set

Dim wb1 as Workbook, wb2 as Workbook
Dim ws1 as Worksheet, ws2 as Worksheet

'Open wb1 and ws1 and set
Set wb1 = Workbooks.Open("R:\Workbook1.xlsx")
Set ws1 = wb1.WorkSheets("Sheet1")

'Open wb2 and ws2 and set
Set wb2 = Workbooks.Open("R:\Workbook2.xlsx")
Set ws2 = wb2.Worksheets("Sheet2")

'Copy Value of wb1.ws1 and paste into wb2.ws2
ws1.Range("A1").Copy ws2.Range("A1")

VBA: Set Workbook, and Activate Workbook Error

1 Answers