Object variable or with block variable not set - Error

Question

I was running a simple VBA code as below:

Sub TransferData()

'transfer stuff from workbook 1 to workbook 2

    Dim strPath1 As String

    Dim strPath2 As String

    Dim wbkWorkbook1 As Workbook

    Dim wbkWorkbook2 As Workbook

    'define paths and filenames

    strPath1 = "C:\blp\data\grid1.xls"

    strPath2 = "Z:\24AM\Risk Managemen\Risk Management Processes.xlsm"

    'copy the values across

    wbkWorkbook2.Worksheets("FXDUMP").Range("A1:Z2000").Value = wbkWorkbook1.Worksheets("Book1").Range("A1:Z2000").Value

    'close the two workbooks

    wbkWorkbook1.Close (False)

    wbkWorkbook2.Close (True)
End Sub

But when I try to run the macro, I got this error message:

Object variable or with block variable not set

Can you please give me an input to figure out this issue?

Thank in advance.

You haven't assigned anything to either workbook variable.

Rory
– Rory

2015-10-27 08:49:32 +00:00
Commented Oct 27, 2015 at 8:49 — Rory
– Rory, Commented Oct 27, 2015 at 8:49
Your workbook variables haven't been assigned...

SierraOscar
– SierraOscar

2015-10-27 08:49:42 +00:00
Commented Oct 27, 2015 at 8:49 — SierraOscar
– SierraOscar, Commented Oct 27, 2015 at 8:49

PASUMPON V N · Accepted Answer · 2015-10-27 09:15:44Z

1

Include the below statements in your code, It will open the excel sheet and assign the variables to excel sheet

Set wbkWorkbook1 = Workbooks.Open(Filename:=strPath1)
Set wbkWorkbook2 = Workbooks.Open(Filename:=strPath2)

edited Oct 27, 2015 at 9:15

answered Oct 27, 2015 at 9:00

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Object variable or with block variable not set - Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related