Linking Text in Multiple Excel Sheets

Question

Hey all,

I am trying to gather text from multiple excel sheets but am not sure how.  So I receive x amount of excel sheets every day with cells A1 and A2 that have unique text.  I would like to gather this unique text from each sheet and compile it into one master excel sheet with each text occupying one individual cell ideally.  Once compiled,  I would like to sort this list alphabetically so I can compare this list to a known list.  Basically, I want to be aware of my inventory on a day to day basis where I receive what is being used from this database of excel sheets daily and will compare it to my known inventory on this master excel sheet.

Regards,

Me

Answer 1

Hello,

Thanks for the help Tom but this is not quite working for me.  First off it might be me as I am not the best at coding even though I have done some over my last few years of college.  Do I need to save my excel sheets as Master.xlsm for my "Master" sheet and all of my sub excel sheets that are being used to get the individual names from as Worksheets.xlsm?  I know that for Matlab that can make a difference when calling up different level of sheets for coding.

I have tried naming the sheets the above but it didn't help.  I haven't been able to input names into A1 and A2 of my "Worksheets" doc and have them compile into my "Master" doc.  I have both excel docs saved to the same folder on my desktop as well since this is effectively where all the docs that get emailed will be saved to a folder.  Also, all docs received will be Macro excel sheets as well if that will change things for what I am trying to do.

Regards,

Chad

Answer 2

You could do it with a macro

Sub ABC()

Dim sh As Worksheet, sh1 As Worksheet

Worksheets.Add After:=Worksheets(Worksheets.Count)

Set sh = ActiveSheet

sh.Name = "Master"

For Each sh1 In ActiveWorkbook.Worksheets

  If sh1.Name <> sh.Name Then

     sh1.Range("A1:A2").Copy _

       sh.Cells(sh.Rows.Count, 1).End(xlUp).Offset(1, 0)

 End If

Next

End Sub

With only your workbook open in Excel

go to the Visual Basic Editor  (alt+F11).  In the menu choose Insert=>Module

past in the above code

Alt+F11 to get back to Excel.  Then run the macro.

--

Regards,

Tom Ogilvy