+ Reply to Thread
Results 1 to 19 of 19

Transfer cells range to another sheet if first cell of destination range is empty

Hybrid View

kopapa Transfer cells range to... 10-23-2013, 03:30 AM
JOHN H. DAVIS Re: Transfer cells range to... 10-23-2013, 06:57 AM
kopapa Re: Transfer cells range to... 10-23-2013, 12:45 PM
JOHN H. DAVIS Re: Transfer cells range to... 10-23-2013, 01:11 PM
kopapa Re: Transfer cells range to... 10-23-2013, 01:26 PM
  1. #1
    Registered User
    Join Date
    02-01-2013
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    28

    Transfer cells range to another sheet if first cell of destination range is empty

    Howdy all!

    I've got a project which is quite complex (at least for me). So, I'm taking it one step at a time
    The first step is creating a macro evoked by a push button (which is already in place) which will achieve the following:

    Sheet1 is the source worksheet
    Sheet2 is the destination sheet

    With the push of the macro button (present in Sheet1) I want to calculate formulas in specific cells (B4, C4, B6, L61) AND
    copy the cells' values to specific cells in Sheet2 (M10, N10, O10, S10) BUT only if the FIRST cell of this range (i.e. M10) is empty. If it contains
    data then paste the values to the cells below (M11, N11, O11, S11) etc etc. The "check for empty cell" routine is necessary because the macro will be evoked

    continuously (push the button on Sheet1 - Calculate - Transfer to empty cells on Sheet2 - Loop)

    Any help will be greatly appreciated!
    Thanks!

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Transfer cells range to another sheet if first cell of destination range is empty

    Does this help?

    Sub kopapa()
    If Sheets("Sheet2").Range("M" & Rows.count).End(3)(2).Value = "" Then
        Sheets("Sheet2").Range("M" & Rows.count).End(3)(2).Value = Sheets("Sheet1").Range("B4").Value
        Sheets("Sheet2").Range("N" & Rows.count).End(3)(2).Value = Sheets("Sheet1").Range("C4").Value
        Sheets("Sheet2").Range("O" & Rows.count).End(3)(2).Value = Sheets("Sheet1").Range("B6").Value
        Sheets("Sheet2").Range("S" & Rows.count).End(3)(2).Value = Sheets("Sheet1").Range("L61").Value
    End If
    End Sub
    It assumes you already have data in M9 on sheet2.

  3. #3
    Registered User
    Join Date
    02-01-2013
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Transfer cells range to another sheet if first cell of destination range is empty

    Well, I think it does!
    I put it in, I checked and everything seems to be OK.
    So many thanks for your precious help!
    First step is (more or less) gone... 99 to go!

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Transfer cells range to another sheet if first cell of destination range is empty

    You're welcome. Glad to help out and thanks for the feedback. In accordance with Forum Rule No. 9, please mark this thread as solved. Also, good luck on the other 99.

  5. #5
    Registered User
    Join Date
    02-01-2013
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Transfer cells range to another sheet if first cell of destination range is empty

    Quote Originally Posted by JOHN H. DAVIS View Post
    You're welcome. Glad to help out and thanks for the feedback. In accordance with Forum Rule No. 9, please mark this thread as solved. Also, good luck on the other 99.

    Hmm, I think I rushed a little bit.
    I seemed to work OK BUT when I try to clear the destination cells (either manually or by code) so that I can start over, it seems to glitch...
    It starts again in M10, copies the first two cells (B4 and C4) to M10 & N10 and ignores the other two (B6 and L61)...
    Any thoughts?

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Transfer cells range to another sheet if first cell of destination range is empty

    Can you provide a sample?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If the range A1:F1 and the cell H1 are not empty, then all range cells must be not em
    By loroverde in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2013, 04:13 AM
  2. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM
  3. Replies: 0
    Last Post: 09-12-2012, 02:12 AM
  4. Moving text from one range of cells to an empty row in the same sheet:
    By ashleyjean in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-11-2012, 07:24 PM
  5. Live Transfer a range of cell values to another Sheet or to another Workbook
    By phstol in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2009, 12:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1