Hi

I am trying to automate my daily spreadsheet.

In col A I have a formula that looks at a table on a different sheet and
returns the value that matches Col C.
I have a macro that copies Col F and pastes it into Col C position then
proceeds to split text into columns. As soon as I run the macro it cancels
out the formula because Col C is being recreated. I tried creating another
column that equalled column C but even that formula cancelled out and gave a
#REF! in the formula instead of C:C.

Any ideas on what I can do to stop it cancelling it out please?

Thx

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Nelly" <(E-Mail Removed)> wrote in message
news:19BC6C32-D18E-4034-B55C-(E-Mail Removed)...
> Hi
>
> I am trying to automate my daily spreadsheet.
>
> In col A I have a formula that looks at a table on a different sheet and
> returns the value that matches Col C.
> I have a macro that copies Col F and pastes it into Col C position then
> proceeds to split text into columns. As soon as I run the macro it
> cancels
> out the formula because Col C is being recreated. I tried creating
> another
> column that equalled column C but even that formula cancelled out and gave
> a
> #REF! in the formula instead of C:C.
>
> Any ideas on what I can do to stop it cancelling it out please?
>
> Thx

Thanks Don, I have emailed it to you. Hopefully I have provided enough info?

Thx
Nelly

"Don Guillett" wrote:

> If desired, send your file to my address below along with this msg and
> a clear explanation of what you want and before/after examples.
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Nelly" <(E-Mail Removed)> wrote in message
> news:19BC6C32-D18E-4034-B55C-(E-Mail Removed)...
> > Hi
> >
> > I am trying to automate my daily spreadsheet.
> >
> > In col A I have a formula that looks at a table on a different sheet and
> > returns the value that matches Col C.
> > I have a macro that copies Col F and pastes it into Col C position then
> > proceeds to split text into columns. As soon as I run the macro it
> > cancels
> > out the formula because Col C is being recreated. I tried creating
> > another
> > column that equalled column C but even that formula cancelled out and gave
> > a
> > #REF! in the formula instead of C:C.
> >
> > Any ideas on what I can do to stop it cancelling it out please?
> >
> > Thx
>
>

Your original formula should have been
=VLOOKUP(b4,Sheet4!$A$1:$B$13,2,0)
your macro should be
Sub cutcolEtoColB()
Columns("E").Cut
Columns("b").Insert
End Sub

formula now
=VLOOKUP(c4,Sheet4!$A$1:$B$13,2,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Nelly" <(E-Mail Removed)> wrote in message
news:2DEAA119-6E41-4A04-A25B-(E-Mail Removed)...
> Thanks Don, I have emailed it to you. Hopefully I have provided enough
> info?
>
> Thx
> Nelly
>
> "Don Guillett" wrote:
>
>> If desired, send your file to my address below along with this msg
>> and
>> a clear explanation of what you want and before/after examples.
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Nelly" <(E-Mail Removed)> wrote in message
>> news:19BC6C32-D18E-4034-B55C-(E-Mail Removed)...
>> > Hi
>> >
>> > I am trying to automate my daily spreadsheet.
>> >
>> > In col A I have a formula that looks at a table on a different sheet
>> > and
>> > returns the value that matches Col C.
>> > I have a macro that copies Col F and pastes it into Col C position then
>> > proceeds to split text into columns. As soon as I run the macro it
>> > cancels
>> > out the formula because Col C is being recreated. I tried creating
>> > another
>> > column that equalled column C but even that formula cancelled out and
>> > gave
>> > a
>> > #REF! in the formula instead of C:C.
>> >
>> > Any ideas on what I can do to stop it cancelling it out please?
>> >
>> > Thx
>>
>>