Extrat data from file & paste into a particular tab in another file

[Ghoti_ca]

Hi,

I have a folder that contains multiple files that I want to merge into a master file which has a tab for each file in the folder.

Step-by-Step

1. Open first file in the folder
2. Copy the contents of the file
3. Close the file
4. Search the master file for the tab that has the file name
5. Paste data in that tab starting from cell B1
6. Repeat for all files in the folder



I am not sure if the code for finding the proper tab/pasting in the data is correct... Can anyone help me out?




So far this is my code:

Sub ImportCommercialData()


Dim FolderPath As String
Dim Filpath As String
Dim Filename As String

'Defines the path to the folder
FolderPath = "C:\Users\Desktop\Internal Accounts\Commercial\"

'Defines the path to the file
Filepath = FolderPath & ".xlsx"

'Stores the name of the file
Filename = Dir(Filepath)

Dim LastRow As Long
Dim LastColumn As Long

'Do While there is a file name
Do While Filename <> ""
Workbooks.Open (FikderPath & Filename)

'Finds last row/column in the opened file
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Copies the data range
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Copy

'Disables alerts
Application.DisplayAlerts = False
'Closes the workbook
Avtiveworkbook.Close

'Searches for the proper tab to paste the data in
Dim wkst As Worksheets

For Each wkst In ThisWorkbook.Sheets
If (wkst.Name = Filename) Then
'Paste into workheet starting from cell B1. I know this doesn't work..
Activeworksheet.Paste

Filename = Dir
End If


Next

Loop

Application.DisplayAlerts = True


End Sub