Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Macro to move data from one sheet to another

    I have a workbook sheet that has misc. info filled in various cells. This workbook serves as a template of sorts.
    I'd like to have a macro button that when clicked will take certain bits of info from this "template" and move that information to another sheet in the "next" available row beginning with 33 and incrementing by 2 (33, 35, etc.).

    For example, if the button is clicked, I want to move data from the template workbook (say, cells A1 and Q10) to the first available slot in the other workbook...in the first case, to the other workbook in cells A33 and B33.

    Then, the template should CLEAR cells A1 and Q10 (in this example). (I suspect a copy/paste special values is needed) Then, new info is entered into the templete, the button is clicked and the other workbook is updated in row 35, cols A and B. This can be done 6 times.

    I can't seem to record the right sequence. I suspect it needs an OFFSET written into the code, but am not sure and not sure how to do that.

    If something can be written, I can alter the macro for the various cells I want to move and clear.

    Thanks in advance.
    Last edited by kweaver; 2012-07-02 at 19:58.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    Here's some code that may work for you. It does require you to SELECT the cells to be moved first then run the macro. It does however allow you to move as many cells as you wish each time and places them in the next available row from Col A on one after the other. Hope this helps.
    Code:
    Option Explicit
    
    Sub MoveData()
    
       Dim lMoveRow As Long
       Dim lCntr    As Long
       Dim lSACount As Long
       
       lMoveRow = 33
       
       Do
          If Worksheets("Target").Cells(lMoveRow, 1) = "" Then Exit Do
          lMoveRow = lMoveRow + 2
       Loop
       
      
       For lCntr = 1 To Selection.Areas.Count
       
          Worksheets("Target").Cells(lMoveRow, lCntr).Value = _
                 Selection.Areas(lCntr).Value
          Selection.Areas(lCntr).ClearContents  'Delete Value
          
       Next lCntr
       
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Always a help. Thanks. I can modify that.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG: maybe you can solve this macro one for me, too.

    On one sheet in a workbook, I have a set of rows and 2 columns (e.g., B23:C28). On another sheet in the same workbook I have the values in from B23:B28 in columns A2:A7.

    I want a macro that first makes the rows A2:A7 BLANK if there's an "x" in C23:C28, then sorts A2:B7 by column A (no heading).

    Is that easy in VB?

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    I'm don't quite understand what you are asking. Are you saying that if there is an "x" in C24 then you want Row 3 blanked on the other sheet? A sample workbook with before and after sheets would be useful.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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