Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies twice...?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code copies between key word is in reverse order | Excel Programming | |||
Code For Copy Copies Twice | Excel Programming | |||
Number of copies to print keep defaulting to 12 copies why? | Excel Worksheet Functions | |||
Code that searches a column, then copies and pastes any matches intoa new Spreadsheet | Excel Programming | |||
Trying to eliminate multiple copies of the SAME code within a UserForm | Excel Programming |