transpose the code from "rows" to "columns"

[markx]

Hello,

I'm using the following code (see below), that basically enables me to copy
rows from "Master" sheet to other worksheets based on the values in column A
(all the rows with "apple" in column "A" will be copied, one under another,
to a new sheet (automatically created, if needed) called "apple" etc...).

What I would like now is to slightly modify this code in order to copy
columns (and not rows) to new worksheets, based on the values in row 1. So,
actually I would like to "transpose" the code.

More concretly, if my columns (in row 1, starting column B) have the
following values:
"apple" "bananas" "apple" "oranges" "apple" "apple"
"bananas" "bananas"
.... then I would like the adapted code to copy all the columns with "apple"
value (i.e. column B, D, F, G) to the new worksheet called "apple" and paste
them one after another (i.e. into columns B, C, D, E)

I tried the "dummy way" changing all the "row" expressions into "column",
and then, at the end, changing also the offset from "Offset(1, 0)" to
"Offset(0, 1)", but apparently it's not enough. Could you please help me on
this?

Many thanks!
Mark

P.S. I know that I can transpose the data manually and then apply the code
below, but I would like to avoid this.
P.P.S. Somebody told me (on one of the "excel" forums) that it's better to
replace "Dim CurrentCellValue As String" by "Dim CurrentCellValue As
Variant". Could you also tell me what could that change?

----------------
Sub CopyRowsToSheets()
'copy rows to worksheets based on value in column A
'assume the worksheet name to paste to is the value in Col A
Dim CurrentCell As Range
Dim SourceRow As Range
Dim Targetsht As Worksheet
Dim TargetRow As Long
Dim CurrentCellValue As String

'start with cell A2 on "Master" sheet
Set CurrentCell = Worksheets("Master").Cells(2, 1) 'row ... column ...

Do While Not IsEmpty(CurrentCell)
CurrentCellValue = CurrentCell.Value
Set SourceRow = CurrentCell.EntireRow

'Check if worksheet exists
On Error Resume Next
Testwksht = Worksheets(CurrentCellValue).Name
If Err.Number = 0 Then
'MsgBox CurrentCellValue & " worksheet Exists"
Else
MsgBox "Adding a new worksheet for " & CurrentCellValue
Worksheets.Add.Name = CurrentCellValue
End If

On Error GoTo 0 'reset on error to trap errors again

Set Targetsht = ActiveWorkbook.Worksheets(CurrentCell.Value)
'note: using CurrentCell.value gave me an error if the value was
numeric

' Find next blank row in Targetsht - check using Column A
TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1
SourceRow.Copy Destination:=Targetsht.Cells(TargetRow, 1)

'do the next cell
Set CurrentCell = CurrentCell.Offset(1, 0)
Loop
End Sub

 

[cush]

Start with the Dim CurrentCellValue as String
As you noted you are getting an error if the current cell
is numeric since you have declared it as a String type.
If you change this to Variant you won't get the error
because Variant means all types.

The term "transpose" in excel has a specific meaning:
You first select a horizontal range and transpose this range
into a verticle range of cells; or vice versa.
In your description it sounds like you dont want to transpose;
you just want to copy colums to new columns instead of
rows to new rows.

If my read is correct then:
Change: Set Sourcerow =CurrentCell.EntireRow
To: Set SourceCol = CurrentCell.EntireColumn

and
TargetCol = Targetsht.Cells(1,Columns.Count).End(xlToLeft).Column+ 1
SourceCol.Copy Destination:=Targetsht.Cells(1,TargetCol)
and
Set CurrentCell = CurrentCell.Offset(0,1)

You will also have to change your variable names accordingly

 

[markx]

Thanks a lot Cush, your solution works perfectly!

An additionnal question: what should I add to the code if I would also like
to get the initial column "A" (the one from the original worksheet) to all
these new worksheets? (You can explain me with the code below (concerning
rows) and I will adapt it (with God's help) to the columns. And also: how
should I modify the code if one day I would like to copy all these columns
to the new workbooks (and not worksheets)?

BTW, do you know what is the best way to learn VBA for Excel? Are there any
good sites with exercices etc...? I googled it, but didn't get any
"reference" site, where everything is explained "step-by-step".

Sorry if I bother you with all these questions...
Thanks again for any help on this.
Mark

 

[cush]

When you want to copy data, you first have to identify the range you want
copied and the range where it will go.

In your first code, the line that actually does the copying is:

SourceRow.Copy Destination:=Targetsht.Cells(TargetRow, 1)

All the rest is just setting it up. It can be as simple as:

Range("A1").Copy Destination:=Range("B1").

You dont even have to use the word Destination:
Range("A1").Copy Range("B1")
will work!

Using Named Ranges, you can do it like:
MyListA.Copy Destination:=MyListB
or

Using variables declared in your code you can do it like:
Dim Source as Range
Dim Dest as Range
Set Source = Range("B2:B10")
Set Dest = Range("G2")
Sourcel.copy Dest
(Here is set the dest as the first cell. The above will paste B2:B10 in
G2:G10)

Then you can start specifying the Sheets and workbooks, if they are different
from where the source range is:

Set Source=ThisWorkbook.sheets("Sheet1").Range("B2:B10")
Set Dest =Workbooks("MyOtherBook.xls").Sheets("Customers").Range("GoodGuys")

To get started learning vba I would take a multi level approach:
A. a book such as John Walkenbach's Power Programming with VBA
B. Use the recorder feature that comes with Excel and study the resulting
code. Play it back one line at a time (Debug it) and watch what each line of
code does.
A note of caution: the recorder often creates many lines of code that are
not necessary.
C. Read forums such as this one or Woody's lounge:
http://www.wopr.com/cgi-bin/w3t/postlist.pl?Cat=&Board=xl
Copy and paste some of the code into a module in a new workbook on
your computer and then debug it (click F8 to step through the code one line
at a time)
while the worksheet is visible so you can see what is happening.

D. Start writing your own code. As you get stumped, post a question (but
make each post a single question and be specific) in a forum like this one or
Woody's
There are a lot of great people willing to help, but they need questions
that are short and direct. If you are too general or vague or improperly
state what you want to achieve, the helpers don't know where to begin.

BTW (By the way) Woody's has a great added feature: you can submit a SHORT
workbook (as long as you delete all personal and proprietary data). Often,
when it would take paragraphs to explain, you can attach a file that is
similar to the real one so others can easily see what you are wanting. (1
Picture=1000 words)

Hope this helps

 

[markx]

Thanks once again Cush,
I don't know how to thank you for all these valuable comments!
I think I understand now much better what's going on with all this VBA
stuff...
Cheers,