Issue when Importing Data from one Workbook to another *VBA*

[BayEnder111]

Hi All,

First time user of this forum and hoping one of the experts on here will be able to help.

I have an issue whereby I'm trying to copy and paste data from one workbook (Workbook 2 - "Monthly Certification Progress Report.xlsb") to another (Workbook 1 - "Certification Dashboard.xlsm") based on certain criteria (If the value is "UK, Ireland" in column 5 of Workbook 2).

I'm using the below VBA Module which is then associated to a Macro in my report which I click to run:

Sub copydatafromfolder()
    Application.ScreenUpdating = False
   
    Dim Directory As String, Filename As String

    WB_1 = "Certification Dashboard.xlsm"
    WB_2 = "Monthly Certification Progress Report.xlsb"
   
    Directory = "C:\Users\Desktop\Dashboard\"
   
    Dim Lastrow As Long, LastColumn As Long
   
    y1 = 7
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If Lastrow > 1 Then
        If MsgBox("Empty 'Master' table?", vbYesNo, "Do Empty?") = vbYes Then
            If Lastrow > 1 Then Worksheets("Create Dashboard").Range("A7:BT" & Lastrow).Delete
        Else
            y1 = Lastrow + 1
        End If
    End If
   
    Workbooks.Open (Directory & WB_2)
        Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        For i = 3 To Lastrow
            If Worksheets("Raw Data").Cells(i, 5) = "UK, Ireland" Then
                Worksheets("Raw Data").Range(Worksheets("Raw Data").Cells(i, 1), Worksheets("Raw Data").Cells(i, 100)).Copy
                    Workbooks(WB_1).Activate
                    Worksheets("Create Dashboard").Cells(y1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                        y1 = y1 + 1
                    Workbooks(WB_2).Activate
            End If
        Next i
        Workbooks(WB_2).Close savechanges:=False
    ActiveWindow.ScrollRow = 1
    Worksheets("Create Dashboard").Range("A7").Select
End Sub

Now this code partially works. It copies and pastes some of the entries across however not all of them (Which is the issue). I'm expecting to have 130 entries copy and pasted over however the above stops at 78 records for some reason. I've checked the data and there's definitely no issues with that as I wrote the below which works, albeit a lot slower which is why I'm trying to get the above to work.

Sub Import_Data2()

Dim Lastrow As Integer, i As Integer, erow As Integer

ThisWorkbook.Activate
Worksheets("Create Dashboard").Select
Range("A7:BT1000").ClearContents 'Clear Previous Contents

'Opens source file (this filename never changes)
Workbooks.Open Filename:="C:\Users\Desktop\PPSM Dashboard\Monthly Certification Progress Report.xlsb", ReadOnly:=True
Workbooks("Certification Progress Report.xlsb").Activate 'Switch to Source workbook
Worksheets("Raw Data").Select

Lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To Lastrow

If Cells(i, 5) = "UK, Ireland" Then
Range(Cells(i, 1), Cells(i, 100)).Select
Selection.Copy

ThisWorkbook.Activate
Worksheets("Create Dashboard").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
Workbooks("Monthly Certification Progress Report.xlsb").Activate 'Switch to Source workbook
Worksheets("Raw Data").Select

End If
Next i
End Sub

Is anyone able to shed any light on why this isn't copy and pasting the expected 130 records across? Any help greatly appreciated.

Many Thanks.

 

[vletm]

the 1st check:
What is value of 'Lastrow'?
Have You checked that it really is 130?

 Workbooks.Open (Directory & WB_2)
        Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
       For i = 3 To Lastrow

 

[BayEnder111]

Hi vletm,

Thanks for the response. I ran the debugger (I think that was the right thing to do! as I mentioned I'm a bit of a novice but learning on the go). I think your right, it's displaying the LastRow = 1361 when there is 2430 rows that I need it to check through for anything "UK, Ireland".

Is there any reason that it would be stopping sooner than I need it to? 1361 is definitely not the last row in the data I need to loop through.

Thanks

 

[BayEnder111]

I fixed it!

Thanks for your help and my subsequent ability to use the debugger as a result...

1361 rows was the first sheet in WB2 when I actually needed to direct it to the second sheet to get the correct amount of rows to cycle through.

I've changed this bit which now gets the required result:

    Workbooks.Open (Directory & WB_2)
    Worksheets("Raw Data").Select
        Lastrow = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row
        LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        For i = 3 To Lastrow

Thanks very much for pointing me in the right direction.