Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
I have a macro that requires functions in the adjacent cells filled right
every month. So for example cells A10 to A15 need to be copied to cells B10 to B15 this month. But next month the cells from B10 to B15 will need to be copied to cells C10 to C15. So what I want to do is find the correct cell and then: ActiveCell.Select Selection.Resize(5, 1).Select But then how do I fill these selected cells right? Help will be much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
Carl: I think this is what you need
Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol), Cells(20, LastCol)) copyrange.Copy Destination:=copyrange.Offset(0, 1) End Sub "carl" wrote: I have a macro that requires functions in the adjacent cells filled right every month. So for example cells A10 to A15 need to be copied to cells B10 to B15 this month. But next month the cells from B10 to B15 will need to be copied to cells C10 to C15. So what I want to do is find the correct cell and then: ActiveCell.Select Selection.Resize(5, 1).Select But then how do I fill these selected cells right? Help will be much appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
Hi Joel,
Thanks for the help but the column that I need to fill isn't actually the last column. It's the second last. So the macro is already set up to insert a new column in between the last two columns and then I need to fill it with the same functions as the one to the left. Thanks Carl "Joel" wrote: Carl: I think this is what you need Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol), Cells(20, LastCol)) copyrange.Copy Destination:=copyrange.Offset(0, 1) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
Is this better
Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy copyrange.Offset(0, 1).Insert Shift:=xlToRight End Sub "carl" wrote: Hi Joel, Thanks for the help but the column that I need to fill isn't actually the last column. It's the second last. So the macro is already set up to insert a new column in between the last two columns and then I need to fill it with the same functions as the one to the left. Thanks Carl "Joel" wrote: Carl: I think this is what you need Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol), Cells(20, LastCol)) copyrange.Copy Destination:=copyrange.Offset(0, 1) End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
Hi Joel,
Thanks again for that. That's pretty much it. But I don't need a column inserted because I've already done that. I just need the cells functions copied in to the cells to the right. So that's probably a cross between the first and second answers that you gave me. I'm close to knowing all of this myself but haven't quite got there yet. You are helping loads though. Thanks Carl "Joel" wrote: Is this better Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy copyrange.Offset(0, 1).Insert Shift:=xlToRight End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy Destination:=copyrange.Offset(0, 1) End Sub "carl" wrote: Hi Joel, Thanks again for that. That's pretty much it. But I don't need a column inserted because I've already done that. I just need the cells functions copied in to the cells to the right. So that's probably a cross between the first and second answers that you gave me. I'm close to knowing all of this myself but haven't quite got there yet. You are helping loads though. Thanks Carl "Joel" wrote: Is this better Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy copyrange.Offset(0, 1).Insert Shift:=xlToRight End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
Thanks Joel,
Managed to work out how to simplify it slightly to better suit my spreadsheet. I used this in the end: Selection.Resize(6, 1).Select Selection.Copy Destination:=Selection.Offset(0, 1) Would not have been able to do it without you though so thanks a lot! "Joel" wrote: Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy Destination:=copyrange.Offset(0, 1) End Sub "carl" wrote: Hi Joel, Thanks again for that. That's pretty much it. But I don't need a column inserted because I've already done that. I just need the cells functions copied in to the cells to the right. So that's probably a cross between the first and second answers that you gave me. I'm close to knowing all of this myself but haven't quite got there yet. You are helping loads though. Thanks Carl "Joel" wrote: Is this better Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy copyrange.Offset(0, 1).Insert Shift:=xlToRight End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill function to next cells
Your code you have to select a cell, mine does it automatically.
When I write macros, I try to make them idiot proof and live little for mistakes. I usually try to avoid using selected cells unless it is necessary. "carl" wrote: Thanks Joel, Managed to work out how to simplify it slightly to better suit my spreadsheet. I used this in the end: Selection.Resize(6, 1).Select Selection.Copy Destination:=Selection.Offset(0, 1) Would not have been able to do it without you though so thanks a lot! "Joel" wrote: Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy Destination:=copyrange.Offset(0, 1) End Sub "carl" wrote: Hi Joel, Thanks again for that. That's pretty much it. But I don't need a column inserted because I've already done that. I just need the cells functions copied in to the cells to the right. So that's probably a cross between the first and second answers that you gave me. I'm close to knowing all of this myself but haven't quite got there yet. You are helping loads though. Thanks Carl "Joel" wrote: Is this better Sub test() LastCol = Cells(10, Columns.Count).End(xlToLeft).Column Set copyrange = Range(Cells(10, LastCol - 1), Cells(20, LastCol - 1)) copyrange.Copy copyrange.Offset(0, 1).Insert Shift:=xlToRight End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function in cell than fill to next 5 column cells. | Excel Programming | |||
color fill cells based on IF function or formula | Excel Worksheet Functions | |||
I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) | |||
Help using the 'If' function to automatically fill in other cells | Excel Worksheet Functions | |||
Function to automatically fill in an array of cells | Excel Programming |