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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jan 18 '19 at 15:48

Pᴇʜ · Answer 1 · 2019-01-18T15:58:14.380

0

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.

edited Jan 18 '19 at 15:58

answered Jan 18 '19 at 15:52

Pᴇʜ

56,719
10
49
73

the first row is A1 which is a merged field, when i try the code it says "sorry we cant do that to a merged cell"? – Khush Jan 18 '19 at 16:30
instead of `.Range("A3", .Cells(.Rows.Count, "A").End(xlUp)).Copy` try `.Range("A3", "A" & Application.Max(.Cells(.Rows.Count, "A").End(xlUp).Row, 3)).Copy` – Pᴇʜ Jan 18 '19 at 16:35
it's still giving the error "RunTime error '1004' We can't do that to a merged cell.".. is there anyway of just specifying the rows that I want to copy, rather than just copying the whole of e.g. column A? but i want to also include the empty cells in the copy function – Khush Jan 18 '19 at 16:43

MS VB Copy from 1 worksheet to another with empty cells

1 Answers1