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