I am working in a project, what I have to do is let the user choose multiple excel files and then from every single file run a macro that take the information in some specific cells of the files and put that information in a new excel file, all the information that I need have to be in the same sheet of the new excel file.
Right now I have two macros that works separated: one for choose and open the excel files and another one to run the macro that take the information, but I need to unified these two and make them just one code. If someone can help me I really appreciate it.
First macro:
Code
Sub Consolidate_Data()
Dim wb As Workbook
Dim sh As Worksheet
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Sheet1")
dsh.UsedRange.Clear
Dim File_Name As Variant
File_Name = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", , "Select Excel Files To Consolidate", , True)
Dim i As Integer
Dim lr As Long
For i = LBound(File_Name) To UBound(File_Name)
lr = dsh.Range("A" & Application.Rows.Count).End(xlUp).Row
Set wb = Workbooks.Open(File_Name(i))
Set sh = wb.Sheets(1)
sh.UsedRange.Copy dsh.Range("A" & lr + 1)
wb.Close False
Next i
End Sub
Display More
Second Macro:
Code
Sub ExtraerValores()
Dim i As Long
Dim BuscarHoja As Boolean
On Error Resume Next
BuscarHoja = (Worksheets("ExtractedData").Name <> "")
If BuscarHoja = False Then
Sheets.Add before:=Sheets(1)
End If
ActiveSheet.Name = "ExtractedData"
Sheets("ExtractedData").Activate
Sheets("ExtractedData").Cells.Select
Selection.ClearContents
Sheets("ExtractedData").Range("A1") = "Classification"
Sheets("ExtractedData").Range("B1") = "Inspector"
Sheets("ExtractedData").Range("C1") = "Standard"
Sheets("ExtractedData").Range("D1") = "Requirement"
Sheets("ExtractedData").Range("E1") = "Classification Rilievi"
v = 5
d = 2
l = 11
m = 6
n = 6
r = 6
Do While v < 550
If Worksheets("report stampabile").Range("P" & v) = "OSS" Or "NC" Or "COM" Then
Sheets("ExtractedData").Range("A" & d) = Sheets("report stampabile").Range("P" & v)
Sheets("ExtractedData").Range("B" & d) = Sheets("report stampabile").Range("D" & l)
Sheets("ExtractedData").Range("C" & d) = Sheets("report stampabile").Range("C" & m)
Sheets("ExtractedData").Range("D" & d) = Sheets("report stampabile").Range("G" & n)
If Worksheets(Sheets(r)).Range("P5") = "OSS" Or "NC" Or "COM" Then
Sheets("ExtractedData").Range("E" & d) = Sheets(r).Range("P5")
r = r + 1
If Worksheets("ExtractedData").Range("E" & d) = 0 Then
Sheets("ExtractedData").Range("E" & d) = ""
End If
v = v + 10
d = d + 1
l = l + 10
m = m + 10
n = n + 10
End If
End If
Loop
End Sub
Display More