Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,814
Default Merge cells to master sheet

Ron de Bruins code to merge cells is very helpful. I'm trying to modify his
code to "Merge cells from all or some worksheets into one Master sheet". How
do you make the range you're copying FROM dynamic? Let's say I want to start
in C3 and want to include to column J but the last row may not be the same in
each sheet? So I want the range C3..J?? How do I do that?

Second question...what if I'm not sure what the last column is? Is there a
function that returns the last cell in usedrange?

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default Merge cells to master sheet

Try this one

You can also test the last column if you want with the lastcol function
If you need help post back.
I will help you tomorrow (bed time now for me)

Sub Test()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long

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

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

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

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
sh.Range(sh.Cells(3, "C"), sh.Cells(shLast, "J")).Copy DestSh.Cells(Last + 1, "A")

End If
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve" wrote in message ...
Ron de Bruins code to merge cells is very helpful. I'm trying to modify his
code to "Merge cells from all or some worksheets into one Master sheet". How
do you make the range you're copying FROM dynamic? Let's say I want to start
in C3 and want to include to column J but the last row may not be the same in
each sheet? So I want the range C3..J?? How do I do that?

Second question...what if I'm not sure what the last column is? Is there a
function that returns the last cell in usedrange?

Thanks.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Merge cells to master sheet

See in-line

On Wed, 12 Dec 2007 14:16:01 -0800, Steve
wrote:

Ron de Bruins code to merge cells is very helpful. I'm trying to modify his
code to "Merge cells from all or some worksheets into one Master sheet". How
do you make the range you're copying FROM dynamic? Let's say I want to start
in C3 and want to include to column J but the last row may not be the same in
each sheet? So I want the range C3..J?? How do I do that?


Set rng = Range(Range("C3"), Cells(Rows.Count, "J").End(xlUp))


Second question...what if I'm not sure what the last column is? Is there a
function that returns the last cell in usedrange?


If data is contiguous you can use currentregion

Set rng = Range("C3").CurrentRegion

To return the address of last cell in currentregion

Set rng = Range("C3").CurrentRegion
MsgBox rng(rng.Count).Address


Thanks.




Gord Dibben MS Excel MVP

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
merge 3 workbooks (and all coniciding worksheets) into 1 master wb maijiuli Excel Discussion (Misc queries) 0 November 5th 07 06:58 PM
How do I import and merge Excel data into a master project file? Sentry16 Excel Discussion (Misc queries) 0 June 11th 07 03:39 PM
Linked cells from master data sheet and sorting merrittr Excel Worksheet Functions 3 October 15th 06 11:13 PM
sorting master sheet messes up cells in other sheets linked to it Kt Excel Worksheet Functions 1 October 30th 05 01:25 PM
sorting master sheet messes up cells in other sheets linked to it Kt Excel Worksheet Functions 0 October 30th 05 11:36 AM


All times are GMT +1. The time now is 03:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"