VBA Excel 2016 throws Object Required Error during run-time

Question

What I am trying to do:

1. I have three excels - Master.xlsx (my source data), DiscrepencyReport (the one my macro will create and populate calculated values from Master.xlsx) and the ReportRunner (my active worksheet where I will give some input values and run the macro)
2. I want to programmatically open the Master.xlsx and Create a new file called DiscepencyReport with the date range in filename.
3. Copy certain values from source, manipulate it and then paste it to the target sheet.
4. Close all workbooks.

What help I need:

1. First, it give me a run-time error saying Object Required. The error is on this line "InputFileName = This.Workbook.Path & "\" & "Master.xlsx"". I googled and read previous errors and tried using / removing the set keyword yet it does not work. I am missing my basics here. No error when I compile.
2. Is this the correct way to approach this problem? or is there a better way?
3. There is a part where I am "Constructing the Report Sheet". I would like some guidance on the syntax on how to copy the column A from worksheet called as "Base" of Master.xlsx to the Target workbook's sheet1's A1 column. The below code does only copying and does not paste.

The code I have is given below:

Sub ReportGeneration()

'Declarations

Dim InputSheet As Workbook, AttendanceDiscrepencyReporter As Workbook
Dim Start As String
Dim Last As String
Dim InputFileName As String
Dim ADRFileName As String
Dim TWorkingDays As Integer
Dim EmpEmail As Range
Dim EmpID As Range
Dim TLeave As Range
Dim TFlexi As Range
Dim TAttendance As Range
Dim IsAtOnsite As Range
Dim CMEmail As Range

'Open Input Workbook

InputFileName = This.Workbook.Path & "\" & "Master.xlsx"
Set InputSheet = Workbooks.Open(InputFileName, True, True)

'Create the Report Workbook

Start = Replace((Range("F7").Value), "/", "_")
Last = Replace((Range("F8").Value), "/", "_")
TWorkingDays = Range("F10").Value

Set AttendanceDiscrepencyReporter = Workbooks.Add
ADRFileName = ThisWorkbook.Path & "\" & "DiscrepencyReport_from_" & Start & "_to_" & Last & ".xlsx"

With AttendanceDiscrepencyReporter
    .Title = "Discrepency Report"
    .Subject = "Discrepency Report"
    .SaveAs Filename:=ADRFileName
End With

' Construct the Report Worksheet

Set EmpEmail = InputSheet.Worksheets("Base").Columns("A")
Set EmpID = InputSheet.Worksheets("Base").Columns("B")

' Close Workbooks

InpuSheet.Close
AttendanceDiscrepencyReporter.Close

End Sub

Answer 1

Without forced explicit variable declaration using Option Explicit at the top of each module VBA will, at compile time, assign a variant to any undeclared variable names. Any code that VBA can't resolve as an object, method or property will become a variant type variable named whatever the unrecognized code string is.

• UnrecognisedCodeString.VarType = vbEmpty applies until an object or property value is assigned to it

• UnrecognisedCodeString.Value = vbNull.

  If you try to do something with the unassigned variable its not going to work:

• InputFileName = This.Workbook.Path & "\" & "Master.xlsx" evaluates to

• InputFileName = vbNull.Workbook.Path & "\" & "Master.xlsx" and then

• InputFileName = vbNull Which causes an error because vbNull isn't a string.

Good news is you can set the VBA IDE to always require Option Explicit by going to Tools -> Options... -> Editor and filling the Require Variable Declaration checkbox.

Unfortunately it isn't retrospective on previously created modules - only new ones.

Answer 2

1. Use Option Explicit - read here: https://www.excel-easy.com/vba/examples/option-explicit.html

2. Using Option Explicit will remove spelling error problems (turn on Option Explicit and use it. Always.)

3. How to copy column in VBA:

   'Range.Copy to other workbooks Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _ Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")

Read more here: How can I copy columns from one sheet to another with VBA in Excel? and here: https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/

Here is corrected cod:

Sub ReportGeneration()

'Declarations

Dim InputSheet As Workbook, AttendanceDiscrepencyReporter As Workbook
Dim Start As String
Dim Last As String
Dim InputFileName As String
Dim ADRFileName As String
Dim TWorkingDays As Integer
Dim EmpEmail As Range
Dim EmpID As Range
Dim TLeave As Range
Dim TFlexi As Range
Dim TAttendance As Range
Dim IsAtOnsite As Range
Dim CMEmail As Range

'Open Input Workbook

InputFileName = ThisWorkbook.Path & "\" & "Master.xlsx"
Set InputSheet = Workbooks.Open(InputFileName, True, True)

'Create the Report Workbook

Start = Replace((Range("F7").Value), "/", "_")
Last = Replace((Range("F8").Value), "/", "_")
TWorkingDays = Range("F10").Value

Set AttendanceDiscrepencyReporter = Workbooks.Add
ADRFileName = ThisWorkbook.Path & "\" & "DiscrepencyReport_from_" & Start & "_to_" & Last & ".xlsx"

With AttendanceDiscrepencyReporter
    .Title = "Discrepency Report"
    .Subject = "Discrepency Report"
    .SaveAs Filename:=ADRFileName
End With

' Construct the Report Worksheet

Set EmpEmail = InputSheet.Worksheets("Base").Columns("A")
Set EmpID = InputSheet.Worksheets("Base").Columns("B")

' Close Workbooks

InputSheet.Close
AttendanceDiscrepencyReporter.Close

End Sub

I have corrected spelling error in This.Workbook part and InpuSheet.Close.