Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 "Eric" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
Eric,
I missed that mycol=mycol+1 isn't necessary because it's immediatly changed back to mycol=1 2 lines later "Eric" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
Hey Mike
Nothing is happening. I copied your macro and it's not doing anything. Eric "Mike H" wrote: 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 "Eric" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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" wrote: Eric, I missed that mycol=mycol+1 isn't necessary because it's immediatly changed back to mycol=1 2 lines later "Eric" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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" wrote: 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" wrote: Eric, I missed that mycol=mycol+1 isn't necessary because it's immediatly changed back to mycol=1 2 lines later "Eric" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
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 "Mike H" wrote: 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" wrote: 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" wrote: Eric, I missed that mycol=mycol+1 isn't necessary because it's immediatly changed back to mycol=1 2 lines later "Eric" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |