Hi everyone,
I would like to ask for help about the code I found and I tried to modify it but I'm not lucky to make it work.
I have a total of eight (8) workbooks with more than one (1) worksheets. What I'm trying to achieved is copy a certain column if the condition is met.
Here is the code:
Thank you in advance!
I would like to ask for help about the code I found and I tried to modify it but I'm not lucky to make it work.
I have a total of eight (8) workbooks with more than one (1) worksheets. What I'm trying to achieved is copy a certain column if the condition is met.
Here is the code:
VBA Code:
Sub copydata()
Dim lastrow As Integer
Dim i As Integer As Integer
Dim erow As Integer
Dim xlsDestination As Workbook 'Master Workbook
Dim xlsSource As Workbook 'Source Workbook
Dim xlsDestSheet As Worksheet 'Destination Sheet
Dim xlsSourceSheet As Worksheet 'Source Sheet
Dim RefNo, Rev, Desc, DateSub
Application.ScreenUpdating = False
Set xlsDestination = ThisWorkbook
Set xlsDestSheet = xlsDestination.Sheets("Master")
Set xlsSource = Workbooks.Open("C:\Users\User\Desktop\Report\Source1.xlsx") 'I have lots of source workbooks
Set xlsSourceSheet = xlsSource.Sheets("Sheet1") 'The problem here is source file composed of more than 1 sheet in every workbooks
lastrow = xlsDestSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 12 To lastrow 'starting row to copy in Source File
If xlsSourceSheet.Cells(i, 13).Value = "C" And xlsSourceSheet.Cells(i, 17).Value = "LT" Then ' This the condition if condition is met
'change the column numbers to the relevant number (from Source Workbook)
RefNo = xlsSourceSheet.Cells(i, 2).Value
Rev = xlsSourceSheet.Cells(i, 3).Value
Desc = xlsSourceSheet.Cells(i, 5).Value
DateSub = xlsSourceSheet.Cells(i, 10).Value
erow = xlsDestSheet.Cells(xlsDestSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'change the column numbers to the relevant number (to Master Workbook)
xlsDestSheet.Cells(erow, 4).Value = RefNo
xlsDestSheet.Cells(erow, 5).Value = Rev
xlsDestSheet.Cells(erow, 6).Value = Desc
xlsDestSheet.Cells(erow, 7).Value = DateSub
End If
Next i
Application.ScreenUpdating = True
End Sub
Thank you in advance!