Copying columns including blanks without skipping rows..leave "blanks" blank VBA

Question

Aplication Defined error Copying a specified column and range including blanks with an embedded button running multiple Macros. I know that all rows will be filled in column A so if I could reference the rest of the Macros to A.end

I've looked Google youtube and here although there is a lot of info on copying and pasting, I cannot find one that works for this running multiple Macros.

Macros 5 & 6 is where I start having problems because these columns have multiple blanks throughout.

Raw data to Copy:

Sheet1

Destination:

Sheet2

Private Sub CommandButton1_Click()

Worksheets("Sheet1").Range("a2", Range("a2").End(xlDown)).Copy _
        Worksheets("Sheet2").Range("a2") 'macro1

Worksheets("Sheet1").Range("d2", Range("d2").End(xlDown)).Copy _
        Worksheets("Sheet2").Range("b2")  'Macro2

Worksheets("Sheet1").Range("c2", Range("c2").End(xlDown)).Copy _
        Worksheets("Sheet2").Range("c2") 'macro3

Worksheets("Sheet1").Range("g2", Range("g2").End(xlDown)).Copy _
        Worksheets("Sheet2").Range("d2") 'macro4


If Worksheets("Sheet1").Range("e2", Range("e2").End(xlDown)).Value = "<0" Then
    Worksheets("Sheet2").Range("i2").Copy  'macro5

If Worksheets("Sheet1").Range("e2", Range("e2").End(xlDown)).Value = ">0" Then
        Worksheets("Sheet2").Range("j2").Copy 'macro6

Worksheets("Sheet2").Activate 'macro7

I would like to help, but honestly don't understand the question. Try editing the question and rephrasing the problem (first paragraph). — Jan Horčička, Oct 06 '18 at 19:23
I have raw data (sheet1) the information I need from the raw data is in the Header of (Sheet2) I'm trying to copy column by column the information I need from (Sheet1) to (Sheet 2). So the first Macro to run is to copy all data from "A2" to end of data in column A and copy to (Sheet2), from A2 down. now this Column is not an issue as all rows in the column A will have Data. Once I get to Macro4 is where the issues start as there is not information in every cell from g2: down. the Macro will stop after the blank found and not process any other numbers. — justin L, Oct 06 '18 at 21:02
If there is a blank in a cell in column g , I still need that blank to maintain its current position while copying the data and pasting to (sheet 2 ) in relation to Row numbers. Thanks for the reply as Im new to the site and learning VBA. — justin L, Oct 06 '18 at 21:09

chillin · Accepted Answer · 2018-10-09T21:45:23.857

Range.end(xldown) only gets you a contiguous range (effectively it will stop at the first blank cell).

Since you want to include blanks, you might want to instead work from the last row of your worksheet back up to the first non-blank cell encountered in that column (which is a way of getting the last row).

This would mean something like:

' If you are new to With statements (below), any objects within the With block that begin with a . relate to "Sheet1". Saves us typing Sheet1 repeatedly, and makes sense to use it since we access a lot of Sheet1's members like range/cells/rows

With Worksheets("Sheet1")

.Range("a2", .cells(.rows.count, "A").End(xlup)).Copy Worksheets("Sheet2").Range("a2") 'macro1

End with

Untested, written on mobile -- but hope it works or gets you closer to a solution. You would need to copy-paste the above and change the A to B, C, D, E, etc. I wasn't too sure what you're trying to achieve with the "<0" condition in macro 5 and 6.

(It would better if you turned the code into a parameterised Sub and just provide the column letter/number as an argument to the sub, but just depends how new you are to VBA and programming in general -- and for the time being whatever is easier for you to understand/maintain.)

Edit regarding macro 5 and 6

With Worksheets("Sheet1")

Dim cell as range

For each cell in .Range("E2", .Cells(.Rows.Count, "E").End(xlUp))

If cell.Value <= 0 Then 'Get rid of the equal sign if you don't want it in your logic/condition'

Cell.Copy Worksheets("Sheet2").cells(cell.row, "I") 'Macro5

ElseIf cell.value > 0 Then

Cell.Copy Worksheets("Sheet2").cells(cell.row, "J") 'Macro6
End If

Next cell

End With



Worksheets("Sheet2").Activate 'macro7

Copying columns including blanks without skipping rows..leave "blanks" blank VBA

1 Answers1