Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    17

    Unanswered: VBA Method: Moving Data from One Sheet to Another

    I am trying to evaluate data in sheet1 and if it fits a certain criteria, do a little math and move the results to a corresponding row in sheet2.

    The data is in rows in sheet 1, and I would like the results to be in rows in sheet2 as well.

    My VBA is rudimentary. So far, I've been brute-forcing my way around spreadsheets using ActiveCell.Offset.

    But now I want to move around two spreadsheets at once. After evaluating a record in sheet1 and writing to sheet2 I want to move down one row in each.

    What method do you recommend for this?

    Thank you.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Don't use ActiveCell.

    Use Worksheet.Cells([row], [column]), and use variables that you increment in the code for [row] and [column]. Then you instantly have a handle on where the the results need to go in the other sheet.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Aug 2011
    Posts
    17
    Perfect! That works. Thanks.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You're welcome! Good to know it was useful
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Aug 2011
    Posts
    17
    I'm stuck again. :-/ What is the syntax for using variables instead of numbers? My code worked until I replaced the numbers with variables.

    I dim r10 etc as integer
    Then I set them equal to values

    Example code for use is:

    initialsum = Sheet10.Cells(r10, ci10)
    primingsum = Sheet10.Cells(r10, cpr10)

    But I'm getting error 1004 on that. Is my syntax off?

  6. #6
    Join Date
    Aug 2011
    Posts
    17
    I think I found the error.

    When I assigned values to the variables I tried doing two at a time:
    c10 = c11 = 3

    This doesn't work, although it doesn't either give me an error. Is there a way to assign a value to multiple variables at a time?

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    No. You have to assign variables one at a time, although it's not usually an onerous task to do that.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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