Help on VBA project

[jdhfch]

HI,

I am trying to adapt an existingVBA project to copy 11 worksheets from a folder into one workbook, then update a master template.

In Column A I have “ABM”, then it runs along for 32 more columns, row 1 is the titles which I don’t need to copyover. There is only 1 worksheet per workbook and each sheet is called the same then renamed after being copied over.

I am using this code, but itkeeps failing at IntABMRow = 2 with a “Compile error: Variable not defined”error (please bear in mind this is copied from a different workbook so it may be source related ?


Private SubUpdate_Master_Template()

Dim sh As Worksheet
Dim strMasterFile As String
Dim strMasterSheet As String
Dim strMaster2019 As String
Dim intMasterRow As Integer
Dim intMaster2019Row As Integer
Dim strCurrentABMSheet As String
Dim intABMRowTotal As Integer
Dim Counter As Integer
Dim Counter2 As Integer
Dim Counter3 As Integer
Dim intMasterRowTotal As Integer

strMasterFile = ActiveWorkbook.Name
strMaster2019 = Sheet1.Name


intMaster2019Row = 2


Application.DisplayAlerts = False
For Each sh In Worksheets

If sh.Name <>strMaster2019 Then

intABMRow = 2

strCurrentABMSheet = sh.Name

If Left(strCurrentABMSheet, 1)= "Non" Then
strMasterSheet =strMaster2019
intMasterRow = intMaster2019Row
ElseIfLeft(strCurrentABMSheet, 1) = "2019" Then
strMasterSheet = str2019
intMasterRow =intMaster2019Row
Else
GoTo NextSheet:

End If


intABMRowTotal =Sheets(strCurrentABMSheet).Range("A3").CurrentRegion.Rows.Count - 1

For Counter = 1 TointABMRowTotal

IfSheets(strCurrentABMSheet).Cells(intABMRow, 5) <> "" Then
For Counter2 = 1 To10
Sheets(strMasterSheet).Cells(intMasterRow, Counter2 + 2) =Sheets(strCurrentABMSheet).Cells(intABMRow, Counter2 + 2)
Next Counter2
intABMRow =intABMRow + 1

intMasterRow =intMasterRow + 1

If strMasterSheet = str2019 Then
intMaster2019Row = intMasterRow

End If

End If
Next Counter

End If

NextSheet:

Next sh
Application.DisplayAlerts = True

Please can someone help, I am a complete novice to VBA!

 

[dmt32]

Hi,
what that error means is that at Top of your module you have Option Explicit declared which is good as it requires that you declare all variables used in your code.

Quick glance at your code suggests that following two variables are not declared

Dim intABMRow As Long, str2019 As String

Try adding these in your declarations section of your code & see if problem resolves itself.

Dave

 

[jdhfch]

Thank you. Unfortunatly it still errors

 

[dmt32]

Thank you. Unfortunatly it still errors

Just pasted in to code sheet & compiled ok - where does it error & what is the error code?