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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,931
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,791
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,791
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,931
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,251
Messages
5,467,547
Members
406,544
Latest member
semoredhawk

This Week's Hot Topics

Top