Excel Macro Help

[n0ra]

I have several worksheets in a workbook.. i need a macro that could like copy/extract all the data from these worksheets and have them all together in one main worksheet... at the same time, i need to make sure that any new data inserted in any of these worksheet is copied into the main sheet too...

any help is appreciated.. 10q :wink:

 

[jindon]

I have several worksheets in a workbook.. i need a macro that could like copy/extract all the data from these worksheets and have them all together in one main worksheet... at the same time, i need to make sure that any new data inserted in any of these worksheet is copied into the main sheet too...

any help is appreciated.. 10q :wink:

Explain further in bold part.

 

[n0ra]

thanks for the quick reply...

what i meant was, say in one of the worksheet, there are data entries sorted by months, currently it's only until July 2007.... I need a macro that can detect any new entries for the subsequent months.. any input in this or any other worksheets will be reflected in the main sheet...

i hope this is clear enough..

 

[jindon]

Not sure if this is what you wanted

Sub test()
Dim ws As worksheet, LastR As Range
On Error Resume Next
Sheets("Combined").Delete
On Error GoTo 0
Sheets.Add.Name = "Combined"
For Each ws In Worksheets
     If ws.Name <> "Combined" Then
          Set LastR = Sheets("Combined").Range("a1")
          If IsEmpty(LastR) Then
               Set LastR = Sheets("Combined").Range("a" & Rows.Count).End(xlUp).Offset(1)
          End If
          ws.UsedRange.Copy LastR
     End If
Next
End Sub

 

[n0ra]

it doesnt seem to work.. maybe i missed out something... ( I need to learn VBA !! )

let's try this again...
(1) i got 4 worksheets, (Sheet 1)-(Sheet4).. i want to consolidate all the data in these sheets into a new worksheet, say (Sheet5)...that's the first part....


(2) Then say I add 4 new entries in (Sheet1) and 3 entries in (Sheet2) next week, I'd like Excel to auto-detect these new entries, and then reflect them in (Sheet5) with the rest of the data...

erm.. i hope it sounds better this way..

 

[jindon]

1) What are the columns/Rows of data range?
2) Do you need to report only the changes made?

Your question is too vague.

e.g
what do you want to do if someone changes existing record, not adding new.

Post your sheet anyway for us to understand what you want.