| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill macro - variable destination cells
Hi guys...
I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill macro - variable destination cells
Hi,
I'm not sure I understand correctly but does this help Range("AH2:AM2").Copy Destination:=Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1) lrow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH2:AM2").AutoFill Destination:=Range("AH2" & ":AM" & lrow) Mike "Kell2604" wrote: Hi guys... I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill macro - variable destination cells
Well...this half works. It's still autofilling from AH2 and I need it to
autofill after it navigates to the first empty cell as determined by column AG). It could be AH5 one time and AH28 the next. The end row of data changes daily. "Mike H" wrote: Hi, I'm not sure I understand correctly but does this help Range("AH2:AM2").Copy Destination:=Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1) lrow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH2:AM2").AutoFill Destination:=Range("AH2" & ":AM" & lrow) Mike "Kell2604" wrote: Hi guys... I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill macro - variable destination cells
Maybe....
Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH") 'AH to AM is 6 columns .Cells(NextRow, "AH").Resize(1, 6).AutoFill _ Destination:=.Range(.Cells(NextRow, "AH"), _ .Cells(LastRow, "AM")) End With But you can copy the formulas into the receiving range in your paste and drop the autofill: Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH").Resize(LastRow - NextRow + 1) End With Are you really using columns A and AG to determine the next and last row? Kell2604 wrote: Hi guys... I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill macro - variable destination cells
No...
My formulas are in AH2 - AM2 I use column AG only to determine my last row. I'll try your suggestions - thanks so much!! "Dave Peterson" wrote: Maybe.... Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH") 'AH to AM is 6 columns .Cells(NextRow, "AH").Resize(1, 6).AutoFill _ Destination:=.Range(.Cells(NextRow, "AH"), _ .Cells(LastRow, "AM")) End With But you can copy the formulas into the receiving range in your paste and drop the autofill: Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH").Resize(LastRow - NextRow + 1) End With Are you really using columns A and AG to determine the next and last row? Kell2604 wrote: Hi guys... I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill macro - variable destination cells
If you're using this code:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select Then you're looking at column A. Kell2604 wrote: No... My formulas are in AH2 - AM2 I use column AG only to determine my last row. I'll try your suggestions - thanks so much!! "Dave Peterson" wrote: Maybe.... Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH") 'AH to AM is 6 columns .Cells(NextRow, "AH").Resize(1, 6).AutoFill _ Destination:=.Range(.Cells(NextRow, "AH"), _ .Cells(LastRow, "AM")) End With But you can copy the formulas into the receiving range in your paste and drop the autofill: Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH").Resize(LastRow - NextRow + 1) End With Are you really using columns A and AG to determine the next and last row? Kell2604 wrote: Hi guys... I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill macro - variable destination cells
SORRY - looks like I may have grabbed the wrong code...
Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 33).Select ActiveSheet.Paste ** Now it should autofill from here to the last occupied row as determined by column AG. "Dave Peterson" wrote: If you're using this code: Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select Then you're looking at column A. Kell2604 wrote: No... My formulas are in AH2 - AM2 I use column AG only to determine my last row. I'll try your suggestions - thanks so much!! "Dave Peterson" wrote: Maybe.... Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH") 'AH to AM is 6 columns .Cells(NextRow, "AH").Resize(1, 6).AutoFill _ Destination:=.Range(.Cells(NextRow, "AH"), _ .Cells(LastRow, "AM")) End With But you can copy the formulas into the receiving range in your paste and drop the autofill: Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH").Resize(LastRow - NextRow + 1) End With Are you really using columns A and AG to determine the next and last row? Kell2604 wrote: Hi guys... I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill macro - variable destination cells
You're still using column A to determine that next row. But you're pasting in
that cell that's one row down and 33 to the right. Kell2604 wrote: SORRY - looks like I may have grabbed the wrong code... Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 33).Select ActiveSheet.Paste ** Now it should autofill from here to the last occupied row as determined by column AG. "Dave Peterson" wrote: If you're using this code: Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select Then you're looking at column A. Kell2604 wrote: No... My formulas are in AH2 - AM2 I use column AG only to determine my last row. I'll try your suggestions - thanks so much!! "Dave Peterson" wrote: Maybe.... Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH") 'AH to AM is 6 columns .Cells(NextRow, "AH").Resize(1, 6).AutoFill _ Destination:=.Range(.Cells(NextRow, "AH"), _ .Cells(LastRow, "AM")) End With But you can copy the formulas into the receiving range in your paste and drop the autofill: Dim NextRow As Long Dim LastRow As Long With Worksheets("Sheetnamehere") NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row .Range("ah2:am2").Copy _ Destination:=.Cells(NextRow, "AH").Resize(LastRow - NextRow + 1) End With Are you really using columns A and AG to determine the next and last row? Kell2604 wrote: Hi guys... I have a macro that needs to: 1) Copy static range of formulas 2) navigate to first empty cell in row/column 3) Paste data in that empty cell 4) Autofill to end of data as determined by prior column. Everything works great until I get to the autofill part. I think I must be missing something obvious but I'm just STUCK!! Any thoughts you have would be GREATLY appreciated. I have hunted around the message boards but not seeing what I need. I can make this work if I am pasting the formulas into a static range and then autofilling. The problem seems to be that my destination range is different everytime. And so the macro needs to find the empty cell (which I have working) and then autofill from there. Range("AH2:AM2").Select Selection.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste lRow = Cells(Rows.Count, "AG").End(xlUp).Row Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autofill Destination range open | Excel Programming | |||
| trouble shoot Selection.AutoFill Destination | Excel Programming | |||
| How do I autofill combo boxes with their destination cell? | Excel Worksheet Functions | |||
| Autofill Destination | Excel Programming | |||
Linear Mode
