0

In my VBA code I have a Do While loop which is supposed to copy all (all but first row) data from different excel files with the extension "*.xlsx". All these files contain the same format only with different values (they are export files from my companies PDM software). This needs to be done to create a spare parts list.

When I press run on my script and it's finished it almost always leaves row 2 to 25 empty which is not supposed to happen, its only supposed to leave row 2 empty (also empty rows in between copied data). I need those empty rows to designate which list is which machine and in turn to which machine a spare part belongs.This is what happens and it's wrong. Not only that but it also does not copy everything.

But when I run my loop step by step it DOES work and only leaves an empty row on row 2 and copies all.

Here's what a to-be-imported-list looks like.

I have tried searching for fixes on stack overflow and the internet for hours but could no find anything on this subject.

Do While Len(Filename) > 0
    'Error -> Handler
    On Error GoTo Handler
    Set wbk = Workbooks.Open(Path & Filename)
    ' finding last row so program knows the range it needs to copy.
    LastRowSource = Workbooks(Filename).Sheets("Exported PRO.FILE Data").Cells(Rows.Count, 1).End(xlUp).Row
    If LastRowSource = 1048576 Or LastRowSource = 0 Then
        LastRowSource = 1
    End If
    'copy entire document
    wbk.Sheets("Exported PRO.FILE Data").Range("A2:Z" & LastRowSource + 1).Copy

    ThisWorkbook.Activate
    ' finding last row so program knows the range it needs to copy underneath.
    LastRowMaster = Cells(Rows.Count, 1).End(xlUp).Row
    If LastRowMaster = 1048576 Or LastRowMaster = 0 Then
        LastRowMaster = 1
    End If
    'Paste values
    Sheets("Gathersheet").Range("A" & LastRowMaster + 2).PasteSpecial xlPasteValues

    wbk.Close True
    Filename = Dir
Loop

I expect the output to be a complete list, which is supposed to look like this. I really hope you can help me stack overflow geniusses.

Thank you in advance, Wouter

GSerg
  • 76,472
  • 17
  • 159
  • 346
W.Jansen
  • 5
  • 6
  • 1
    A good place to start the debugging would be [qualifying](https://stackoverflow.com/q/17733541/11683) that `Cells(Rows.Count, 1)` with the sheet object. – GSerg May 03 '19 at 09:25
  • I agree with GSerg, when you are activating ThisWorkbook, you might not have `Gathersheet` as the active sheet... maybe. Set `LastRowMaster = Sheets("Gathersheet").Cells(Rows.Count, 1).End(xlUp).Row`, should probably do the trick. – FAB May 03 '19 at 09:40
  • Thank you GSerg and DarXyde, a simple solution solved my problem. I kinda feel stupid now but still happy :-). – W.Jansen May 03 '19 at 09:49

1 Answers1

0

Answer was short but I needed to set

LastRowMaster = Cells(Rows.Count, 1).End(xlUp).Row

to

LastRowMaster = Sheets("Gathersheet").Cells(Sheets("Gathersheet").Rows.Count, 1).End(xlUp).Row

Defining the active sheet which I had to find the last row of. Thanks Gserg and DarXyde :-)

Damian
  • 5,152
  • 1
  • 10
  • 21
W.Jansen
  • 5
  • 6