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...