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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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:
Upvote 0
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.
 
Upvote 0
There's a typo. Missing a . from cells
Code:
.Range("B1", [COLOR=#ff0000].[/COLOR]Cells(Rows.Count, 2).End(xlUp)).Value
 
Upvote 0
cnt = .Range("B1", .Cells(Rows.Count, 2).End(xlUp)).Rows.Count

I get an object defined error on this line.
 
Upvote 0
The code that JLGWhiz works fine for me (after adding the .).
Could you post the entire code
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top