|
|
Hey, sorry for the title. It's late here in Africa. Happy new year
btw!
I need two relatively simple things.
1) I have a recorded macro that just copies a bunch of cells and then
pastes them into the row below. Trouble is, they have direct cell
references (ie. "B3:F3") which I would rather just be referred to as
offsets from the activecell. Unsure how to do this in VBA though.
2) I would only like this macro to initiate once text is entered into
a cell in column A. For instance, if the user enters text into cell
A2, the macro should then initiate, copy all formulas from B2:F2, and
paste them into B3:F3. If the user then enters text into A3, it should
follow that it copies B3:F3 and pastes them into B4:F4.
In summary:
Once text is entered in to cell A2, the macro initiates and copies
cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
macro. Of course, not ever referencing the cell by its name directly.
The purpose for all this is to save file size of course, rather than
having 65000*5 cells sitting there with inactive formulas jsut waiting
for text to go into the A column. I hope this makes sense. Thanks!
|
|
On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis
<theseandavis[ at ]gmail.com> wrote:
[Quoted Text] >Hey, sorry for the title. It's late here in Africa. Happy new year
>btw!
>
>I need two relatively simple things.
>
>1) I have a recorded macro that just copies a bunch of cells and then
>pastes them into the row below. Trouble is, they have direct cell
>references (ie. "B3:F3") which I would rather just be referred to as
>offsets from the activecell. Unsure how to do this in VBA though.
>
>2) I would only like this macro to initiate once text is entered into
>a cell in column A. For instance, if the user enters text into cell
>A2, the macro should then initiate, copy all formulas from B2:F2, and
>paste them into B3:F3. If the user then enters text into A3, it should
>follow that it copies B3:F3 and pastes them into B4:F4.
>
>In summary:
>Once text is entered in to cell A2, the macro initiates and copies
>cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
>macro. Of course, not ever referencing the cell by its name directly.
>
>The purpose for all this is to save file size of course, rather than
>having 65000*5 cells sitting there with inactive formulas jsut waiting
>for text to go into the A column. I hope this makes sense. Thanks!
Try this macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(0, 1).Resize(1, 5).Copy
Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End If
End Sub
Hope this helps / Lars-Åke
|
|
On Dec 31, 11:47 pm, Lars-Åke Aspelin <lar...[ at ]REMOOOVE.telia.com>
wrote:
[Quoted Text] > On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis
>
>
>
> <theseanda...[ at ]gmail.com> wrote:
> >Hey, sorry for the title. It's late here in Africa. Happy new year
> >btw!
>
> >I need two relatively simple things.
>
> >1) I have a recorded macro that just copies a bunch of cells and then
> >pastes them into the row below. Trouble is, they have direct cell
> >references (ie. "B3:F3") which I would rather just be referred to as
> >offsets from the activecell. Unsure how to do this in VBA though.
>
> >2) I would only like this macro to initiate once text is entered into
> >a cell in column A. For instance, if the user enters text into cell
> >A2, the macro should then initiate, copy all formulas from B2:F2, and
> >paste them into B3:F3. If the user then enters text into A3, it should
> >follow that it copies B3:F3 and pastes them into B4:F4.
>
> >In summary:
> >Once text is entered in to cell A2, the macro initiates and copies
> >cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
> >macro. Of course, not ever referencing the cell by its name directly.
>
> >The purpose for all this is to save file size of course, rather than
> >having 65000*5 cells sitting there with inactive formulas jsut waiting
> >for text to go into the A column. I hope this makes sense. Thanks!
>
> Try this macro:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 1 Then
> Target.Offset(0, 1).Resize(1, 5).Copy
> Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
> Application.CutCopyMode = False
> End If
> End Sub
>
> Hope this helps / Lars-Åke
Wow! I can't even tell what is happening there. Can you explain how it
realizes the change in the cell and then copies the formulas down?
Many thanks,
_Sean
|
|
I didn't test this but it should work. Put it in the worksheet code module
for the sheet with the formulas. It assumes one header row.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If Target = Range("A" & lr + 1) Then
Range("B" & Target.Row & ":F" & Target.Row).Copy
Range("B" & Target.Row + 1).PasteSpecial _
Paste:=xlPasteFormulas
End If
End Sub
"S Davis" wrote:
[Quoted Text] > Hey, sorry for the title. It's late here in Africa. Happy new year
> btw!
>
> I need two relatively simple things.
>
> 1) I have a recorded macro that just copies a bunch of cells and then
> pastes them into the row below. Trouble is, they have direct cell
> references (ie. "B3:F3") which I would rather just be referred to as
> offsets from the activecell. Unsure how to do this in VBA though.
>
> 2) I would only like this macro to initiate once text is entered into
> a cell in column A. For instance, if the user enters text into cell
> A2, the macro should then initiate, copy all formulas from B2:F2, and
> paste them into B3:F3. If the user then enters text into A3, it should
> follow that it copies B3:F3 and pastes them into B4:F4.
>
> In summary:
> Once text is entered in to cell A2, the macro initiates and copies
> cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
> macro. Of course, not ever referencing the cell by its name directly.
>
> The purpose for all this is to save file size of course, rather than
> having 65000*5 cells sitting there with inactive formulas jsut waiting
> for text to go into the A column. I hope this makes sense. Thanks!
>
|
|
On Wed, 31 Dec 2008 12:50:40 -0800 (PST), S Davis
<theseandavis[ at ]gmail.com> wrote:
[Quoted Text] >On Dec 31, 11:47 pm, Lars-Åke Aspelin <lar...[ at ]REMOOOVE.telia.com>
>wrote:
>> On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis
>>
>>
>>
>> <theseanda...[ at ]gmail.com> wrote:
>> >Hey, sorry for the title. It's late here in Africa. Happy new year
>> >btw!
>>
>> >I need two relatively simple things.
>>
>> >1) I have a recorded macro that just copies a bunch of cells and then
>> >pastes them into the row below. Trouble is, they have direct cell
>> >references (ie. "B3:F3") which I would rather just be referred to as
>> >offsets from the activecell. Unsure how to do this in VBA though.
>>
>> >2) I would only like this macro to initiate once text is entered into
>> >a cell in column A. For instance, if the user enters text into cell
>> >A2, the macro should then initiate, copy all formulas from B2:F2, and
>> >paste them into B3:F3. If the user then enters text into A3, it should
>> >follow that it copies B3:F3 and pastes them into B4:F4.
>>
>> >In summary:
>> >Once text is entered in to cell A2, the macro initiates and copies
>> >cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
>> >macro. Of course, not ever referencing the cell by its name directly.
>>
>> >The purpose for all this is to save file size of course, rather than
>> >having 65000*5 cells sitting there with inactive formulas jsut waiting
>> >for text to go into the A column. I hope this makes sense. Thanks!
>>
>> Try this macro:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Column = 1 Then
>> Target.Offset(0, 1).Resize(1, 5).Copy
>> Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
>> Application.CutCopyMode = False
>> End If
>> End Sub
>>
>> Hope this helps / Lars-Åke
>
>Wow! I can't even tell what is happening there. Can you explain how it
>realizes the change in the cell and then copies the formulas down?
>
>Many thanks,
>_Sean
The Worksheet_Change sub is a predefined macro that is triggered
whenever there are any changes in any cells of the worksheet.
The (also predefined) parameter Target holds a reference to the cell
that has been changed.
In this case we are only interested in changes to column A (which has
the value 1) therefore the If statement.
The remaining three statements is the copying of Bn:Fn to Bn+1:Fn+1.
Example: If changes are made to cell A2 then
Target = A2
Target.Column = 1 (so this should be handled)
Target.Offset(0,1) = B2
Target.Offset(0,1).Resize(1,5) = B2:F2
Target.Offset(1,1) = B3
Application.CutCopyMode = False
is just to end the copying mode.
Lars-Åke
|
|
Lars' code does the same as mine, only his executes for any selection in
column one and mine only executes if the selection is the next empty cell in
column one.
"S Davis" wrote:
[Quoted Text] > On Dec 31, 11:47 pm, Lars-Ã…ke Aspelin <lar...[ at ]REMOOOVE.telia.com>
> wrote:
> > On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis
> >
> >
> >
> > <theseanda...[ at ]gmail.com> wrote:
> > >Hey, sorry for the title. It's late here in Africa. Happy new year
> > >btw!
> >
> > >I need two relatively simple things.
> >
> > >1) I have a recorded macro that just copies a bunch of cells and then
> > >pastes them into the row below. Trouble is, they have direct cell
> > >references (ie. "B3:F3") which I would rather just be referred to as
> > >offsets from the activecell. Unsure how to do this in VBA though.
> >
> > >2) I would only like this macro to initiate once text is entered into
> > >a cell in column A. For instance, if the user enters text into cell
> > >A2, the macro should then initiate, copy all formulas from B2:F2, and
> > >paste them into B3:F3. If the user then enters text into A3, it should
> > >follow that it copies B3:F3 and pastes them into B4:F4.
> >
> > >In summary:
> > >Once text is entered in to cell A2, the macro initiates and copies
> > >cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
> > >macro. Of course, not ever referencing the cell by its name directly.
> >
> > >The purpose for all this is to save file size of course, rather than
> > >having 65000*5 cells sitting there with inactive formulas jsut waiting
> > >for text to go into the A column. I hope this makes sense. Thanks!
> >
> > Try this macro:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Column = 1 Then
> > Target.Offset(0, 1).Resize(1, 5).Copy
> > Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
> > Application.CutCopyMode = False
> > End If
> > End Sub
> >
> > Hope this helps / Lars-Ã…ke
>
> Wow! I can't even tell what is happening there. Can you explain how it
> realizes the change in the cell and then copies the formulas down?
>
> Many thanks,
> _Sean
>
|
|
Try this in your sheet module.
Assumes you have formulas in B1:F1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Me.Range("A" & n).Value <> "" Then
With Target
..Offset(-1, 1).Resize(, 5).Copy _
Destination:=.Offset(, 1)
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub
Gord Dibben MS Excel MVP
On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis <theseandavis[ at ]gmail.com>
wrote:
[Quoted Text] >Hey, sorry for the title. It's late here in Africa. Happy new year
>btw!
>
>I need two relatively simple things.
>
>1) I have a recorded macro that just copies a bunch of cells and then
>pastes them into the row below. Trouble is, they have direct cell
>references (ie. "B3:F3") which I would rather just be referred to as
>offsets from the activecell. Unsure how to do this in VBA though.
>
>2) I would only like this macro to initiate once text is entered into
>a cell in column A. For instance, if the user enters text into cell
>A2, the macro should then initiate, copy all formulas from B2:F2, and
>paste them into B3:F3. If the user then enters text into A3, it should
>follow that it copies B3:F3 and pastes them into B4:F4.
>
>In summary:
>Once text is entered in to cell A2, the macro initiates and copies
>cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
>macro. Of course, not ever referencing the cell by its name directly.
>
>The purpose for all this is to save file size of course, rather than
>having 65000*5 cells sitting there with inactive formulas jsut waiting
>for text to go into the A column. I hope this makes sense. Thanks!
|
|
On Jan 1, 12:08 am, JLGWhiz <JLGW...[ at ]discussions.microsoft.com> wrote:
[Quoted Text] > Lars' code does the same as mine, only his executes for any selection in
> column one and mine only executes if the selection is the next empty cell in
> column one.
>
> "S Davis" wrote:
> > On Dec 31, 11:47 pm, Lars-Åke Aspelin <lar...[ at ]REMOOOVE.telia.com>
> > wrote:
> > > On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis
>
> > > <theseanda...[ at ]gmail.com> wrote:
> > > >Hey, sorry for the title. It's late here in Africa. Happy new year
> > > >btw!
>
> > > >I need two relatively simple things.
>
> > > >1) I have a recorded macro that just copies a bunch of cells and then
> > > >pastes them into the row below. Trouble is, they have direct cell
> > > >references (ie. "B3:F3") which I would rather just be referred to as
> > > >offsets from the activecell. Unsure how to do this in VBA though.
>
> > > >2) I would only like this macro to initiate once text is entered into
> > > >a cell in column A. For instance, if the user enters text into cell
> > > >A2, the macro should then initiate, copy all formulas from B2:F2, and
> > > >paste them into B3:F3. If the user then enters text into A3, it should
> > > >follow that it copies B3:F3 and pastes them into B4:F4.
>
> > > >In summary:
> > > >Once text is entered in to cell A2, the macro initiates and copies
> > > >cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
> > > >macro. Of course, not ever referencing the cell by its name directly..
>
> > > >The purpose for all this is to save file size of course, rather than
> > > >having 65000*5 cells sitting there with inactive formulas jsut waiting
> > > >for text to go into the A column. I hope this makes sense. Thanks!
>
> > > Try this macro:
>
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Column = 1 Then
> > > Target.Offset(0, 1).Resize(1, 5).Copy
> > > Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
> > > Application.CutCopyMode = False
> > > End If
> > > End Sub
>
> > > Hope this helps / Lars-Åke
>
> > Wow! I can't even tell what is happening there. Can you explain how it
> > realizes the change in the cell and then copies the formulas down?
>
> > Many thanks,
> > _Sean
Well it works excellent. However, dumb as I may be, I can not get it
to select the next cell in line after the macro has completed. Simply,
if I enter text inbto A5, I need it to finish by selecting A6, or
whatever it is. activecell.offset(0,-2).select is not working for some
reason, though positive values do work....
|
|
On Jan 1, 12:13 am, Gord Dibben <gorddibbATshawDOTca> wrote:
[Quoted Text] > Try this in your sheet module.
>
> Assumes you have formulas in B1:F1
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> On Error GoTo enditall
> Application.EnableEvents = False
> If Target.Cells.Column = 1 Then
> n = Target.Row
> If Me.Range("A" & n).Value <> "" Then
> With Target
> .Offset(-1, 1).Resize(, 5).Copy _
> Destination:=.Offset(, 1)
> End With
> End If
> End If
> enditall:
> Application.EnableEvents = True
> End Sub
>
> Gord Dibben MS Excel MVP
>
> On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis <theseanda...[ at ]gmail.com>
> wrote:
>
> >Hey, sorry for the title. It's late here in Africa. Happy new year
> >btw!
>
> >I need two relatively simple things.
>
> >1) I have a recorded macro that just copies a bunch of cells and then
> >pastes them into the row below. Trouble is, they have direct cell
> >references (ie. "B3:F3") which I would rather just be referred to as
> >offsets from the activecell. Unsure how to do this in VBA though.
>
> >2) I would only like this macro to initiate once text is entered into
> >a cell in column A. For instance, if the user enters text into cell
> >A2, the macro should then initiate, copy all formulas from B2:F2, and
> >paste them into B3:F3. If the user then enters text into A3, it should
> >follow that it copies B3:F3 and pastes them into B4:F4.
>
> >In summary:
> >Once text is entered in to cell A2, the macro initiates and copies
> >cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
> >macro. Of course, not ever referencing the cell by its name directly.
>
> >The purpose for all this is to save file size of course, rather than
> >having 65000*5 cells sitting there with inactive formulas jsut waiting
> >for text to go into the A column. I hope this makes sense. Thanks!
Much nicer, thank you Mr. Savant. I love this group :) Thank you Gord
and JLWhiz as well, just saved me 7 meg of space and taught me a ton!
-Sean in Africa
|
|
|