VBA - Set Object within If Statement

Adoy005

New Member
Joined
Jul 11, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
So, I created a series of formula on a sheet called Extractor in a workbook called "Processor*". The filename of the workbook "Processor*" will usually vary, having other characters to the right of it. My intention is to copy the range covering all formula I created in a sheet named "Extractor" of the workbook "Processor*", and paste to another workbook "INJ*", with a filename also having variable characters to the right, and specifically to the worksheet named "Table". Upon pasting these formula, it will give results of the different cells i need from "INJ*" based on some conditions I already set in my formula. Please, note that the formula works fine when I do the copy and paste myself. Then I want to copy these results to another sheet on the "Processor*". A sheet called "calculation".
Below is the code I wrote, but I can't seem to get the object defined within the IF statement to work outside the statement. I have several of these files to work with, I will really appreciate your help. Thank you!

Sub ResultExtract()

Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet, lrow As Long, lrow2 As Long, rng As Range

Dim Ct As Long
For Each WB In Application.Workbooks
wb1 = Null
If WB.Name Like "Processor*" Then
Ct = Ct + 1
WB.Activate
Set wb1 = ActiveWorkbook
Set sh1 = wb1.Sheets("Extractor")
Set sh2 = wb1.Sheets("calculation")
Exit For
End If
Next WB
If Ct = 0 Then MsgBox "File not open"


Dim Ct2 As Long
For Each WB In Application.Workbooks
If WB.Name Like "INJ*" Then
Ct2 = Ct2 + 1
WB.Activate
Set wb2 = ActiveWorkbook
Set sh3 = wb2.Sheets("Manager Report")
Set sh4 = wb2.Sheets("TABLE")
Exit For
End If
Next WB
If Ct2 = 0 Then MsgBox "File not open"


With wb1
sh1.Range("C38:J42").Copy wb2.sh4.Range("C38")
End With

With sh4
.Range("C42:J42").Copy
sh2.Range("A" & lrow2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With

End Sub
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
I would remove the wb2 from this line
VBA Code:
sh1.Range("C38:J42").Copy wb2.sh4.Range("C38"):Rem bad

sh1.Range("C38:J42").Copy sh4.Range("C38"):Rem good
wb2 is a workbook object, and sh4 is not a property of a workbook object
sh4 is a worksheet object, that object is in the wb2 Worksheet collection. There is no need to double specify wb2.
 

Adoy005

New Member
Joined
Jul 11, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
You are right. Thank you for the suggestion. Instead of using the 'If' statement, I had to create a function separately, and all I had to do was reference the function in my actual code. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,735
Messages
5,626,587
Members
416,192
Latest member
steinach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top