VBA: Copying values until empty cell is reached

[iand5]

I currently have the following code to copy values from one workbook to another. It copies from cells B1:B186.

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.

 

[JLGWhiz]

Replace the statement in the OP with the code below

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

 

[iand5]

Replace the statement in the OP with the code below

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]

There's a typo. Missing a . from cells

.Range("B1", [COLOR=#ff0000].[/COLOR]Cells(Rows.Count, 2).End(xlUp)).Value

 

[iand5]

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

I get an object defined error on this line.

 

[Fluff]

The code that JLGWhiz works fine for me (after adding the .).
Could you post the entire code

 

[iand5]

The code that JLGWhiz works fine for me (after adding the .).
Could you post the entire code

I currently have this:

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):

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

 

[JLGWhiz]

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.