ExcelBanter - Copy Multiple Columns, Paste Under a Single column

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Copy Multiple Columns, Paste Under a Single column (https://www.excelbanter.com/excel-worksheet-functions/141563-copy-multiple-columns-paste-under-single-column.html)

Sriram

Copy Multiple Columns, Paste Under a Single column

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

ExcelBanter AI

Answer: Copy Multiple Columns, Paste Under a Single column

Yes, you can definitely copy multiple columns and paste them under a single column in Excel. Here's how you can do it:

Select the columns that you want to copy. You can do this by clicking on the column header of the first column, and then dragging your mouse to select the other columns.
Once you have selected the columns, right-click on any of the selected columns and choose "Copy" from the context menu. Alternatively, you can use the keyboard shortcut "Ctrl+C" to copy the columns.
Now, select the cell where you want to paste the data. This will be the top-left cell of the single column where you want to paste the data.
Right-click on the selected cell and choose "Paste Special" from the context menu.
In the "Paste Special" dialog box, select "Transpose" and click on "OK". This will paste the data from the multiple columns into a single column, with all the values appearing one below the other.
If there were any blank cells in the original data, they will also be pasted into the single column. To eliminate the blank cells, you can use the "Go To Special" feature. Select the column where you pasted the data, and press "Ctrl+G" to open the "Go To" dialog box. Click on the "Special" button, and then select "Blanks" from the options. Click on "OK" to select all the blank cells in the column. Now, right-click on any of the selected cells and choose "Delete" from the context menu. In the "Delete" dialog box, select "Entire row" and click on "OK". This will delete all the blank cells from the column, leaving only the values that you copied from the multiple columns.

That's it! You have now successfully copied multiple columns and pasted them into a single column, eliminating the blank cells.

[email protected]

Copy Multiple Columns, Paste Under a Single column

On May 4, 10:55 am, Sriram wrote:
I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Example:
A1 = "All we need"
B1 = "is love"

C1 = concatenate ( SUBSTITUTE(A1;" ";"") ; SUBSTITUTE(B1;" ";"") )

OK? Check Helop on TRIM, CLEAN, SUBSTITUTE functions.
Mitja

PS: You might need to change ; to , depending on regional settings.

Sriram

Copy Multiple Columns, Paste Under a Single column

Thanks a lot for coming forward to help Mitja... I came to learn about new
Functions but that is not really what I wanted...

I will elaborate my question for you...

Say I have columns like

A B C

1 4 6
2 3 4
3 2
1

Now I want to bring them all under a Single Column D by using a function
where multiple Columns are copied to a single Column eliminating Blanks

A B C D

1 4 6 1 {From Column A}
2 3 4 2
3 2 3
1 4 {From Column B}
3
2
1
6 {From Column C}
4

I apologise if i was being very vague the last time. Thanks in Advance!!!

Sriram

" wrote:

On May 4, 10:55 am, Sriram wrote:
I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Example:
A1 = "All we need"
B1 = "is love"

C1 = concatenate ( SUBSTITUTE(A1;" ";"") ; SUBSTITUTE(B1;" ";"") )

OK? Check Helop on TRIM, CLEAN, SUBSTITUTE functions.
Mitja

PS: You might need to change ; to , depending on regional settings.

Gord Dibben

Copy Multiple Columns, Paste Under a Single column

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Sriram

Copy Multiple Columns, Paste Under a Single column

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Gord Dibben

Copy Multiple Columns, Paste Under a Single column

And thanks to Bernie for the code.

Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

CathyH

Copy Multiple Columns, Paste Under a Single column

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center

These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!

"Gord Dibben" wrote:

And thanks to Bernie for the code.

Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

CathyH

Copy Multiple Columns, Paste Under a Single column

Ooops - that didn't format correctly when I posted but I think you get the
idea - I hope.

"CathyH" wrote:

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center

These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!

"Gord Dibben" wrote:

And thanks to Bernie for the code.

Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Gord Dibben

Copy Multiple Columns, Paste Under a Single column

Cathy

Line wrap caught your example data so is too disjointed to get a clear fix on.

You mean you have one worksheet with 40 columns?

4 columns per set?

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?

You state you want to paste into a single set of 4 columns on a separate sheet
but you also state "These all have to be placed in one long column without
blanks "

You want one column on one sheet from these 40 columns?

Or 4 columns each on 10 sheets?

Or 1 column of 4 sets on 10 sheets?

Gord

On Tue, 15 May 2007 11:39:01 -0700, CathyH
wrote:

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center

These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!

"Gord Dibben" wrote:

And thanks to Bernie for the code.

Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

CathyH

Copy Multiple Columns, Paste Under a Single column

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?
This is what I have - correct

and want to put in one set of 4 columns all on the same sheet
1 2 3 4
no blanks

I forgot to mention as well that each set will have one thing in common - a
customer name
so would look like:
Paint Tools Sundry
Customer 1 2 3 4 1 2 3 4 1 2 3 4
A
B
C

Then it would have to look like for importing:

Customer 1 2 3 4
A p p p p
B p p p p
C p p p p all the rows of paint data
A t t t t
B t t t t
C t t t t all the rows of tools data
A s s s s
B s s s s
C s s s s all the rows of sundry data

I apologize if I'm not explaining it correctly - sometimes it's clear in my
head what I want but hard to translate to others.

Cathy

"Gord Dibben" wrote:

Cathy

Line wrap caught your example data so is too disjointed to get a clear fix on.

You mean you have one worksheet with 40 columns?

4 columns per set?

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?

You state you want to paste into a single set of 4 columns on a separate sheet
but you also state "These all have to be placed in one long column without
blanks "

You want one column on one sheet from these 40 columns?

Or 4 columns each on 10 sheets?

Or 1 column of 4 sets on 10 sheets?

Gord

On Tue, 15 May 2007 11:39:01 -0700, CathyH
wrote:

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center

These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!

"Gord Dibben" wrote:

And thanks to Bernie for the code.

Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Gord Dibben

Copy Multiple Columns, Paste Under a Single column

Please email me the workbook if you choose so's I can get a look at the current
layout.

Change the AT and DOT to appropriate puctuation to get my email address.

Gord

On Tue, 15 May 2007 12:40:01 -0700, CathyH
wrote:

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?
This is what I have - correct

and want to put in one set of 4 columns all on the same sheet
1 2 3 4
no blanks

I forgot to mention as well that each set will have one thing in common - a
customer name
so would look like:
Paint Tools Sundry
Customer 1 2 3 4 1 2 3 4 1 2 3 4
A
B
C

Then it would have to look like for importing:

Customer 1 2 3 4
A p p p p
B p p p p
C p p p p all the rows of paint data
A t t t t
B t t t t
C t t t t all the rows of tools data
A s s s s
B s s s s
C s s s s all the rows of sundry data

I apologize if I'm not explaining it correctly - sometimes it's clear in my
head what I want but hard to translate to others.

Cathy

"Gord Dibben" wrote:

Cathy

Line wrap caught your example data so is too disjointed to get a clear fix on.

You mean you have one worksheet with 40 columns?

4 columns per set?

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?

You state you want to paste into a single set of 4 columns on a separate sheet
but you also state "These all have to be placed in one long column without
blanks "

You want one column on one sheet from these 40 columns?

Or 4 columns each on 10 sheets?

Or 1 column of 4 sets on 10 sheets?

Gord

On Tue, 15 May 2007 11:39:01 -0700, CathyH
wrote:

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center

These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!

"Gord Dibben" wrote:

And thanks to Bernie for the code.

Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Gord Dibben

Copy Multiple Columns, Paste Under a Single column

Group

I would like to turn this one back over to the group for one of the VBA gurus.

Sort of beyond my skills without a lot of kludge-code.

Cathy sent me a sample workbook which I can send to anyone looking to assist.

Or upload it to your favorite site.

Thanks Gord

On Tue, 15 May 2007 16:08:54 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Please email me the workbook if you choose so's I can get a look at the current
layout.

Change the AT and DOT to appropriate puctuation to get my email address.

Gord

On Tue, 15 May 2007 12:40:01 -0700, CathyH
wrote:

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?
This is what I have - correct

and want to put in one set of 4 columns all on the same sheet
1 2 3 4
no blanks

I forgot to mention as well that each set will have one thing in common - a
customer name
so would look like:
Paint Tools Sundry
Customer 1 2 3 4 1 2 3 4 1 2 3 4
A
B
C

Then it would have to look like for importing:

Customer 1 2 3 4
A p p p p
B p p p p
C p p p p all the rows of paint data
A t t t t
B t t t t
C t t t t all the rows of tools data
A s s s s
B s s s s
C s s s s all the rows of sundry data

I apologize if I'm not explaining it correctly - sometimes it's clear in my
head what I want but hard to translate to others.

Cathy

"Gord Dibben" wrote:

Cathy

Line wrap caught your example data so is too disjointed to get a clear fix on.

You mean you have one worksheet with 40 columns?

4 columns per set?

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?

You state you want to paste into a single set of 4 columns on a separate sheet
but you also state "These all have to be placed in one long column without
blanks "

You want one column on one sheet from these 40 columns?

Or 4 columns each on 10 sheets?

Or 1 column of 4 sets on 10 sheets?

Gord

On Tue, 15 May 2007 11:39:01 -0700, CathyH
wrote:

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center

These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!

"Gord Dibben" wrote:

And thanks to Bernie for the code.

Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim mycell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each mycell In myRng
If mycell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next mycell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
mycell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub

Gord Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

All times are GMT +1. The time now is 04:44 PM.