+ Reply to Thread
Results 1 to 4 of 4

New to Macro writing

  1. #1
    Going in Circles
    Guest

    New to Macro writing

    I have read thru several articles on this site to get started writing Macros.
    To get started I thought I would read some lines and paste them at the
    bottom of the existing sheet to test my understanding (which is obviously
    zip) I can't even get started. Here is what I wrote (one of many versions).

    Sub Readcopylines
    Worksheets("sheet6").Activate
    Set currentline=worksheet.row(3).Activate
    Dim counter
    counter=3
    Do While counter <>12
    currentline.Paste(Range("counter+15:counter+15"))
    Range("A,counter+16") = "next"
    counter = counter +2
    Loop
    End Sub

    If someone could list the things I am doing wrong, I will go back and read
    some more. Is there a good source of information to get started writing? I
    feel completely frustrated with my abilty to get started on this.

    Pam





  2. #2
    Tom Ogilvy
    Guest

    RE: New to Macro writing

    Option Explicit
    Sub Readcopylines()
    Dim currentLine As Range
    Dim counter As Long
    Worksheets("sheet6").Activate
    Set currentLine = Rows(3) '.Activate
    ' currentline now references row 3
    counter = 3
    ' if you start with 3 and count by 2
    ' your number will never = 12, so you
    ' will loop to the bottom of the page
    ' I assumed you don't want to exceed 12
    Do While counter < 12
    Rows(counter + 15).Value = currentLine.Value
    Range("A" & counter + 16) = "next"
    counter = counter + 2
    Loop
    End Sub

    Hard to tell what you really are trying to do, but run this and see what you
    get.

    Make sure there is some data in Row 3 of sheet 6.

    --
    Regards,
    Tom Ogilvy






    "Going in Circles" wrote:

    > I have read thru several articles on this site to get started writing Macros.
    > To get started I thought I would read some lines and paste them at the
    > bottom of the existing sheet to test my understanding (which is obviously
    > zip) I can't even get started. Here is what I wrote (one of many versions).
    >
    > Sub Readcopylines
    > Worksheets("sheet6").Activate
    > Set currentline=worksheet.row(3).Activate
    > Dim counter
    > counter=3
    > Do While counter <>12
    > currentline.Paste(Range("counter+15:counter+15"))
    > Range("A,counter+16") = "next"
    > counter = counter +2
    > Loop
    > End Sub
    >
    > If someone could list the things I am doing wrong, I will go back and read
    > some more. Is there a good source of information to get started writing? I
    > feel completely frustrated with my abilty to get started on this.
    >
    > Pam
    >
    >
    >
    >


  3. #3
    Going in Circles
    Guest

    RE: New to Macro writing

    Thank you for the quick response. When I try the macro suggested I get a
    "run time error 9" You said it was hard to determine what I was trying to
    do....because the language is off or because you can't see the file I am
    working with?

    To give you a better picture of what I am trying to do...
    I have an excel file with column headers-current date,Month,Name,
    contact,arrival, departure, volume,price and total revenue. I eventually
    want the macro to read the lines, determine by the value in the Month column
    what sheet(one sheet for each month) to copy that line to, go to that sheet,
    print that line, then go back and read the next line and continue.
    I have not written a macro before so I thought I would do things a piece at
    a time. First, I wanted to know if the macro was reading the lines in order
    and to determine this I was going to have it print the line at the bottom.

    I know no one can spend a lot of time helping here but I want to learn and
    am at a bit of a loss on where to turn to next. I thought if I could get
    this maco stuff one bit at a time I could learn it that way. Is there a
    better forum for this?

    "Tom Ogilvy" wrote:

    > Option Explicit
    > Sub Readcopylines()
    > Dim currentLine As Range
    > Dim counter As Long
    > Worksheets("sheet6").Activate
    > Set currentLine = Rows(3) '.Activate
    > ' currentline now references row 3
    > counter = 3
    > ' if you start with 3 and count by 2
    > ' your number will never = 12, so you
    > ' will loop to the bottom of the page
    > ' I assumed you don't want to exceed 12
    > Do While counter < 12
    > Rows(counter + 15).Value = currentLine.Value
    > Range("A" & counter + 16) = "next"
    > counter = counter + 2
    > Loop
    > End Sub
    >
    > Hard to tell what you really are trying to do, but run this and see what you
    > get.
    >
    > Make sure there is some data in Row 3 of sheet 6.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    >
    >
    >
    > "Going in Circles" wrote:
    >
    > > I have read thru several articles on this site to get started writing Macros.
    > > To get started I thought I would read some lines and paste them at the
    > > bottom of the existing sheet to test my understanding (which is obviously
    > > zip) I can't even get started. Here is what I wrote (one of many versions).
    > >
    > > Sub Readcopylines
    > > Worksheets("sheet6").Activate
    > > Set currentline=worksheet.row(3).Activate
    > > Dim counter
    > > counter=3
    > > Do While counter <>12
    > > currentline.Paste(Range("counter+15:counter+15"))
    > > Range("A,counter+16") = "next"
    > > counter = counter +2
    > > Loop
    > > End Sub
    > >
    > > If someone could list the things I am doing wrong, I will go back and read
    > > some more. Is there a good source of information to get started writing? I
    > > feel completely frustrated with my abilty to get started on this.
    > >
    > > Pam
    > >
    > >
    > >
    > >


  4. #4
    Tom Ogilvy
    Guest

    Re: New to Macro writing

    Runtime error 9 is subscript out of range. That means you don't have a
    sheet6. If you think you have a Sheet6, perhaps the name has a space on the
    end. In any event, that wasn't anything I changed and I can assure you the
    code ran several times for me (I made a sheet named Sheet6).

    As written, you code never moved off row 3 when doing the copying. If you
    want to copy lines 3 to 12, you would do

    With worksheets("Sheet6")
    for i = 3 to 12
    'copy columns A:K
    .Cells(i,1).Resize(1,11).copy _
    Destination:= .Cells(i+15,1)
    next
    End with

    Assume the sheet name of the destination is in column F and the copied row
    should go to the next blank row as determined by checking column A on the
    destination sheet.

    With worksheets("Sheet6")
    for i = 3 to 12
    'copy columns A:K
    .cells(i,1).Resize(1,11).copy _
    Destination:=Worksheets(.cells(i,"F")) _
    .Cells(rows.count,1).Offset(1,0)
    next
    End with

    --
    Regards,
    Tom Ogilvy



    "Going in Circles" <[email protected]> wrote in
    message news:[email protected]...
    > Thank you for the quick response. When I try the macro suggested I get a
    > "run time error 9" You said it was hard to determine what I was trying to
    > do....because the language is off or because you can't see the file I am
    > working with?
    >
    > To give you a better picture of what I am trying to do...
    > I have an excel file with column headers-current date,Month,Name,
    > contact,arrival, departure, volume,price and total revenue. I eventually
    > want the macro to read the lines, determine by the value in the Month

    column
    > what sheet(one sheet for each month) to copy that line to, go to that

    sheet,
    > print that line, then go back and read the next line and continue.
    > I have not written a macro before so I thought I would do things a piece

    at
    > a time. First, I wanted to know if the macro was reading the lines in

    order
    > and to determine this I was going to have it print the line at the bottom.
    >
    > I know no one can spend a lot of time helping here but I want to learn and
    > am at a bit of a loss on where to turn to next. I thought if I could get
    > this maco stuff one bit at a time I could learn it that way. Is there a
    > better forum for this?
    >
    > "Tom Ogilvy" wrote:
    >
    > > Option Explicit
    > > Sub Readcopylines()
    > > Dim currentLine As Range
    > > Dim counter As Long
    > > Worksheets("sheet6").Activate
    > > Set currentLine = Rows(3) '.Activate
    > > ' currentline now references row 3
    > > counter = 3
    > > ' if you start with 3 and count by 2
    > > ' your number will never = 12, so you
    > > ' will loop to the bottom of the page
    > > ' I assumed you don't want to exceed 12
    > > Do While counter < 12
    > > Rows(counter + 15).Value = currentLine.Value
    > > Range("A" & counter + 16) = "next"
    > > counter = counter + 2
    > > Loop
    > > End Sub
    > >
    > > Hard to tell what you really are trying to do, but run this and see what

    you
    > > get.
    > >
    > > Make sure there is some data in Row 3 of sheet 6.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > >
    > >
    > >
    > >
    > > "Going in Circles" wrote:
    > >
    > > > I have read thru several articles on this site to get started writing

    Macros.
    > > > To get started I thought I would read some lines and paste them at

    the
    > > > bottom of the existing sheet to test my understanding (which is

    obviously
    > > > zip) I can't even get started. Here is what I wrote (one of many

    versions).
    > > >
    > > > Sub Readcopylines
    > > > Worksheets("sheet6").Activate
    > > > Set currentline=worksheet.row(3).Activate
    > > > Dim counter
    > > > counter=3
    > > > Do While counter <>12
    > > > currentline.Paste(Range("counter+15:counter+15"))
    > > > Range("A,counter+16") = "next"
    > > > counter = counter +2
    > > > Loop
    > > > End Sub
    > > >
    > > > If someone could list the things I am doing wrong, I will go back and

    read
    > > > some more. Is there a good source of information to get started

    writing? I
    > > > feel completely frustrated with my abilty to get started on this.
    > > >
    > > > Pam
    > > >
    > > >
    > > >
    > > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1