Dynamic cell referencing on another worksheet

[AkaMaDdiSk]

Hi, I am a super novice in Excel and need some help referencing cells.
currently have 6 worksheets in a workbook each with same ten colum
headings on row 1 starting in cell A1. Worksheets 2-6 will belong to
different people where they will each enter a different rows of dat
each week on their respectively sheets. Each week the number of row
entered will differ. For example, this week a worksheet might have
rows below the column head while next week, I it might have 6 rows.

What I'd like to do is have all the rows entered in sheets 2-
automatically show up on sheet 1. So sheet 1 will have the aggregate o
all the data entered in 2-6 by the end of the week.

Is this at all possible? If so, how how I go about doing this?

Help is much appreciated.

Thanks

 

[Bernie Deitrick]

Aka,

You can use a macro to copy and paste all of the values from the five sheets
onto a summary sheet. Copy the macro below into a codemodule of either your
personal.xls, or the current workbook. Then select the five sheets using
ctrl-clicks or shift-click on the tabs, and run the macro. It assumes that
your data starts in cell A1, is contiguous, and has no enpty cells in column
A.

If those assumptions are wrong, the code can be modified.

HTH,
Bernie
MS Excel MVP

Sub ExtractData()
'By Bernie Deitrick
Dim mySheets() As String
Dim myRange As Range
Dim Sh As Worksheet
Dim SelCount As Integer
Dim i As Integer

SelCount = ActiveWorkbook.Windows(1).SelectedSheets.Count
If SelCount = 1 Then
MsgBox prompt:="First select the sheets from which to extract the data.", _
Buttons:=vbOKOnly + vbExclamation, Title:="Bernie's Data Extraction"
Exit Sub
End If

ReDim mySheets(1 To SelCount)

i = 1
For Each Sh In ActiveWindow.SelectedSheets
mySheets(i) = Sh.Name
i = i + 1
Next

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
Worksheets(1).Select
Sheets("Combined Data").Delete
Worksheets.Add.Name = "Combined Data"

For i = 1 To SelCount
Set Sh = Worksheets(mySheets(i))
Sh.Range("A1").CurrentRegion.Copy
Sheets("Selected Slice").Select
Set myRange = Range("A65536").End(xlUp)(2)
myRange(1, 2).PasteSpecial Paste:=xlValues
myRange(1, 2).PasteSpecial Paste:=xlFormats
Range(myRange, Range("B65536").End(xlUp)(1, 0)).Value = Sh.Name
Next i
End Sub