I'm sure this is very straightforward but...

I'm trying to copy columns A to D from sheets 1 to 3 into a master sheet. My
difficulty is that the datasets are of different sizes so a record macro is
no use.

Sheet 1 has multiple ranges (a3:d36, a40:d47, a51:d55, a59:d71) which don't
change. Sheets 2 and 3 are frequently updated and so change in size.

So, basically, I'm looking for a bit of code to paste this stuff into a
master sheet.

Sounds easy enough. Well, that's what I thought.

Thks in anticipation,
MM

MM,

Use a macro - see below. Assumes that your 3 sheets are Sheet1, Sheet2, and Sheet3.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim mySht As Worksheet
Dim i As Integer

Set mySht = Sheets.Add
mySht.Name = "Master Sheet"

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

For i = 1 To 3
Worksheets("Sheet" & i).Range("A1", _
Worksheets("Sheet" & i).Range("A65536").End(xlUp)).Resize(, 4).Copy _
mySht.Range("a65536").End(xlUp).Offset(1, 0)
Next i

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

End Sub


"MM" wrote in message
...
I'm sure this is very straightforward but...

I'm trying to copy columns A to D from sheets 1 to 3 into a master sheet. My
difficulty is that the datasets are of different sizes so a record macro is
no use.

Sheet 1 has multiple ranges (a3:d36, a40:d47, a51:d55, a59:d71) which don't
change. Sheets 2 and 3 are frequently updated and so change in size.

So, basically, I'm looking for a bit of code to paste this stuff into a
master sheet.

Sounds easy enough. Well, that's what I thought.

Thks in anticipation,
MM

Thanks for the response, Bernie. I'll give it a go.


"Bernie Deitrick" wrote:

MM,

Use a macro - see below. Assumes that your 3 sheets are Sheet1, Sheet2, and Sheet3.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim mySht As Worksheet
Dim i As Integer

Set mySht = Sheets.Add
mySht.Name = "Master Sheet"

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

For i = 1 To 3
Worksheets("Sheet" & i).Range("A1", _
Worksheets("Sheet" & i).Range("A65536").End(xlUp)).Resize(, 4).Copy _
mySht.Range("a65536").End(xlUp).Offset(1, 0)
Next i

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

End Sub


"MM" wrote in message
...
I'm sure this is very straightforward but...

I'm trying to copy columns A to D from sheets 1 to 3 into a master sheet. My
difficulty is that the datasets are of different sizes so a record macro is
no use.

Sheet 1 has multiple ranges (a3:d36, a40:d47, a51:d55, a59:d71) which don't
change. Sheets 2 and 3 are frequently updated and so change in size.

So, basically, I'm looking for a bit of code to paste this stuff into a
master sheet.

Sounds easy enough. Well, that's what I thought.

Thks in anticipation,
MM

I'm afraid it doesn't work (or I'm doing something wrong), so I'll expand on
what I'm trying to do.

I want to copy the following:

Sheets("Geodetic points").Range("a3:d36, a40:d47, a51:d55, a59:d71")
Sheets("Control <T1152").Range("a3:d65536")
Sheets("Control T2000").Range("a3:d65536")

into one sheet. All sheets are in one workbook. The "Geodetic points"
sheet's contents do not change but the other two's do.

The "copy" part seems to work ok - I think it's the paste part that I'm
tripping up on.

Hopefully, MM



"MM" wrote:

Thanks for the response, Bernie. I'll give it a go.


"Bernie Deitrick" wrote:

MM,

Use a macro - see below. Assumes that your 3 sheets are Sheet1, Sheet2, and Sheet3.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim mySht As Worksheet
Dim i As Integer

Set mySht = Sheets.Add
mySht.Name = "Master Sheet"

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

For i = 1 To 3
Worksheets("Sheet" & i).Range("A1", _
Worksheets("Sheet" & i).Range("A65536").End(xlUp)).Resize(, 4).Copy _
mySht.Range("a65536").End(xlUp).Offset(1, 0)
Next i

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

End Sub


"MM" wrote in message
...
I'm sure this is very straightforward but...

I'm trying to copy columns A to D from sheets 1 to 3 into a master sheet. My
difficulty is that the datasets are of different sizes so a record macro is
no use.

Sheet 1 has multiple ranges (a3:d36, a40:d47, a51:d55, a59:d71) which don't
change. Sheets 2 and 3 are frequently updated and so change in size.

So, basically, I'm looking for a bit of code to paste this stuff into a
master sheet.

Sounds easy enough. Well, that's what I thought.

Thks in anticipation,
MM

You can't copy the Range("a3:d65536"). Try this version, assuming that the data is in a block.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim mySht As Worksheet
Dim i As Integer
Dim myShtNames(1 To 3) As String

myShtNames(1) = "Geodetic points"
myShtNames(2) = "Control <T1152"
myShtNames(3) = "Control T2000"

Set mySht = Sheets.Add
mySht.Name = "Master Sheet"

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

For i = 1 To 3
Worksheets(myShtNames(i)).Range("A3", _
Worksheets(myShtNames(i)).Range("A65536").End(xlUp )).Resize(, 4).Copy _
mySht.Range("a65536").End(xlUp).Offset(1, 0)
Next i

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

End Sub


"MM" wrote in message
...
I'm afraid it doesn't work (or I'm doing something wrong), so I'll expand on
what I'm trying to do.

I want to copy the following:

Sheets("Geodetic points").Range("a3:d36, a40:d47, a51:d55, a59:d71")
Sheets("Control <T1152").Range("a3:d65536")
Sheets("Control T2000").Range("a3:d65536")

into one sheet. All sheets are in one workbook. The "Geodetic points"
sheet's contents do not change but the other two's do.

The "copy" part seems to work ok - I think it's the paste part that I'm
tripping up on.

Hopefully, MM



"MM" wrote:

Thanks for the response, Bernie. I'll give it a go.


"Bernie Deitrick" wrote:

MM,

Use a macro - see below. Assumes that your 3 sheets are Sheet1, Sheet2, and Sheet3.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim mySht As Worksheet
Dim i As Integer

Set mySht = Sheets.Add
mySht.Name = "Master Sheet"

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

For i = 1 To 3
Worksheets("Sheet" & i).Range("A1", _
Worksheets("Sheet" & i).Range("A65536").End(xlUp)).Resize(, 4).Copy _
mySht.Range("a65536").End(xlUp).Offset(1, 0)
Next i

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

End Sub


"MM" wrote in message
...
I'm sure this is very straightforward but...

I'm trying to copy columns A to D from sheets 1 to 3 into a master sheet. My
difficulty is that the datasets are of different sizes so a record macro is
no use.

Sheet 1 has multiple ranges (a3:d36, a40:d47, a51:d55, a59:d71) which don't
change. Sheets 2 and 3 are frequently updated and so change in size.

So, basically, I'm looking for a bit of code to paste this stuff into a
master sheet.

Sounds easy enough. Well, that's what I thought.

Thks in anticipation,
MM

A thousand thank yous,
That works very nicely. I just have to delete a few rows at the end of the
macro and it's perfect. Easy when you know how, eh?

Thanks again for your help,
MM

"Bernie Deitrick" wrote:

You can't copy the Range("a3:d65536"). Try this version, assuming that the data is in a block.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim mySht As Worksheet
Dim i As Integer
Dim myShtNames(1 To 3) As String

myShtNames(1) = "Geodetic points"
myShtNames(2) = "Control <T1152"
myShtNames(3) = "Control T2000"

Set mySht = Sheets.Add
mySht.Name = "Master Sheet"

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

For i = 1 To 3
Worksheets(myShtNames(i)).Range("A3", _
Worksheets(myShtNames(i)).Range("A65536").End(xlUp )).Resize(, 4).Copy _
mySht.Range("a65536").End(xlUp).Offset(1, 0)
Next i

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

End Sub


"MM" wrote in message
...
I'm afraid it doesn't work (or I'm doing something wrong), so I'll expand on
what I'm trying to do.

I want to copy the following:

Sheets("Geodetic points").Range("a3:d36, a40:d47, a51:d55, a59:d71")
Sheets("Control <T1152").Range("a3:d65536")
Sheets("Control T2000").Range("a3:d65536")

into one sheet. All sheets are in one workbook. The "Geodetic points"
sheet's contents do not change but the other two's do.

The "copy" part seems to work ok - I think it's the paste part that I'm
tripping up on.

Hopefully, MM



"MM" wrote:

Thanks for the response, Bernie. I'll give it a go.


"Bernie Deitrick" wrote:

MM,

Use a macro - see below. Assumes that your 3 sheets are Sheet1, Sheet2, and Sheet3.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim mySht As Worksheet
Dim i As Integer

Set mySht = Sheets.Add
mySht.Name = "Master Sheet"

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

For i = 1 To 3
Worksheets("Sheet" & i).Range("A1", _
Worksheets("Sheet" & i).Range("A65536").End(xlUp)).Resize(, 4).Copy _
mySht.Range("a65536").End(xlUp).Offset(1, 0)
Next i

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

End Sub


"MM" wrote in message
...
I'm sure this is very straightforward but...

I'm trying to copy columns A to D from sheets 1 to 3 into a master sheet. My
difficulty is that the datasets are of different sizes so a record macro is
no use.

Sheet 1 has multiple ranges (a3:d36, a40:d47, a51:d55, a59:d71) which don't
change. Sheets 2 and 3 are frequently updated and so change in size.

So, basically, I'm looking for a bit of code to paste this stuff into a
master sheet.

Sounds easy enough. Well, that's what I thought.

Thks in anticipation,
MM

A thousand thank yous,
That works very nicely.

You're welcome. Thanks for letting me know that you got things to work.

Bernie
MS Excel MVP