copy and paste

E

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
 
M

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
 
M

Mike H

Eric,

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

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
 
M

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
 
E

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top