Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Until Done (Excel 97 SR2)

    In the attached spreadsheet, I need to copy cells E through P and R through V for each row that has a total in Q AND put that information in the next available row in the upload tab starting in F3. It would have to exclude the rows for TOTAL REVENUE, EXPENSE, and NET.

    There could be more/less than 55 rows if the user inserts/deletes rows.

    Also, I need to do this for each budget sheet in the workbook, the number of which could vary by user.

    The Upload tab is formatted for uploading to our GL and can
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Sherry,

    Does this code work?


    Sub TransferData()

    Dim PickRowVal As Double
    Dim PutRowVal As Double
    Dim testme As Variant
    PickRowVal = 12
    PutRowVal = 3

    Do While Worksheets("budget 1").Cells(PickRowVal, 1).Value <> "TOTAL REVENUE"

    testme = 0 + Worksheets("budget 1").Cells(PickRowVal, 17).Value

    If testme <> 0 Then

    Worksheets("budget 1").Select
    Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Worksheets("budget 1").Select
    Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    PutRowVal = PutRowVal + 1

    End If

    PickRowVal = PickRowVal + 1
    Loop


    Do While Worksheets("budget 1").Cells(PickRowVal, 1).Value <> "EXPENSE"
    PickRowVal = PickRowVal + 1
    Loop


    Do While Worksheets("budget 1").Cells(PickRowVal, 1).Value <> "TOTAL EXPENSE"

    testme = 0 + Worksheets("budget 1").Cells(PickRowVal, 17).Value

    If testme <> 0 Then

    Worksheets("budget 1").Select
    Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Worksheets("budget 1").Select
    Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    PutRowVal = PutRowVal + 1

    End If

    PickRowVal = PickRowVal + 1
    Loop


    End Sub





    I would however, be careful with the condition for transferring a line - as I'm sure you're aware, a full year budget of zero does not mean there is no movement on the account.

    Brooke

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Brooke,

    It works like a charm, and I understand MOST of it. I follow everything that happens up to the first LOOP. And I understand the third DO..LOOP. What I don
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Try the following: it seemed to work ok. It's kind of you to comment on the efficiency, but there is a fair bit of tidying up that could be done here.


    Brooke



    Sub TransferData()

    Dim PickRowVal As Double
    Dim PutRowVal As Double
    Dim testme As Variant
    Dim ws As Worksheet

    PutRowVal = 3

    For Each ws In Worksheets
    Msgbox ws.Name
    If ws.Name <> "upload" Then

    PickRowVal = 12

    Do While ws.Cells(PickRowVal, 1).Value <> "TOTAL REVENUE"

    testme = 0 + ws.Cells(PickRowVal, 17).Value

    If testme <> 0 Then

    ws.Select
    Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ws.Select
    Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    PutRowVal = PutRowVal + 1

    End If

    PickRowVal = PickRowVal + 1
    Loop


    Do While ws.Cells(PickRowVal, 1).Value <> "EXPENSE"
    PickRowVal = PickRowVal + 1
    Loop


    Do While ws.Cells(PickRowVal, 1).Value <> "TOTAL EXPENSE"

    testme = 0 + ws.Cells(PickRowVal, 17).Value

    If testme <> 0 Then

    ws.Select
    Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ws.Select
    Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
    Worksheets("upload").Select
    Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    PutRowVal = PutRowVal + 1

    End If

    PickRowVal = PickRowVal + 1
    Loop

    End If

    Next




    End Sub

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Brooke,

    It works great AND I have learned something!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thanks a lot!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    No problems. I didn't answer your question though - i put the middle loop in purely because you said users might be adding or deleting rows and so needed to be sure that I was in the right place before starting copying data over again.



    Brooke

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    I think I'm a little fuzzy on the DO..LOOP statements. Will a LOOP continue indefinitely if there isn
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    No and yes to the first question. I haven't got an exit loop in there, but it exits when the condition after while is met. To illustrate this, when I wrote the above, for my first trial I had forgotten to add the line pickrowval = pickrowval + 1, so after a minute or two with the code still running, I knew I'd done something wrong. pickrowval was still at 12 and so the condition was never met and the loop would run for ever.




    for the second question, the basic structure here is

    Do While (condition)
    (do stuff)
    loop

    so each loop finishes before the next one starts. However, you can nest loops, eg

    <pre>Do While (condition)
    (do stuff)
    Do While (condition)
    (do stuff)
    Do While (condition)
    (do stuff)
    loop
    loop
    loop

    </pre>


    but here I haven't.

    There are other ways of looping eg

    For Each letter In alphabet
    ....
    Next

    For letter = a To z
    .....
    Next

    and I didn't necessarily choose the best method here.



    HTH

    Brooke

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Amazing, I understand <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>. I really do.

    Thanks a million!!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Hi All,

    Just a very picky point, ignore if you wish. If PickRowVal and PickRowPut are row numbers (versus cell values), it is a bit much to declare them as Double (double-precision floating point). A better choice would be Long (long integer), which can still account for the maximum number of rows in a sheet.

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Your point is valid. I just use the rule of thumb that if it's a number then it's a double. I guess I picked that up from a thread here back around march/april (ish) where the advantages of using different data-types was discussed. Only after the code is written and done the job and there's an outside chance that I might keep it and reuse it in the future do I even think about cleaning it up - and it very rarely happens even then!

    Brooke

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    I only vaguely understand why you declare a variable as a certain type. I looked up the Data Types in VB Help, but I can
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Until Done (Excel 97 SR2)

    Technically speaking it should do but when I pulled the code together for you it was falling over - I wanted to see what value testme actually was - by putting that line in and pausing the code I could see what value it held - (it was coming up empty for some reason) similarly, the 0 + testme is my way of forcing a type conversion to make sure the result is numeric - you can do this by Cdbl(testme) but old habits die hard.......

    Brooke

Posting Permissions

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