collecting data from many sheets to one sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.
 
Thanks. I haven't heard or used consolidate command. I checked the help.
sounds good and will help want I want to do. But like other commands, help is
not easy to follow at least for me. Is there any tutor in MVP for this
command?
 
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
 
Perhaps Ron will explain in the other thread, where you're working with
him on the same problem. (Re: Consolidate different worksheets into one
worksheet)
 
Thank you so much for this post!!!! It just saved my life and about a days
work! :-)

I modified the code to fit my needs, but it will not run. It stops at the
following line:
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy

Can someone tell me what I did wrong? Here's the modified code:
Sub MergeSheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Master" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Master").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Master"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"

'loop through all worksheets and copy the data to the DestSh
For Each sh In Sheets(Array("Start", "End"))
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies values and formats from A2 onward
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With

'This will copy the sheet name in the H column if you want
'DestSh.Cells(Last + 1, "H").Value = sh.Name

Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Thanks again!
 
Oops click send to fast

If there is no data on one of the sheets the you also have a problem
 
ahh
that must be the issue, i created a sandwich Start! and End! so that I
wouldn't have to keep updating the range because I want a certain number of
tabs that vary from month to month (Jan!, Feb!, etc.) but those aren't the
only tabs in the workbook so I didn't want to use the one that did ALL tabs.

The start and end tabs I inserted are empty. :-(
Here I thought I was being creative!

Thanks for the clarification.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Keep Excel Sheet Formatting On a CSV Sheet with vba 1
Pivot from all sheets 1
How to update data from multiple sheets to one specific sheets 4
Summary from multiple sheets 1
Generating Master sheet from child sheets 3
Merge Multiple workbook sheets into one workbook 0
Moving certain data to different sheet 1
copy rows from one Data sheet to another sheet based on cell conte 2

Back
Top