Guidance for a new VBA user

[Scottie]

Hello All, I am a new VBA user. I am attempting to copy rows of information from a folder of files into an excel sheet. My criteria for the rows to be copied is "rowrow". If you refer to the files that I uploaded.I would want to copy those rows with criteria:"rowrow" in column B into a master sheet so as to speed up the process of my work. For simulation sake, there is only two excel files that I am uploading. Many thanks!

Regards,
Scottie

 

[Scottie]

Hello All, I am sorry, I guess this is quite a tall order. I have a vba code that I learnt from a thread in this forum, I did some changes though, but the problem with this code is that it is unable to identify all the rows ta include my condition"rowrow", this code Is only able to identify the first row that includes the condition "rowrow" ad copy it into my mastersheet.

 

[Nebu]

Hi:

I have made the following changes to your code, not tested as your workbook was blank.

Sub GetData()
    Const sPath = "C:\Users\scott.sy.taw\Desktop\Scottie VBA part 2\" ' Change to suit ; ensure the path ends with a backslash
   
    Dim sFil As String
    Dim ws As Worksheet
    Dim rowie As Integer
    Dim wb As Workbook
    Dim colcol As Integer
    Dim lastrow As Integer
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'The sheet name you want to consolidate to

    Application.ScreenUpdating = False
   
    sFil = Dir(sPath & "*.xlsx*")
    Do While sFil <> ""
        Set wb = Workbooks.Open(sPath & sFil)
        wb.Worksheets("Sheet1").Activate
        With ActiveSheet
          lastrow = ActiveSheet.Cells.Find("rowrow", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row
            'rowie = .Range("B1:B500").Find("rowrow").row
            .Range(.Cells(lastrow, 1), .Cells(lastrow, 100)).Copy ws.Range("Iv1").End(xlToLeft).Offset(, 1)
           
        End With
        wb.Close 0
        sFil = Dir
    Loop
   
Range("A1:R1").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

   
    Application.ScreenUpdating = True
Sheets("Sheet1").Range("A3:A200").Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

Thanks

 

[Scottie]

Hi:

I have made the following changes to your code, not tested as your workbook was blank.

Sub GetData()
    Const sPath = "C:\Users\scott.sy.taw\Desktop\Scottie VBA part 2\" ' Change to suit ; ensure the path ends with a backslash
  
    Dim sFil As String
    Dim ws As Worksheet
    Dim rowie As Integer
    Dim wb As Workbook
    Dim colcol As Integer
    Dim lastrow As Integer
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'The sheet name you want to consolidate to

    Application.ScreenUpdating = False
  
    sFil = Dir(sPath & "*.xlsx*")
    Do While sFil <> ""
        Set wb = Workbooks.Open(sPath & sFil)
        wb.Worksheets("Sheet1").Activate
        With ActiveSheet
          lastrow = ActiveSheet.Cells.Find("rowrow", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row
            'rowie = .Range("B1:B500").Find("rowrow").row
            .Range(.Cells(lastrow, 1), .Cells(lastrow, 100)).Copy ws.Range("Iv1").End(xlToLeft).Offset(, 1)
          
        End With
        wb.Close 0
        sFil = Dir
    Loop
  
Range("A1:R1").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

  
    Application.ScreenUpdating = True
Sheets("Sheet1").Range("A3:A200").Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

Thanks

Thank you so much for your reply. I tried out the code but it is only able to capture the last row. I will include the data files that I want to extract the info in this post so that you can try out. The two data files will have the rows that I want to extract, which is the rows with rowrow in column B. I need all the rows with rowrow in column B to be extracted and copied to a mastersheet which is the sheet 1 of the xlsm file. So sorry to bother you due to my inexperience in vba and thank you very muc to taking the time to help me.