VBA - Copy specific values from 400+ workbooks into a table in a masterfile

Doblez

New Member
Joined
Apr 21, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hey,
I'm trying to copy 22 values from 400-something excelfiles and put the values in a specific format in the new sheet. All the values from the first file should end up on row 1 and their respective columns and all the values from file 2 on row 2 and so on. I've tried to base my code off of VBA to copy data from multiple workbooks into master sheet but I can't seem to get it adapted to my use case.
I need multiple if statements within and would like to have the resulting values formated similar to a table starting from a2. This is what I've tested with so far, but it doesn't work and I am unsure how to transpose the values I copy.
I've attached an image of the values I need to copy (starting at h8) and transpose to the other direction.
I hope someone can help - In advance thanks. Please let me know if you need further explanation to help out.

VBA Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "H:\Bruger\DLHL\VSP\Lonstat\Source\"
    ChDir strPath
    strExtension = Dir("*.xlsx*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            If .Sheets("Ark1").Range(Cells(11, 8)) <> "x" Then
            LastRow = .Sheets("Ark1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Ark1").Range("H8:H" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
            Else
            End If
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub


excel
 

Attachments

  • Unavngivet.png
    Unavngivet.png
    18.5 KB · Views: 3

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,132,980
Messages
5,656,203
Members
418,289
Latest member
Kjohno

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top