Hi,

I want to import a sheet from another workbook into the current workbook, create a sheet in the current workbook and copy/paste some columns from the imported sheet into the created sheet of the current workbook. Then, use a vlookup formula to run on the created sheet (with the pasted data) and transfer to another sheet (OUT_Main) in the current workbook. After vlookup, delete the imported sheet.

I also have a macro button in the current workbook that runs before this one that needs to be built. I would like to combine the 2 macros into 1.

Also, how can you record a macro so it autofills down a formula with a dynamic range?

Here is the code for the first macro that runs. I am essentially trying to do the same thing that this first one does but with a different worksheet. Since I am new to VBA, I don't know how. Thanks for your help!

Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim ws As Worksheet


Cells.Clear

'This part combines all the files in the path in our main file.
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = ActiveWorkbook.Path & "/Raw searches TM"

FileName = Dir(Path & "/*.csv", vbNormal)

Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "/" & FileName)
For Each ws In Wkb.Worksheets
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next ws
Wkb.Close False
FileName = Dir()
Loop

'This second part copies all the sheets of our main file in the main page! Be careful: the Main sheet has to be called "Main"

Dim mainSh As Worksheet
Dim LastR As Integer, copyC As Range, ur As Integer, counter As Integer, checkMonth As String, firstMonth As String, compareMonth As Integer, errorMonth As Variant
counter = 0

Set mainSh = Sheets("OUT_Main")

For Each ws In Worksheets
counter = counter + 1


With ws

If .Name <> "Main" And counter > 8 Then
checkMonth = .Range("E1")
compareMonth = StrComp(firstMonth, checkMonth, vbTextCompare)
LastR = .Cells.Find(What:="*", After:=[a1], searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
If compareMonth <> 0 Then
errorMonth = MsgBox("There is an error (12 months instead of 24) in the searches in the file named as '" & .Name & ".csv'", vbInformation)
'mainSh.Cells.Clear
'Exit For
End If

Set copyC = .Range("a2:bh" & LastR)

copyC.Copy Destination:=mainSh.Range("B" & ur + 2)
ur = ur - 1 + LastR

ElseIf .Name <> "Main" And counter = 8 Then
firstMonth = .Range("E1")

LastR = .Cells.Find(What:="*", After:=[a1], searchorder:=xlByRows, SearchDirection:=xlPrevious).Row

Set copyC = .Range("a1:bh" & LastR)

copyC.Copy Destination:=mainSh.Range("B" & ur + 1)
ur = ur - 1 + LastR
End If
End With

Next

'This third part deletes all sheets of our main file except "Main" page & Control Panel page & DB pages

Dim tSh As Worksheet, dbmbtSh As Worksheet, cpSh As Worksheet, dbCountrySh As Worksheet, tempDBSh As Worksheet

Dim sheetsCounter As Integer

sheetsCounter = 0
Set cpSh = Sheets("Control Panel")
Set dbmbtSh = Sheets("CALC_DBMicroBT")
Set sh = Sheets("OUT_Main")
Set dbCountrySh = Sheets("CALC_DBCountry")
Set tempDBSh = Sheets("CALC_TempDBMicroBT")


Application.DisplayAlerts = False
For Each tSh In ThisWorkbook.Worksheets
sheetsCounter = sheetsCounter + 1
If sheetsCounter > 7 Then
tSh.Delete
End If
Next

'this fourth parth matchs the microBTDB with the country we are working on
Dim countryName As String
Dim localCountryName As String
Dim tempCountryName As String
Dim countryNameCL As String
Dim countryIndex As Double
Dim compareCountryName As Double
Dim compareLanguafe As Double
Dim tempLanguage As String
Dim tempLanguage2 As String
Dim numberSpelling As Double
'Dim englishLanguage As String
Dim language As String
Dim tempKeywordDb As String
Dim tempKeywordMain As String
Dim rowNumber As Double
'Dim countRow As Double
'Dim countColumn As Double
Dim rowNumber2 As Double
Dim columnNumber As Double
Dim currentRow As Double
Dim dbmbtSh2 As Worksheet
'Dim mainSh As Worksheet
Dim dbCountrySh2 As Worksheet
'Dim controlPanelSh As Worksheet
Dim errorCountry As Variant
Dim tmName As String
Dim mBTCel As Range
Dim keyword As String
Dim TMNumber As Double
Dim compareKeyword As Integer
Dim tempKeyword As String
Dim tempTMA As String, tempTMB As String, tempTMC As String, tempTMD As String, tempTME As String, tempTMF As String
Dim help As Variant



mainSh.Columns("BB:BI").Delete Shift:=xlToLeft
mainSh.Columns("D:E").Delete Shift:=xlToLeft
mainSh.Columns("B").Delete Shift:=xlToLeft
mainSh.Columns("B").Insert
mainSh.Columns("D").Insert
mainSh.Columns("E").Insert
mainSh.Columns("F").Insert
mainSh.Columns("G").Insert

rowNumber = mainSh.Range("C" & Rows.Count).End(xlUp).Row
TMNumber = dbmbtSh.Range("A" & Rows.Count).End(xlUp).Row
'Inizialization of variables

'Set mainSh = Sheets("Main")
Set dbmbtSh2 = Sheets("CALC_DBAfter")
Set dbCountrySh2 = Sheets("CALC_DBCountryPrep")
'Set controlPanelSh = Sheets("Control Panel")
countRow = 0
countColumn = 0
countryName = cpSh.Range("A8").Value
tmName = cpSh.Range("A11").Value
If countryName = "" Then
errorCountry = MsgBox("Select the country!!", vbInformation)
End If

rowNumber = dbmbtSh.Range("A" & Rows.Count).End(xlUp).Row

'this fifth part deletes all the useless column and adds one for the MicroBT
TMNumber = tempDBSh.Range("A" & Rows.Count).End(xlUp).Row
rowNumber = mainSh.Range("C" & Rows.Count).End(xlUp).Row

For j = 1 To rowNumber
keyword = mainSh.Range("C" & j).Value
For i = 1 To TMNumber
tempKeyword = tempDBSh.Range("B" & i).Value
compareKeyword = StrComp(keyword, tempKeyword, vbTextCompare)
mainSh.Range("A" & j + 1).Value = tmName
If compareKeyword = 0 Then
tempTMA = tempDBSh.Range("A" & i).Value
mainSh.Range("B" & j).Value = tempTMA
tempTMC = tempDBSh.Range("C" & i).Value
mainSh.Range("D" & j).Value = tempTMC
tempTMD = tempDBSh.Range("D" & i).Value
mainSh.Range("E" & j).Value = tempTMD
tempTME = tempDBSh.Range("E" & i).Value
mainSh.Range("F" & j).Value = tempTME
tempTMF = tempDBSh.Range("F" & i).Value
mainSh.Range("G" & j).Value = tempTMF
Exit For
End If
Next i
Next j

For j = 1 To rowNumber
If (mainSh.Range("D" & j).Value = "") Then
mainSh.Range("C" & j).Interior.Color = RGB(177, 160, 199) ' colour the cells without brandtag
End If
Next j

mainSh.Activate

End Sub