VBA - Transpose and xlPasteValues from multiple excel files to master

[bubblystace]

Hi All,

Basically I am trying to copy hundreds of new files weekly into one Master.
Each file (see https://ufile.io/bh1z1) has a "GPI" sheet with data in D4:D300, which I need to transpose and paste as values into a Master.

I have hit some roadblocks with my current code. It is running but only giving me a filename and no data.

Thanks a lot for any help!

Sub CopyAll()


FileType = "*.xlsx*"
FilePath = "G:\Statewide\Condition Assessment\GEH INSPECTIONS\2017-18 Condition Assessment\CQ\GPI ALCA"


Dim OutputCol As Variant
Dim Curr_File As Variant
Dim FldrWkbk As Workbook


OutputCol = 1


ThisWorkbook.ActiveSheet.Range(Cells(1, OutputCol), Cells(1, OutputCol)) = FilePath & FileType
OutputCol = OutputCol + 1


Curr_File = Dir(FilePath & FileType)


Do Until Curr_File = ""
Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True)
Sheets("GPI").Range("D4:D300").Copy


Workbooks("GPI Master.xlsm").Activate
Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
ActiveSheet.Paste
OutputCol = OutputCol + 1


FldrWkbk.Close SaveChanges:=False
Curr_File = Dir
Loop
Set FldrWkbk = Nothing
End Sub

 

[Fluff]

Hi & welcome to the board.
Assuming that GPI ALCA is a folder name, add an \ to the end of your filepath.
You might also want to remove this line

ActiveSheet.Paste