Results 1 to 8 of 8

Thread: VBA: Copying values until empty cell is reached
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Copying values until empty cell is reached

    I currently have the following code to copy values from one workbook to another. It copies from cells B1:B186.
    Code:
    Workbooks("Placeholder.xlsm").Sheets("Sheet1").Range("A1:A186").Value = Workbooks("Finances.xlsm").Sheets("Sheet1").Range("B1:B186").Value
    In the case where I don't know how many cells I want to copy. How can I copy all cells until a blank cell is found?


    TIA.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,931
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA: Copying values until empty cell is reached

    Replace the statement in the OP with the code below

    Code:
    Dim cnt As Long 'add this to your declarations area
    With Workbooks("Finances.xlsm").Sheets("Sheet1")
        cnt = .Range("B1", .Cells(Rows.Count, 2).End(xlUp)).Rows.Count
        Workbooks("Placeholder.xlsm").Sheets("Sheet1").Range("A1").Resize(cnt).Value = _
        .Range("B1", Cells(Rows.Count, 2).End(xlUp)).Value
    End With
    Last edited by JLGWhiz; Aug 16th, 2018 at 01:46 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Jul 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copying values until empty cell is reached

    Quote Originally Posted by JLGWhiz View Post
    Replace the statement in the OP with the code below

    Code:
    Dim cnt As Long 'add this to your declarations area
    With Workbooks("Finances.xlsm").Sheets("Sheet1")
        cnt = .Range("B1", .Cells(Rows.Count, 2).End(xlUp)).Rows.Count
        Workbooks("Placeholder.xlsm").Sheets("Sheet1").Range("A1").Resize(cnt).Value = _
        .Range("B1", Cells(Rows.Count, 2).End(xlUp)).Value
    End With
    I get a 'subscript out of range' error even though I have both worksheets open.

    Any suggestions?


    Thanks.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,006
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: VBA: Copying values until empty cell is reached

    There's a typo. Missing a . from cells
    Code:
    .Range("B1", .Cells(Rows.Count, 2).End(xlUp)).Value
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jul 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copying values until empty cell is reached

    cnt = .Range("B1", .Cells(Rows.Count, 2).End(xlUp)).Rows.Count

    I get an object defined error on this line.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,006
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: VBA: Copying values until empty cell is reached

    The code that JLGWhiz works fine for me (after adding the .).
    Could you post the entire code
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Jul 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copying values until empty cell is reached

    Quote Originally Posted by Fluff View Post
    The code that JLGWhiz works fine for me (after adding the .).
    Could you post the entire code
    I currently have this:

    Code:
    Workbooks("Outages_Placeholder.xlsm").Sheets("Sheet1").Range("C1:C186").Value = Workbooks("TOA Copy of REQ_01_18_18_To_03_31_18.xls").Sheets("Outage Request List").Range("E1:E186").Value
    I want to turn it into this (Don't know if it's correct):

    Code:
    Dim cnt As Long 'add this to your declarations area
    With Workbooks("TOA Copy of REQ_01_18_18_To_03_31_18.xls").Sheets("Outage Request List")
        cnt = .Range("E1", .Cells(Rows.Count, 2).End(xlUp)).Rows.Count
        Workbooks("Outages_Placeholder_EMPTYCell.xlsm").Sheets("Sheet1").Range("C1").Resize(cnt).Value = _
        .Range("E1", .Cells(Rows.Count, 2).End(xlUp)).Value
    End With
    Last edited by iand5; Aug 16th, 2018 at 04:43 PM.

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,931
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA: Copying values until empty cell is reached

    Code:
    With Workbooks("TOA Copy of REQ_01_18_18_To_03_31_18.xls").Sheets("Outage Request List")
        cnt = .Range("E1", .Cells(Rows.Count, 5).End(xlUp)).Rows.Count
        Workbooks("Outages_Placeholder_EMPTYCell.xlsm").Sheets("Sheet1").Range("C1").Resize(cnt).Value = _
        .Range("E1", .Cells(Rows.Count, 5).End(xlUp)).Value
    End With
    If you change your column letter, you need to also change the column number.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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