Excel Macro Help

n0ra

New Member
Joined
Aug 2, 2007
Messages
3
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:
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Aug 2, 2007
Messages
3
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Not sure if this is what you wanted
Code:
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

New Member
Joined
Aug 2, 2007
Messages
3
it doesnt seem to work.. maybe i missed out something... ( I need to learn VBA :oops: !! )

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.. :oops:
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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.
 

Forum statistics

Threads
1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top