Macro to merge data from multiple worksheets

[Chubba]

Hello

I'm pretty experienced with Excel (2003) but am a bit of an amateur when it comes to macros and VBA. I have part of an existing macro that someone created for me that I would like to edit:

Application.StatusBar = "Cycling over files..."
  For Each loFile In loFSO.GetFolder(ThisWorkbook.Path).Files
    If LCase(Right(loFile.Path, 3)) = "xls" And loFile.Name <> ThisWorkbook.Name Then
      Set loWorkbook = Workbooks.Open(loFile.Path, ReadOnly:=True)
      For Each loWorksheet In loWorkbook.Worksheets
        If InStr(1, mcsExcludedSheets, loWorksheet.Name, vbTextCompare) = 0 Then 'if not excluded
          Application.StatusBar = "Extracting Data from " & loWorkbook.Name & ": " & loWorksheet.Name
          'Select range to search
          Set loSource = loWorksheet.Range(mcsAlwaysPresentStart)
          loWorksheet.AutoFilterMode = False
          llCount = loWorksheet.Range("A65000").End(xlUp).Row
          For i = 1 To llCount
            If loSource.Text <> "" Then
              If pbAll Or loSource.Offset(0, mclSometimesMissingColumnOffset).Text = "" Then
                loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Copy loTarget
                'loSource.Worksheet.Rows(loSource.Row).Copy loTarget
                'loTarget.Offset(0, 10).Value = "'" & loTarget.Offset(0, 10).Value
                Set loTarget = loTarget.Offset(1, 0)
              End If
            End If
            Set loSource = loSource.Offset(1, 0)
          Next i
        End If
      Next loWorksheet
      loWorkbook.Close False
    End If
  Next loFile

The purpose of this macro is it extracts data from all individual workbooks within the same folder and merge it into one separate/master worksheet. The problem is that when it extracts from one of the workbooks, it contains VLOOKUP formulae and significantly slows down the whole process as a result.

Is there a way I can amend the above code such that rather than extract/copy and paste all data as it is (and therefore formulae as it is) but have it paste values instead?

I hope the above is clear. I would be most grateful for any assistance or guidance offered.

Many thanks

 

[seenfresh]

Looks like this line of your code performs the data copy event... try changing it to the following:

From:

loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Copy loTarget

To:

loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Copy loTarget.PasteSpecial xlPasteValues

Assuming loTarget is an actual range

Make sure you create a back up of your data before experimenting with the code just in case!!!!!

 

[Chubba]

Hello

Many thanks seenfresh for the tip. Unfortunately the suggestion didn't seem to work - the macro couldn't process this particular command after this amendment.

Instead I had a playaround and found that the following seemed to work:

loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Calculate
loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Copy
loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).PasteSpecial (xlPasteValues)
loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Copy loTarget

It seemed that I had to include

loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Calculate

at the beginning otherwise blank cells were being copied before the VLOOKUP formulae calculated (at least this is what I thought was happening).

I know the whole code seems a bit cumbersome now but at least it is working - for now!

Many thanks again seenfresh for pointing out the line I needed to work on.

 

[Chubba]

Hello again

I hope it's ok to post in an old existing thread - apologies in advance if not.

The above solution has been working for some time but for some reason has developed a bug.

The macro would have no problems running on one machine but when another user attempts to run it, it pops up an error message (Runtime error 1004) and when I click on debug it highlights the line in the code:

loSource.Worksheet.Range(loSource.Worksheet.Cells(loSource.Row, 1), loSource.Worksheet.Cells(loSource.Row, llColumns)).Calculate

As far as I can see I've set the same parameters and computer settings on both computers. Any ideas what other variable that would cause one machine to run ok while the other one trips up?

Many thanks