copy and paste

[Eric]

Good morning everyone,

I am using the macro below and if every cell has information in it, the
macro works great. But, if a cell is empty the macro will work up to the
empty cell and go no further leaving empty spaces on the other sheet. Is
there a way to make it copy the blank cells and continue with all the other
cells?
Here is the macro:

Sub Last_four()

Sheets("Last Four").Select

Dim lastrow As Long
Dim myrow As Long
Dim mycol As Long

lastrow = Sheets("test database").Cells(Rows.count, "A").End(xlUp).Row
myrow = 1
mycol = 1
For i = 12 To 9 Step -1
Do Until Sheets("test database").Cells(lastrow, mycol) = ""
Sheets("last four").Cells(i, mycol) = Sheets("test database").Cells(lastrow,
mycol)
mycol = mycol + 1

Loop
lastrow = lastrow - 1
mycol = 1

Next

End Sub

Example:
column 1 2 3 4 5
a b c d e ~~~>
this works

Example:
column 1 2 3 4 5
a c d e ~~~>
this does not work

Any help would be appreciated....Thank you in advance....

Eric

 

[Mike H]

Eric,

It was stopping at a blank because your do loop was telling it to do so
Do Until Sheets("test database").Cells(lastrow, mycol) = ""
Try this. Note the changes to the copying line as well

Sub Last_four()
Dim lastrow As Long
Dim myrow As Long
Dim mycol As Long
lastrow = Sheets("test database").Cells(Rows.Count, "A").End(xlUp).Row
myrow = 1
mycol = 1
For i = 12 To 9 Step -1
Worksheets("last four").Cells(i, mycol).Value = Worksheets("test
database").Cells(lastrow, mycol).Value
mycol = mycol + 1
lastrow = lastrow - 1
mycol = 1
Next
End Sub


Mike

 

[Mike H]

Eric,

I missed that
mycol=mycol+1 isn't necessary because it's immediatly changed back to
mycol=1 2 lines later

 

[Eric]

Hey Mike

Nothing is happening. I copied your macro and it's not doing anything.
Eric

 

[Eric]

Mike,

Still nothing. I see what your talking about but how do I get this to copy
that blank cell? The things that make you go hmmmmmmmmmmm....

Eric

 

[Mike H]

Eric,

As written the macro I gave you will copy the bottom 4 cells in column A of
the worksheet 'Test database' to A12 upwards on the sheet 'Last Four'
irrespective of whether the range contains blanks or not.

Paste the code into a general module and if it doesn't work for you post
back with what precisely is going wrong.

Mike

 

[Eric]

Mike,

Here is what I have typed,
Sub last_four2()

Dim lastrow As Long
Dim myrow As Long
Dim mycol As Long
lastrow = Sheets("test database").Cells(Rows.count, "A").End(xlUp).Row
myrow = 1
mycol = 1
For i = 12 To 9 Step -1
Sheets("last four").Cells(i, mycol).value = Sheets("test
database").Cells(lastrow, mycol).value
mycol = mycol + 1
lastrow = lastrow - 1
mycol = 1
Next
End Sub

Only the first column is appearingon the Last four sheet. Nothing else is
happening

The tests are in rows A through AC
only column A is showing up

Eric