Copying data from one worksheet to the other VBA

[Neveidas]

Hello, I am having some trouble with these codes, They seem to be working the first time but when i try to import a second time, an error occurs. When i click on debug, the line "rngSrc2.Copy Destination:=rngDst2" is highlighted.

Public Sub Step_3_Combine_Local_XB_Lost()

Application.ScreenUpdating = False

    Dim wkssrc2 As Worksheet, wksDst2 As Worksheet
    Dim rngSrc2 As Range, rngDst2 As Range
    Dim lngLastCol2 As Long, lngSrcLastRow2 As Long, lngDstLastRow2 As Long
    
    If Range("A1").Value = "Shipment" Then
    Range("A:A").EntireColumn.Delete

    End If
    
    'Set references up-front
    Set wksDst2 = ThisWorkbook.Worksheets("Import2")
    lngDstLastRow2 = LastOccupiedRowNum2(wksDst2)
    lngLastCol2 = LastOccupiedColNum2(wksDst2)
    
    'Set the initial destination range
    Set rngDst2 = wksDst2.Cells(lngDstLastRow2 + 1, 1)
    'SET WORKSHEET SOURCE
    

    'Loop through all sheets
    For Each wkssrc2 In ThisWorkbook.Worksheets
    
        'Make sure we skip the "Import2" destination sheet!
        If wkssrc2.Name <> "Import2" And wkssrc2.Name <> "Update(Breach)" And wkssrc2.Name <> "Update XB(Breach)" And wkssrc2.Name <> "Import" Then
            
            'Identify the last occupied row on this sheet
            lngSrcLastRow2 = LastOccupiedRowNum2(wkssrc2)
            
            'Store the source data then copy it to the destination range
        
        
            With wkssrc2
                Set rngSrc2 = .Range(.Cells(2, 1), .Cells(lngSrcLastRow2, lngLastCol2))
                rngSrc2.Copy Destination:=rngDst2
            End With
            
            'Redefine the destination range now that new data has been added
            lngDstLastRow2 = LastOccupiedRowNum2(wksDst2)
            Set rngDst2 = wksDst2.Cells(lngDstLastRow2 + 1, 1)
            
        End If
    
    Next wkssrc2

    Set rngSrc2 = Nothing
    Set rngDst2 = Nothing
    Set wksDst2 = Nothing
    
Application.ScreenUpdating = True

This is the error that occurs.

I have the same exact codes (but with different variables, taking off the 2 at the end of the variables) but they are able to import multiple times without having the error above.
What can I do here? I appreciate any advice and solutions, thank you~

 

[Alex Blakenburg]

when i try to import a second time, an error occurs.

What does import a second time mean ? Do you mean the 2nd time it loops or do you actually mean running the macro again ?
Not sure how running it again would work unless you have brought in new data to the other sheets.

You seem to be running 2 functions which are not included in the code provided being LastOccupiedRowNum2, LastOccupiedColNum2.
If the problem is on the 2nd time it loops then LastOccupiedRowNum2 may be a possible issue.

When it errors out does what you get in the immediate window when you enter the following ? Does it make sense ?

? rngSrc2.address
? rngDst2.address

 

[Neveidas]

What does import a second time mean ? Do you mean the 2nd time it loops or do you actually mean running the macro again ?
Not sure how running it again would work unless you have brought in new data to the other sheets.

You seem to be running 2 functions which are not included in the code provided being LastOccupiedRowNum2, LastOccupiedColNum2.
If the problem is on the 2nd time it loops then LastOccupiedRowNum2 may be a possible issue.

When it errors out does what you get in the immediate window when you enter the following ? Does it make sense ?

? rngSrc2.address
? rngDst2.address

apologies for the super late reply,

yeah it was the macros clashing, but I have worked out an alternative for the error, thank you for replying though

 

[Alex Blakenburg]

No problem, thanks for closing the loop on this. Glad you were able to sort it out.