Macro to open files in a folder, search a column for criteria, and copy those rows to a master sheet

[sgeng4]

Hello =)

I need help making a macro. I have a bunch of files in a folder, and I am trying to organize them. In sheet(2) column B in those files, there is a name. Basically if the name starts with MC / mc / Mc, I want the macro to copy over that name to a master, as well as its value from column sheet(2) column G, a date from wbk.Sheets(1).Range("C7"), a certificate number from wbk.Sheets(1).Range("F7"), and a link to the file.

Below is what I have been messing with, however I'm not very good at VBA. I appreciate any help.

Sub CopyMCsOverToMaster()
   'DECLARE AND SET VARIABLES
   Dim wbk As Workbook
   Dim Sht As Worksheet
   Dim FileName As String
   Dim Path As String
   Dim Cl As Range
   Dim Rng As Range
   Dim Cnt As Long
 
Application.ScreenUpdating = False


   With Application.FileDialog(4)
       .Show
       Path = .SelectedItems(1) & "\"
   End With
    
      FileName = Dir(Path & "*.xls*")
    
       Do While Len(FileName) > 0
         Set wbk = Workbooks.Open(Path & FileName)


'unhide all worksheets
             For Each WS In ActiveWorkbook.Worksheets
             WS.Visible = xlSheetVisible
             Next WS


         Cnt = Cnt + 1
         Set Sht = wbk.Sheets(4)        'Looks for assays in sheet 4
         If wbk.Sheets(4).Range("B2").Value Like "*MC*" Then  ' If value is MC
               .Item(Rng.Value).Offset(, 0).Value = wbk.Sheets(4).Range(, 0).Value      ' adds the MC name to column A
               .Item(Rng.Value).Offset(, 5).Value = Rng.Offset(, 5).Value               'adds the value to the master
               .Item(Rng.Value).Offset(, 10).Value = wbk.Sheets(1).Range("C7").Value   ' adds the date to the master
               .Item(Rng.Value).Offset(, 11).Value = wbk.Sheets(1).Range("F7").Value    'add the certificate to the master
                WS.Range.Offset(a, 2).Value = c.Address
                WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
                Sht.Name & "!" & c.Address, TextToDisplay:="Link"
            End If
         Next Rng
         wbk.Close False
         FileName = Dir
      Loop
   End With
   MsgBox Cnt
End Sub

<looks for="" assays="" in="" sheet="" 4


<looks for="" assays="" in="" sheet="" 4

Thank you very much!</looks></looks>

 

[JLGWhiz]

You should be getting an error on this line:

WS.Hyperlinks.Add Anchor:=WS.Range("D4").Of[COLOR=#ff0000]fset(a, 0)[/COLOR], Address:=myfolder & Value, SubAddress:= _
                Sht.Name & "!" & c.Address, TextToDisplay:="Link"

I don't find where 'a' is initialized with an integer value, or any value, and that should produce either a type mismatch, error 13, or a run time, error 1004, syntax error. Values inside the parentheses for the Offset function have to be integers since they determine the number of cells and direction that VBA must count to execute the offset.

Just noticed this line is same problem.

  WS.Range.[COLOR=#FF0000]Offset(a, 2).[/COLOR]Value = c.Address

 

[sgeng4]

Thank you JLGWhiz,

I was getting the error at

[COLOR=#ff0000].Item[/COLOR](Rng.Value).Offset(, 0).Value = wbk.Sheets(4).Range(, 0).Value      ' adds the MC name to column A

So I updated the code a bit like so:

Sub CopyMCsOverToMaster()
   'DECLARE AND SET VARIABLES
   Dim wbk As Workbook
   Dim Sht As Worksheet
   Dim FileName As String
   Dim Path As String
   Dim Cl As Range
   Dim Rng As Range
   Dim Cnt As Long
 
Application.ScreenUpdating = False


   With Application.FileDialog(4)
       .Show
       Path = .SelectedItems(1) & "\"
   End With
    
      FileName = Dir(Path & "*.xls*")
    
       Do While Len(FileName) > 0
         Set wbk = Workbooks.Open(Path & FileName)


'unhide all worksheets
             For Each WS In ActiveWorkbook.Worksheets
             WS.Visible = xlSheetVisible
             Next WS


        Cnt = Cnt + 1
         Set Sht = wbk.Sheets(4)        'Looks for assays in sheet 4
         If wbk.Sheets(4).Range("B2").Value Like "*MC*" Then  ' If value is MC
                WS.Range.Offset(, 2).Value = c.Address

                WS.Range.Offset(, 0).Value = wbk.Sheets(4).Range(, 0).Value   ' adds the MC name to column A

                WS.Range.Offset(, 5).Value = Rng.Offset(, 5).Value       ' adds the value to the master

                WS.Range.Offset(, 10).Value = wbk.Sheets(1).Range("C7").Value   ' adds the date to the master

                WS.Range.Offset(, 11).Value = wbk.Sheets(1).Range("F7").Value    ' add the certificate to the master
            
                WS.Hyperlinks.Add Anchor:=WS.Range.Offset(, 0), Address:=myfolder & Value, SubAddress:= _
                Sht.Name & "!" & c.Address, TextToDisplay:="Link"
            End If<looks for="" assays="" in="" sheet="" 4
 

<looks for="" assays="" in="" sheet="" 4
 <looks for="" assays="" in="" sheet="" 4
         [COLOR=#ff0000]    Next Rng
[/COLOR]
         wbk.Close False
         FileName = Dir
      Loop
   MsgBox Cnt
End Sub

I get an error at Next Rng. I'm not sure why. If I take it off, the code doesn't work either, so what I wrote must be wrong, but I don't know where.</looks></looks></looks>

 

[JLGWhiz]

I was getting the error at

I get an error at Next Rng

This does not help to understand your problem. What is the error message? You get better assistance if you can be specific about the issues.

 

[JLGWhiz]

I have been trying to analyze the code to determine if I could re-write it for you. I find that you have confused your worksheet variables WS And Sht in some cases, and have also used the ActiveSheet constant intending that it be the master sheet, but it is in fact a sheet from the recently opened workbook. I became confused with what you were attempting, so I curtailed my re-write and suggest that you go over your code and make sure that the variables are referring to the correct worksheets. For information, when you open a workbook with VBA, that workbook automatically becomes the active workbook. If you want to refer to your master sheet, you should set it to a variable and use that variable exclusively throughout the code to avoid confusion. I was not sure if the Sht variable was supposed to be the master or not. There is no explanation of the project that allows me to make that determination.

 

[sgeng4]

Hi JLGWhiz,

I've uploaded an example of what I am trying to do here:
https://files.fm/u/ncjvvqqw

I really hope that helps you understand what I am trying to do.

Most of what I've done has been from reading other threads and putting them together to try to make something that works. I don't fully understand the code. I've been googling and hoping to just find something that works.

The error I get is "Next without For".

I'm just trying to get all the data for any name that starts with MC from all these files, and organize them in a master.

Thank you so much JLGWhiz for the help. I'm going to play with the names, WS and Sht, and see if I can get it to work.

Any extra guidance and help is very appreciated!

 

[JLGWhiz]

The sample sheets did not help. I cannot correlate the variables for data locations in the code to the sheets. I suggest that you continue to work on understanding how to use the variables so that they represent the files, sheets and ranges that you intend for them to represent and then when you get that straightened out, if you need assistance, start a new thread. I am going to disconnect from this one now.

Regards, JLG