Forum Discussion

Rudrabhadra's avatar
Rudrabhadra
Brass Contributor
Jul 01, 2023

VBA

Hi,

Appreciate if anyone could help me in the error in the below code. Thanks in advance

 

Sub Com()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim NewSht As Worksheet
Dim ActBook As Workbook
Dim ActSht As Worksheet
Dim MyFolder As String
Dim StrFilename As String
Dim Counter As Integer


Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Set ActBook = ActiveWorkbook
Set ActSht = ActBook.Worksheets("Merged")

MyFolder = "C:\Combine"
Set wbDst = Workbooks.Add(xlWBATWorksheet)
Set NewSht = wbDst.Worksheets(1)
StrFilename = Dir(MyFolder & "\*.xls*", vbNormal)

 

a = 2

NewSht.Range("A1") = "Structure Code"
NewSht.Range("B1") = "Level Area Code"
NewSht.Range("C1") = "Drawing No."
NewSht.Range("D1") = " Rev. No."
NewSht.Range("E1") = "Equipment/ Cable Tray Tag No."
NewSht.Range("F1") = "Qty "
NewSht.Range("G1") = "Tag Description"
NewSht.Range("H1") = "Eng Trl No"
NewSht.Range("I1") = "Eng Trl Date"
NewSht.Range("J1") = "Pmt Trl No"
NewSht.Range("K1") = "Pmt Trl Date"
NewSht.Range("L1") = "Tag Type Code"
NewSht.Range("M1") = "ERECTION LOCATION"

If Len(StrFilename) = 0 Then Exit Sub

Do Until StrFilename = ""
Set wbsrc=Workbooks.Open(Filename:=MyFolder & "\" & StrFilename, UpdateLinks:=0, ReadOnly:=False)
Set wssrc=wbSrc.Worksheets(1)

For i = 2 To 46
NewSht.Cells(a, "A") = wsSrc.Cells(i, "A")
NewSht.Cells(a, "B") = wsSrc.Cells(i, "B")
NewSht.Cells(a, "C") = wsSrc.Cells(i, "C")
NewSht.Cells(a, "D") = wsSrc.Cells(i, "D")
NewSht.Cells(a, "E") = wsSrc.Cells(i, "E")
NewSht.Cells(a, "F") = wsSrc.Cells(i, "F")
NewSht.Cells(a, "G") = wsSrc.Cells(i, "G")
NewSht.Cells(a, "H") = wsSrc.Cells(i, "H")
NewSht.Cells(a, "I") = wsSrc.Cells(i, "I")
NewSht.Cells(a, "J") = wsSrc.Cells(i, "J")
NewSht.Cells(a, "K") = wsSrc.Cells(i, "K")
NewSht.Cells(a, "L") = wsSrc.Cells(i, "L")
NewSht.Cells(a, "M") = wsSrc.Cells(i, "M")

a = a + 1
Next i

'wbSrc.Save
wbSrc.Close
StrFilename = Dir()
Loop

End Sub

Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 01, 2023

    Rudrabhadra 

    The code provided contains several errors that need to be corrected.

    Here are the identified errors and the corresponding corrections:

    1. Error: The variables wbSrc and wsSrc are not declared.

    Correction: Add the declarations for wbSrc and wsSrc before using them in the code. For example:

    Vba code

    Dim wbSrc As Workbook
Dim wsSrc As Worksheet
    1. Error: The variable names wbDst and NewSht are used interchangeably, but they refer to different objects.

    Correction: Replace all instances of NewSht with wbDst.Worksheets(1) to refer to the correct worksheet object.

    1. Error: The loop counter variable i is not declared.

    Correction: Add the declaration for the loop counter i before using it in the code. For example:

    vba code

    Dim i As Integer
    1. Error: The variables wbDst, wbSrc, wsSrc, ActBook, and ActSht are not being assigned properly.

    Correction: Update the assignments for these variables to reference the correct workbook and worksheet objects. For example:

    vba code

    Set wbDst = Workbooks.Add(xlWBATWorksheet)
Set wbsrc=Workbooks.Open(Filename:=MyFolder & "\" & StrFilename, UpdateLinks:=0, ReadOnly:=False)
Set wssrc=wbSrc.Worksheets(1)
    1. Error: The variables wbSrc and wssrc are used instead of wbSrc and wsSrc.

    Correction: Update the variable names to use the correct casing. Replace all instances of wssrc with wsSrc.

    After making these corrections, the code should run without errors. The text and steps were created with the help of AI.

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources