Copy Formula To New Worksheet Without Path

jamesparker_1 · Apr 5th 2007

Hi All

I am copying a sheet to paste into a new workbook. The Sheet contains formula and numbers so I figured that a xlPasteFormulas should work. However the number data gets pasted across fine but the formula get pasted along with their path.

I.e. ='Add Detail Client Delivery-Bmth'!C6

is copied an becomes:

='C:\Documents and Settings\JP\Desktop\Test\[Weekly Metric Template 04_02_2007 Bournemouth.xls]Add Detail Client Delivery-Main'!$C$6

Here is the basic code i'm using:

Code

Set Nws = Sheets(shtName)
     
    Set WwB = Workbooks.Open(Filename:=SiteFile & Day_Str$ & Sitenamefull & ".xls")
     
    Set Ws = WwB.Worksheets(14)
     
    With Ws.Cells.Copy
    End With
     
    Nws.Cells.PasteSpecial xlPasteFormulas

How can I make it copy the formula without the file path?

Thanks

James

PetLahev · Apr 5th 2007

Re: Copy Formula To New Worksheet Without Path

I didn't come to my mind another resolution ....

Before copy to cell (Change Activecell on your cell)

Code

If ActiveCell.HasFormula Then
   myFunc = Mid(String:=ActiveCell.Formula, Start:=2, Length:=Len(ActiveCell.Formula))
End If

and Paste (change Cells(10) on your cell)

Code

Cells(10).Formula = "=" & myFunc

Sorry for my Englis

jamesparker_1 · Apr 5th 2007

Re: Copy Formula To New Worksheet Without Path

Hi PetLahev

Thank you for your reply. It is very much appreciated.

However, I don't think that will work because I have many formula and many data values so I would have to specify each cell i think.

Is there anyway of just pasting the sheet in one go?

Aaron Blood · Apr 5th 2007

Re: Copy Formula To New Worksheet Without Path

What do you want to show in place of the external linked formula?

Do you want to just overwrite it with the value?

If you just paste it without the path, the calculation will probably no longer make any sense.

You could always just copy/paste values instead of formulas. I assume however you're wanting to maintain the functionality of a calculating worksheet and perhaps just substitute values for the linked formulas where required.

jamesparker_1 · Apr 5th 2007

Re: Copy Formula To New Worksheet Without Path

Hi Aaron

I want the formula to just copy across as it is written because I have the same sheet names in the workbook i am copying it across to.

so for example, if the formula in the cell is:

I.e. ='Add Detail Client Delivery-Bmth'!C6

I want to copy it to be exactly the same in the new workbook. I.e.

I.e. ='Add Detail Client Delivery-Bmth'!C6

I hope that makes sense.

James

Aaron Blood · Apr 5th 2007

Re: Copy Formula To New Worksheet Without Path

It would be easier if you structured the target workbook so the external links were on the sheet "Add Detail Client Delivery-Bmth" and that way you wouldn't have to worry about the external refs in your copy/paste-formula action.

I'd have to invest 30 mins in suggesting an alternative solution that would allow you to do exactly what you want.

It would involve:

1. the existing copy/paste that you've done

2. a FOR/EACH search of every formula on the paste target sheet for the existance of the left bracket "]" which indicates an external ref formula

3. editing the ext ref formula string to remove everything in the formula left of the bracket

Unfortunately, it's late... I'm going home.

Tomorrow is Good Friday, I won't be around. If you can't figure it out or someone else doesn't pitch in by Monday or Tuesday next week you may drop me a note.

jamesparker_1 · Apr 10th 2007

Re: Copy Formula To New Worksheet Without Path

Hi Aaron!

I hope you had a good break over easter! Thanks for looking at the above problem!

Yeah, unfortunately I am still stuck and it looks like nobody has been able to offer a solution. So if you are able to help me then I would greatly appreciate that!

Thanks

James

Aaron Blood · Apr 11th 2007

Re: Copy Formula To New Worksheet Without Path

You've already developed the code to do the copy/pasting to a new sheet and workbook. Now all you need to do is scan the formulas of the new worksheet and remove the ext refs from the formulas.

Run something like this at the end of your existing copy/paste macro...

Code

Sub ExtRef_Remover()


    Dim cell As Range, n As Variant


    For Each cell In Workbooks("New_WB").Sheets("Sheet1").Cells.SpecialCells(xlFormulas)
        n = Application.Find("]", cell.Formula)
        If Not IsError(n) Then
            cell.Formula = "='" & Right(cell.Formula, Len(cell.Formula) - n)
        End If
    Next cell


End Sub

Display More

Where "New_WB" is the new workbook you created and "Sheet1" is the name of the sheet in the new workbook.

**royUK** · Apr 11th 2007

Re: Copy Formula To New Worksheet Without Path

Do you do this regularly? If so why not create an Excel template?

jamesparker_1 · Apr 23rd 2007

Re: Copy Formula To New Worksheet Without Path

Hi Aaron!

Thanks so much! That did exactly what I needed!

Very much appreciated!

RoyUK, Yes this will be something i will want to do regularly. I've never used templates before but looking at the helpfile I guess you are saying that I can setup standard formula use them again and again? If so then I think that looks like something I shall look in to!

James

Copy Formula To New Worksheet Without Path

Participate now!

Share

Users Viewing This Thread