I have 3 sample csv files and I want to open/select 2 of the files and copy only the matching data between them into a third Master csv... I have included pictures of the 3 csv files with random data & timestamps. I am new to VBA so my code prolly isn't great but...
1. MasterCSV = the csv i want to copy the data into
2. Target_Time = the csv i want to use to match data (timestamp) against
3. Import_Data = the csv i want to copy the row data from if it matches the timestamp in Target_Time.
1. MasterCSV = the csv i want to copy the data into
2. Target_Time = the csv i want to use to match data (timestamp) against
3. Import_Data = the csv i want to copy the row data from if it matches the timestamp in Target_Time.
- So I want the Macro to allow me to select the Target_Time csv & Import_Data csv - these will be a new csv file for each day (24hr period)
- Then I want to select the rows of the Import_Data csv that have a timestamp (Column A in both csv's) matching with the timestamps of the Target_Time cvs.
- Finally, i want to import ONLY those rows from the Import_Data csv into the MasterCSV.
- I have some code already in my macro to adjust the timestamp cells into a "Date" and "Time" column so the copied rows need to start on column 3 of the MasterCSV.
- Finally I would like to create another macro (to use with another button) that saves the MasterCSV with the "date" of the cells in column A (which should all be the same since it's all data from a 24hr period).
VBA Code:
Sub ImportData()
Dim ImportFile As String
Dim TargetFile As String
Dim MyDateTime As Date
Dim TargetColumn As Range
TargetFile = Application.GetOpenFilename
If TargetFile = "False" Then
Exit Sub
End If
Set TargetWorkbook = Workbooks.Open(filename:=TargetFile)
ImportFile = Application.GetOpenFilename
If ImportFile = "False" Then
Exit Sub
End If
Application.ScreenUpdating = False
'Set the source and target sheets
Set ImportWorkbook = Workbooks.Open(filename:=ImportFile)
Set MasterSheet = ThisWorkbook.Worksheets("Data")
Set TargetColumn = TargetWorkbook.Worksheets(1).Range("A2:A")
'Find the last row in the source sheet
lastRow = ImportWorkbook.Worksheets(1).Cells(ImportWorkbook.Worksheets(1).Rows.Count, "A").End(xlUp).Row
'Find the last row in the Target Sheet
TargetDTRow = TargetWorkbook.Worksheets(1).Cells(TargetWorkbook.Worksheets(1).Rows.Count, "A").End(xlUp).Row
'Find the last row in the master sheet
MasterLastRow = MasterSheet.Cells(MasterSheet.Rows.Count, "A").End(xlUp).Row
'Loop through each row in the source sheet
For i = 2 To lastRow
'Check if cell in column A contains "Target Date/Time"
If ImportWorkbook.Cells(i, "A").Values = TargetColumn Then
'Copy the entire row to the target sheet
ImportWorkbook.Rows("Bi:B").Copy Destination:=MasterSheet.Range("C, MasterLastRow")
MyDateTime = ImportWorkbook.Worksheets(1).Range("A2").Value
'get date
ThisWorkbook.Worksheets(2).Range("A3").Value = Int(MyDateTime)
ThisWorkbook.Worksheets(2).Range("A3").NumberFormat = "YYYY-MM-DD"
'get time
ThisWorkbook.Worksheets(2).Range("B3").Value = MyDateTime - Int(MyDateTime)
ThisWorkbook.Worksheets(2).Range("B3").NumberFormat = "hh:mm:ss"
ImportWorkbook.Close
Application.ScreenUpdating = True
End If
Next i
End Sub
