MS VB Copy from 1 worksheet to another with empty cells

Question

I have a MS Visual Basic macro for my excel sheet, I have a child workbook and a parent workbook. I want to copy the cells from the child worksheet "account" into the parent worksheet "account". the cells in the child sheet have some blank cells, currently with this code, it stops at the blank cell, I want it to miss the blank cell and go to the next cell with values and then keep copying.

    Sub Button1_Click()
    'Field Name
    Windows("childsheet.xlsm").Activate
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("parentsheet.xlsm").Activate
    Range("A3").Select
    ActiveSheet.Paste

    'API Name
    Windows("childsheet.xlsm").Activate
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("parentsheet.xlsm").Activate
    Range("B3").Select
    ActiveSheet.Paste

'Type
Windows("childsheet.xlsm").Activate
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("C3").Select
ActiveSheet.Paste

'Length
Windows("childsheet.xlsm").Activate
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("D3").Select
ActiveSheet.Paste

'Required
Windows("childsheet.xlsm").Activate
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("E3").Select
ActiveSheet.Paste

'Read Only?
Windows("childsheet.xlsm").Activate
Range("F3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
    Range("F3").Select
    ActiveSheet.Paste
    End Sub

it works. it copies each column that i specify but when it gets to a column that has empty cells it copy any info. in that cell from top to bottom but if it encounters a blank it stops there and then moves onto the next column. I want it to copy all info.

You might benefit from reading How to avoid using Select in Excel VBA — Pᴇʜ

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-01-18T15:52:51

Instead of

Windows("childsheet.xlsm").Activate
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("A3").Select
ActiveSheet.Paste

Use something like

With Workbooks("childsheet.xlsm").ActiveSheet
    .Range("A3", .Cells(.Rows.Count, "A").End(xlUp)).Copy Destination:=Workbooks("parentsheet.xlsm").ActiveSheet.Range("A3")
    .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)).Copy Destination:=Workbooks("parentsheet.xlsm").ActiveSheet.Range("B3")
    '… and so on …
End With

A further improvement is to replace .ActiveSheet with the sheet name like .Worksheets("YourSheetName") so your code is more reliable.

MS VB Copy from 1 worksheet to another with empty cells

1 Answers