Macro to copy data from X worksheets in to a single Master worksheet

[thunt]

I searched the forums and found things similar to what I need, but nothing specifically and these posts were 3-10 years old and no one was responding to them so I thought I would ask on a new post...

I have a workbook with several worksheets (will be adding and removing worksheets often) all formatted the same. Headers are in row 1 with data starting in row 2 and going from A:K. Also, each worksheet has varying rows of data

1. I am looking for a macro that will go through the workbook and grab the data from almost every worksheet and compile (copy) it in a worksheet called "Master" starting in row 2 (as headers will be in row 1).
2. I need it so every time I run the macro (I will put a refresh button in), it deletes all of the current data in "Master" (from row 2 and down) and then updates it with the new data from each worksheet.
3. I am trying to find a way that when it compiles the data in to "Master" it will also add in the name of the worksheet (in column A) where that data came from, so I have a understanding of which worksheet it is/was from.
4. Besides the "Master" worksheet, I have a worksheet called "Summary" that I am will be using a drop down list and simple vlookup against all the data compiled in "Master" so I can select the defined name/range and then get the values based off the vlookup (I know how to do this part), but I do not want the marco to run on this "Summary" worksheet either. I know it would be "If Sht.Name <> "Master" Then" so it won't run on the Master sheet, but have not seen a macro similar to what I am looking to do that won't run on two specific sheets (Master and Summary).

Thanks in advance

-I am using Excel 2010 on windows 7

 

[JackDanIce]

You haven't specified what data you want to copy back to the Master worksheet.

Try this code:

Sub CompileData()

Dim wsMaster as worksheet: set wsMaster = Sheets("Master")
Dim ws as worksheet
Dim LR as Long
Dim LC as Long
Dim i as Long: i = 2

Application.ScreenUpdating = False

With wsMaster
  LR = .Range("A" & .rows.count).end(xlup).Row
  LC = .cells(1 & .columns.count).end(xltoleft).Column
  .Range("A2").Resize(LR-1,LC).ClearContents
End With

For each ws in ActiveWorkbook.Worksheets
    With wsMaster
      If ws.Name <> "Master" And ws.Name <> "Summary" Then
          .Range("A" & i).Value = ws.Name
          .Range("B" & i).Value = ws.Range("A1").Value
          i = i + 1
      End if
    End With
Next ws

If ActiveSheet.Name <> "Master" Then wsMaster.Select

Application.ScreenUpdating = True
Msgbox "Finished extracting data into Master sheet"
Set wsMaster = Nothing

End sub

 

[thunt]

You haven't specified what data you want to copy back to the Master worksheet.

I am looking to copy data from columns A:K starting in row 2 (number of rows varies, but always start in row 2) in the worksheets.

I tried the code you provided and get a "Run-time error '1004': Application-defined or object-define error" and its look to be happening in the 14th row of the code (string below)

.Range("A2").Resize(LR - 1, LC).ClearContents