Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro - copy cell value to a specific cell in another worksheet based on cell number INDIRECT

    Hello,

    I'm trying to set up a macro that will copy the VALUE of certain cells from one worksheet into specific cells in the first blank row in other worksheets.

    For instance, B2 value could need to go to Bride!C200 - that is, the first blank row.
    B8 value would then need to go to 'General details'!R200.
    B25 value would need to go to Groom!I200.

    ... and so on.

    I'm using the following (formula in C2) to find the number of the first blank row:
    Code:
    =MIN(IF(Bride!C2:C8000="",ROW(Bride!C2:C8000),""))
    I thought I could use the resulting row number with INDIRECT to create a macro that would do what I need, but I am sitting here feeling as though my mind has turned to slush with tiredness...

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    You could use the formula

    AvailableRow = Range("A1").End(xlDown).Row + 1

    To find the next available row. The above example uses column A as the index column to search down for the first available space. Replacing with "B1" uses the second column. Replacing with "C5" searches down column 3 starting at row 5.

  4. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Some of the cells have formulae within them, which I think (I may be wrong) precludes using AvailableRow.

    The other problem is that some of the worksheets may well have blank cells in an earlier row because I may be waiting for my clients to provide additional information - or they haven't signed some documentation yet (which means the relevant date cells need to remain blank until they have signed) - or I haven't sighted some documentation yet (so those cells need to remain blank until I have sighted it). That's why I thought the obvious first step was to find the first blank cell for the bride's first name in that particular worksheet; obviously if I've got no bride's name in a row in the 'Bride' worksheet, then that is the first available row.

    When I am transferring information from a form emailed to me by clients, I want to automatically copy the information they've sent into the appropriate cells (and it involves several different worksheets). Let's say that I've already filled 300 rows (300 previous clients); the new client information then has to go into row 302 (first row being the header). I suppose I want - if it works - the VBA equivalent of
    Code:
    INDIRECT("Bride!C"&'form'!F2)
    as the destination for the first paste, and
    Code:
    INDIRECT("'General details'!I"&'form'!F2)
    as the destination for the second paste, and so on.

    Is there a way to achieve this as a macro?

    (I will be changing the information in the 'form' worksheet, column A, as new clients email the form to me. So the information won't be fixed (only the TYPE and FORMAT of information remains constant), and of course the number in cell F2 (in the 'form' worksheet) would change every time a new client is added, and the destination cells are therefore dynamic with regard to row (but the data for one client needs to be on the same row across all the worksheets).

    I'm probably making this seem too complicated...!

  5. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    Could this be what you are looking for?

    Following your opening post, this code will do the following:
    If you enter a values on sheet1 in cells B2 (bride information), B8 (general information), or B25 (groom information), it will automatically transfer that value to the first available cell in column 3 on the appropriate sheet. Change the ranges for the columns you desire. Place code in the sheet 1 module

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '------------------------------------
    'DECLASE AND SET VARIABLES
        Dim Rng As Range
        Dim cell As Range
    '------------------------------------
    'SET RANGE AND DATA ELEMENT
        Select Case Target.Address
            Case "$B$2"
                Set Rng = Worksheets("Bride").Range("C2:C5000")
                Data = Range("B2")
            Case "$B$8"
                Set Rng = Worksheets("General details").Range("C2:C5000")
                Data = Range("B8")
            Case "$B$25"
                Set Rng = Worksheets("Groom").Range("C2:C5000")
                Data = Range("B25")
            Case Else
                Exit Sub
        End Select
    '------------------------------------
    'TRANSFER DATA TO NEXT AVAILABLE ROW
        For Each cell In Rng
            If cell = "" Then
                cell = Data
                Exit Sub
            End If
        Next cell
    End Sub
    Last edited by Maudibe; 2014-08-06 at 02:01.

Posting Permissions

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