I am very new a creating Macros (I have created maybe 4 that are very simple) and have been assigned to what It feels like an impossible task. Unfortunately, I do not have anyone here at work that can help me with this. I’m really hoping someone here can.

Task:
I have a 100 or so worksheets with the same form of data on it. Every month a new worksheet is added to the workbook with new data. Within this worksheet I have different categories (witness plates) of data. With the data in each individual’s categories a graph needs to be created (So I will have a total of 11 different graph). As a new worksheet with new data is added to the workbook these graph most automatically update (if possible).

Example of data:

Name Live Time Ar K Ca Cr Fe Ni Cu Zn Mo
TL-#70 seconds Count Count Count Count Count Count Count Count Count
Iron Standard #3.# #### 3## ## 7# ##0## #7 7# #3 #7###
Blank Tape ##.## #### 3## # ## ### ##0 ## ## #7#7#
Ca3 standard #0.7 ##3# 3#0 #### 3# ## 7# ## 3# ###33
Clean Plastic ##.## #73# 3#3 7# ## ### ## #7 ## #####
Witness plate 00# #3.0# #77# 3## ### 3# ##0 ## ## ### #####
Witness plate #B ##.## ###0 33# #3 ## ##3 #0# #7 #00 #7###
Witness plate 00# #3.# ##0# 3#0 #0# #7 ### #03 7# ## #7##7
Witness plate 00# #3.## #### 3#3 ## ## ##3 ## 7# #33 ####7
Witness plate 0#0 ##.## #0## #30 ## 3# ### ## ## 7# #07##
Witness plate 0## ##.3# ###7 ### # ## ##7 ## ## ### ##3##
Witness plate 0## ##.# #### ### ## 3# ### ## 70 ## ####7
Witness plate 0#3 ##.## #0## #3# ## ## #3# #0# ## ##3 #####
Witness plate 00# ##.3# ##03 ### ### #0# #3##0 #03# #7# ###7 ##7##
Witness plate 0## ##.0# ##0# ## ## #3 ##7 ## #0 ##3 #####
Fe#a standard ##.0# #### ### 3# #3 ####7 ## ## ## ##0##



So far I have a Macro that loops through all of my worksheets and collects the data. I would somehow like to sort the data by rows according to the “name” for example all data from witness plate 00# would gather in a table to proceed to make a graph. Every time a new worksheet would be added I would run he macro and be able to update all graphs.

This is what I have so far.


Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
Sheets("Combined").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:N").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("D:D,F:I").Select
Range("F1").Activate
Selection.Delete Shift:=xlToLeft
Range("K11").Select
Columns("A:A").ColumnWidth = 20.22

End Sub


If anybody can help be complete this macro that would be great OR if anyone has any ideas on a better way to do this, PLEASE LET ME KNOW!

Thanks you so very much for your time!