In workbook, I have six sheets. Sheets 1, 2 & 3 have data in column A with varied number of rows.

The code here copies each of those columns to sheet 4, Column A. BUT does it twice. Sheet data 1, 2, 3 listed in sheet 4 followed by an identical list right below the first.

Once I get the double copy solved, I intend to use an array with the elements as the sheets I want to copy from. There is a sheet 5 & 6 but they are blank now. Still I would prefer to avoid even looking at them unless they were included in the array.

Thanks,
Howard

Option Explicit

Sub ThreeColumnsToOne()

Dim lastRow As Long, lastRowDest As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

lastRowDest = 1

For Each ws In ThisWorkbook.Sheets
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A1:A" & lastRow).Copy Destination:=Sheets("Sheet4").Range("A" & lastRowDest)(1)
lastRowDest = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row + 1
Next

Application.ScreenUpdating = True
MsgBox "Done!"

End Sub

Hi Howard,

Am Mon, 18 Nov 2013 22:33:03 -0800 (PST) schrieb Howard:

In workbook, I have six sheets. Sheets 1, 2 & 3 have data in column A with varied number of rows.

The code here copies each of those columns to sheet 4, Column A. BUT does it twice. Sheet data 1, 2, 3 listed in sheet 4 followed by an identical list right below the first.

Once I get the double copy solved, I intend to use an array with the elements as the sheets I want to copy from. There is a sheet 5 & 6 but they are blank now. Still I would prefer to avoid even looking at them unless they were included in the array.

try:

Sub ThreeColumnsToOne()

Dim lastRow As Long, lastRowDest As Long
Dim varOut As Variant
Dim i As Integer

Application.ScreenUpdating = False

lastRowDest = 1

For i = 1 To 3
With Sheets(i)
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varOut = .Range("A1:A" & lastRow)
Sheets(4).Cells(Rows.Count, lastRowDest).End(xlUp) _
.Offset(1, 0).Resize(rowsize:=lastRow) = varOut
End With
Next

Application.ScreenUpdating = True
MsgBox "Done!"

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, November 18, 2013 11:36:15 PM UTC-8, Claus Busch wrote:
Hi Howard,


try:

Sub ThreeColumnsToOne()

Dim lastRow As Long, lastRowDest As Long

Dim varOut As Variant

Dim i As Integer



Application.ScreenUpdating = False



lastRowDest = 1



For i = 1 To 3

With Sheets(i)

lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

varOut = .Range("A1:A" & lastRow)

Sheets(4).Cells(Rows.Count, lastRowDest).End(xlUp) _

.Offset(1, 0).Resize(rowsize:=lastRow) = varOut

End With

Next



Application.ScreenUpdating = True

MsgBox "Done!"



End Sub





Regards

Claus B.

I'll give your suggestion a go, I'm sure it will work.

I have tried this worksheet array and the new problem with it is that I only get the Sheet 3 data copied into Sheet 4.

I'm thinking the advantage here is that the sheet selection and the sheet order can be adjusted in the array.

Say Worksheets(Array("Sheet3", "Sheet6", "Sheet1")) (Omitting sheets 1, 2 from the copy to sheet 4)

Does that make any sense?

Howard


Sub ThreeColumnsToOne()

Dim lastRow As Long, lastRowDest As Long
Dim sh As Worksheet
Application.ScreenUpdating = False

For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
lastRowDest = 1
lastRow = sh.Range("A" & Rows.Count).End(xlUp).Row
sh.Range("A1:A" & lastRow).Copy Destination:=Sheets("Sheet4").Range("A" & lastRowDest)
lastRowDest = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row + 1
Next

Application.ScreenUpdating = True
MsgBox "Done!"

End Sub

Hi Howard,

Am Mon, 18 Nov 2013 23:55:59 -0800 (PST) schrieb Howard:

I have tried this worksheet array and the new problem with it is that I only get the Sheet 3 data copied into Sheet 4.

I'm thinking the advantage here is that the sheet selection and the sheet order can be adjusted in the array.

Say Worksheets(Array("Sheet3", "Sheet6", "Sheet1")) (Omitting sheets 1, 2 from the copy to sheet 4)

with a sheet array try:

Sub ThreeColumnsToOne()

Dim lastRow As Long, lastRowDest As Long
Dim varSheets As Variant
Dim varOut As Variant
Dim i As Integer
Application.ScreenUpdating = False

varSheets = Array("Sheet1", "Sheet2", "Sheet3")
lastRowDest = 1
For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
varOut = .Range("A1:A" & lastRow)
Sheets("Sheet4").Cells(lastRowDest, 1) _
.Resize(rowsize:=lastRow) = varOut
lastRowDest = Sheets("Sheet4").Range("A" & Rows.Count) _
.End(xlUp).Row + 1
End With
Next

Application.ScreenUpdating = True
MsgBox "Done!"

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

with a sheet array try:



Sub ThreeColumnsToOne()



Dim lastRow As Long, lastRowDest As Long

Dim varSheets As Variant

Dim varOut As Variant

Dim i As Integer

Application.ScreenUpdating = False



varSheets = Array("Sheet1", "Sheet2", "Sheet3")

lastRowDest = 1

For i = LBound(varSheets) To UBound(varSheets)

With Sheets(varSheets(i))

lastRow = .Range("A" & Rows.Count).End(xlUp).Row

varOut = .Range("A1:A" & lastRow)

Sheets("Sheet4").Cells(lastRowDest, 1) _

.Resize(rowsize:=lastRow) = varOut

lastRowDest = Sheets("Sheet4").Range("A" & Rows.Count) _

.End(xlUp).Row + 1

End With

Next



Application.ScreenUpdating = True

MsgBox "Done!"



End Sub


Regards

Claus B.


Works a treat. Thank you.

Regards,
Howard

If you pre-select (group) the sheets to copy from then you can iterate
as follows...

For Each ws In ActiveWindow.SelectedSheets
'code here
Next 'ws

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Tuesday, November 19, 2013 10:43:56 AM UTC-8, GS wrote:
If you pre-select (group) the sheets to copy from then you can iterate

as follows...



For Each ws In ActiveWindow.SelectedSheets

'code here

Next 'ws



--

Garry

Hi Garry,

It's not clear to me how you mean "If you pre-select (group)the sheets..."

Something like this, maybe, but really I'm just guessing.

Set SelectedSheets = (Sheet1,Sheet2,Sheet3)

Howard

On Tuesday, November 19, 2013 10:43:56 AM UTC-8, GS wrote:
If you pre-select (group) the sheets to copy from then you can
iterate

as follows...



For Each ws In ActiveWindow.SelectedSheets

'code here

Next 'ws



--

Garry

Hi Garry,

It's not clear to me how you mean "If you pre-select (group)the
sheets..."

Something like this, maybe, but really I'm just guessing.

Set SelectedSheets = (Sheet1,Sheet2,Sheet3)

Howard

No! I mean the user selects the sheets to copy data from, then runs the
code. This serves not knowing in advance which sheets the data needs to
be copied from.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

No! I mean the user selects the sheets to copy data from, then runs the

code. This serves not knowing in advance which sheets the data needs to

be copied from.



--

Garry


Okay, that makes sense, so would Alt + click each sheet tab you wanted to copy from?

Howard

No! I mean the user selects the sheets to copy data from, then runs
the

code. This serves not knowing in advance which sheets the data needs
to

be copied from.



--

Garry


Okay, that makes sense, so would Alt + click each sheet tab you
wanted to copy from?

Howard

Alt works for contiguous sheets. Use Ctrl otherwise!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Tuesday, November 19, 2013 6:51:55 PM UTC-8, GS wrote:


No! I mean the user selects the sheets to copy data from, then runs

the



code. This serves not knowing in advance which sheets the data needs

to



be copied from.







--



Garry





Okay, that makes sense, so would Alt + click each sheet tab you

wanted to copy from?



Howard



Alt works for contiguous sheets. Use Ctrl otherwise!



--

Garry

Woops, that was a typo, I did mean Ctrl.

Thanks,
Howar

Oops! Should read...

*Shift* works for contiguous sheets. Use Ctrl otherwise!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Tuesday, November 19, 2013 8:43:57 PM UTC-8, GS wrote:
Oops! Should read...



*Shift* works for contiguous sheets. Use Ctrl otherwise!



--

Garry


It's more difficult to spell it out in type than to just do it. If I get it wrong on the first try, easy enough to back out and use the other key. lol

Howard

On Tuesday, November 19, 2013 8:43:57 PM UTC-8, GS wrote:
Oops! Should read...



*Shift* works for contiguous sheets. Use Ctrl otherwise!



--

Garry


It's more difficult to spell it out in type than to just do it. If I
get it wrong on the first try, easy enough to back out and use the
other key. lol

Howard

Well.., you can use them in combination too when some sheets are
contiguous and some are not!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com