Need data consolidation VBA code help

[Sarah Powell]

Trying to create a VBA code that will pick up values from multiple worksheets saved in different locations.


So far I have created a “List” tab with file location, and used this code.


However no values are pulling through. Please can someone help – I am a VBA newbie!



Module 1:


Public strFileName As String

Public currentWB As Workbook

Public dataWB As Workbook

Public strCopyRange As String

Sub GetData()

Dim strWhereToCopy As String, strStartCellColName As String

Dim strListSheet As String

strListSheet = "List"



On Error GoTo ErrH

Sheets(strListSheet).Select

Range("B2").Select



'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet

Set currentWB = ActiveWorkbook

Do While ActiveCell.Value <> ""



strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value

strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)

strWhereToCopy = ActiveCell.Offset(0, 4).Value

strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)



Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True

Set dataWB = ActiveWorkbook



Range(strCopyRange).Select

Selection.Copy



currentWB.Activate

Sheets(strWhereToCopy).Select

lastRow = LastRowInOneColumn(strStartCellColName)

Cells(lastRow + 1, 1).Select



Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone

Application.CutCopyMode = False

dataWB.Close False

Sheets(strListSheet).Select

ActiveCell.Offset(1, 0).Select

Loop

Exit Sub



ErrH:

MsgBox "It seems some file was missing. The data copy operation is not complete."

Exit Sub

End Sub




Public Function LastRowInOneColumn(col)

'Find the last used row in a Column: column A in this example

'http://www.rondebruin.nl/last.htm

Dim lastRow As Long

With ActiveSheet

lastRow = .Cells(.Rows.Count, col).End(xlUp).Row

End With

LastRowInOneColumn = lastRow

End Function

 

[Marc L]

Hi !

Thanks to use code tags or appropriate icon for code !

First remove On Error codeline to try to see where your logic fails …
​

Function ColumnLastRow&(Vcol, Optional Under As Boolean)
         ColumnLastRow = Cells(Rows.Count, Vcol).End(xlUp).Row - Under
End Function

Just in case you really need a function for last row …
​

 

[Monty]

Hello Sarah.

Another way.

https://www.thespreadsheetguru.com/...to-find-the-last-row-or-last-column-using-vba