VBA: Copying values until empty cell is reached

iand5

New Member
Joined
Jul 26, 2017
Messages
36
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,934
Office Version
2013
Platform
Windows
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:

iand5

New Member
Joined
Jul 26, 2017
Messages
36
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
There's a typo. Missing a . from cells
Code:
.Range("B1", [COLOR=#ff0000].[/COLOR]Cells(Rows.Count, 2).End(xlUp)).Value
 

iand5

New Member
Joined
Jul 26, 2017
Messages
36
cnt = .Range("B1", .Cells(Rows.Count, 2).End(xlUp)).Rows.Count

I get an object defined error on this line.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
The code that JLGWhiz works fine for me (after adding the .).
Could you post the entire code
 

iand5

New Member
Joined
Jul 26, 2017
Messages
36
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:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,934
Office Version
2013
Platform
Windows
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, [COLOR=#FF0000]5[/COLOR]).End(xlUp)).Rows.Count
    Workbooks("Outages_Placeholder_EMPTYCell.xlsm").Sheets("Sheet1").Range("C1").Resize(cnt).Value = _
    .Range("E1", .Cells(Rows.Count, [COLOR=#FF0000]5[/COLOR]).End(xlUp)).Value
End With
If you change your column letter, you need to also change the column number.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,578
Messages
5,469,498
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top