New to Macro writing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Going in Circles - 22 Mar 2006 20:09 GMT

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

Tom Ogilvy - 22 Mar 2006 20:48 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 18 lines]
>
> Pam

Reply to this Message

Going in Circles - 22 Mar 2006 21:52 GMT

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?

> Option Explicit
> Sub Readcopylines()
[quoted text clipped - 42 lines]
> >
> > Pam

Reply to this Message

Tom Ogilvy - 23 Mar 2006 05:24 GMT

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 62 lines]
> > >
> > > Pam

Reply to this Message