Hi excel users.
I have a table set up as a template where people can enter data as they need. Therefore, the row count will consistently change.
There are two things I need to happen.
1) I have the following VBA code to copy a value into the cells.
This works fine for cells but when I put this code in a table, the values get entered to the end of the table (including cells that were blanks in column b). What I need to happen is that the values only get entered in column E if there is text (not blanks) in cell b of that row.
2) I need to do the same thing for columns F:L. So instead of writing a macro for each, can it be done in one macro. Similarly can it be done so I can add this macro to a button for each column, so they work individually and not all at once. Meaning column E would be assigned to Shapes("Button 1"), column F Button 2 etc. And if Button 1 is clicked only that column is changed.
FYI, the following will change for each column - column F would look like
Thanks.
I have a table set up as a template where people can enter data as they need. Therefore, the row count will consistently change.
There are two things I need to happen.
1) I have the following VBA code to copy a value into the cells.
Code:
Sub Copy_all()
Dim LastRow As Long
Range("E5:E35").ClearContents ' so range is always the same
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Range("E5:E" & LastRow).Value = Range("E3")
End SubThis works fine for cells but when I put this code in a table, the values get entered to the end of the table (including cells that were blanks in column b). What I need to happen is that the values only get entered in column E if there is text (not blanks) in cell b of that row.
2) I need to do the same thing for columns F:L. So instead of writing a macro for each, can it be done in one macro. Similarly can it be done so I can add this macro to a button for each column, so they work individually and not all at once. Meaning column E would be assigned to Shapes("Button 1"), column F Button 2 etc. And if Button 1 is clicked only that column is changed.
FYI, the following will change for each column - column F would look like
Code:
Range("f5:f35").ClearContents ' so range is always the same
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Range("f5:f" & LastRow).Value = Range("f3")Thanks.
ffice
></o
