transpose the code from "rows" to "columns"

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" wrote:

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

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" <cush@discussions.microsoft.com> wrote in message
news:4C150364-B90D-4B7B-858F-A1849AB7B414@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>>

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/post...?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" wrote:

> 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" <cush@discussions.microsoft.com> wrote in message
> news:4C150364-B90D-4B7B-858F-A1849AB7B414@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>

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,

"cush" <cush@discussions.microsoft.com> wrote in message
news:FCC241A3-191D-471A-BA3F-9F64F1EF7565@microsoft.com...
> 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/post...?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" wrote:
>
>> 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" <cush@discussions.microsoft.com> wrote in message
>> news:4C150364-B90D-4B7B-858F-A1849AB7B414@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>>
>>
>>