Free Microsoft Excel 2013 Quick Reference

Copy cell format in formula from one worksheet to another Results

Is it possible to copy the Format of a Cell from one Worksheet to another when using a Formula / reference to another Worksheet Cell?

Eg, When refering to a Cell in another worksheet, ie. ='Sheet 2'!B4 the value of that cell (B4) is copied / linked, is it possible to link the Format of that cell aswell, by any method.

Given that "Conditional Formatting" doesn't allow references to another Worksheet, and that the Source worksheet already uses "Conditional Formatting" for different values / colors, copying / linking the format would be the best solution for this particular Workbook.

Can this be achieved within Excel 2007? and if so, how?

I need to copy the formulas, format etc.. from one sheet to another sheet,
but I do not all the text (data I have put in for the previous month.

I have been, using copy and paste But then I'm having to clear Each cell
that is used to do all the calculations (sometimes there are over 200).

I do not use any calculations based on previous sheets (as far as like
adding something from one page to another for totals)

Just want to use that one sheet - blank - to start my new month.

Thanks to anyone that can make this easier for me.

Hello: I am not a VBA programmer but I have gathered enough info to accomplish most of what I set out to, Much thanks to this forum. I am still having an issue with one item. When I try to copy a value from one worksheet to another it is rounding off the number to 2 decimal places rather than 4. eg shows up on sheet 3 as $13.4100 rather than $13.4135.
Both cells are formatted to display currency to 4 decimal places.
The value being copied from sheet one is calculated using a formula

and the code used to transfer the info is:
Private Sub CommandButton2_Click() 'Tax Form Button - captures all sales for year



Dim lastrow3 As Long
lastrow3 = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row

If Cells(4, "A") = "" Then
Sheets("Sheet3").Range(Cells(4, "A"), Cells(lastrow3, "H")).Select
Selection = ""

Sheets("Sheet3").Range(Cells(lastrow3, "B"), Cells(lastrow3, "H")).Select
     With Selection.Borders(xlEdgeTop)
        .LineStyle = none
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = none
    End With
End If

Dim i As Integer, rng As Range
Dim lastrow As Long, nextrow As Long
Dim Title As String
Title = ("MY WESTJET SHARES - TAX REPORT FOR " & TextBox1.Text)
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
nextrow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row + 1

Set rng = Sheets("Sheet1").Range("T4:T" & lastrow)

    For i = 4 To lastrow
            If Cells(i, "T").Value = TextBox1.Text And Cells(i, "C").Value = "Sell" Then
            'Range(Cells(i, "A"), Cells(i, "L")).Copy
Destination:=Sheets("Sheet3").Cells(nextrow, "A")
            Sheets("Sheet3").Cells(nextrow, "A").Value = Range(Cells(i, "A"), Cells(i,
"A")).Value 'Date
            Sheets("Sheet3").Cells(nextrow, "B").Value = Range(Cells(i, "E"), Cells(i,
"E")).Value 'Share Price
            Sheets("Sheet3").Cells(nextrow, "C").Value = Range(Cells(i, "G"), Cells(i,
"G")).Value '#Shares Sold
            Sheets("Sheet3").Cells(nextrow, "D").Value = Range(Cells(i, "I"), Cells(i,
"I")).Value 'Price Sold For
            Sheets("Sheet3").Cells(nextrow, "E").Value = Range(Cells(i, "K"), Cells(i,
"K")).Value 'ACB/Share
            Sheets("Sheet3").Cells(nextrow, "G").Value = Range(Cells(i, "J"), Cells(i,
"J")).Value 'Capital Gain/Loss
            Sheets("Sheet3").Cells(nextrow, "H").Value = Range(Cells(i, "D"), Cells(i,
"D")).Value 'Sales Fee
            Sheets("Sheet3").Cells(nextrow, "I").Value = Range(Cells(i, "P"), Cells(i,
"P")).Value 'Date 1st
            Sheets("Sheet3").Cells(nextrow, "J").Value = Range(Cells(i, "Q"), Cells(i,
"Q")).Value 'Date last
            'Sheets("Sheet3").Cells(nextrow, "I").Value = Range(Cells(i, "I"), Cells(i,
            'Sheets("Sheet3").Cells(nextrow, "J").Value = Range(Cells(i, "J"), Cells(i,
            'Sheets("Sheet3").Cells(nextrow, "K").Value = Range(Cells(i, "K"), Cells(i,
            'Sheets("Sheet3").Cells(nextrow, "A").Resize(1, 12).Value = Range(Cells(i, "A"),
Cells(i, "L")).Value
            nextrow = nextrow + 1
        End If
    Next i


    Dim EndRow As Long
    EndRow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row
    Cells(EndRow + 1, 1).Value = "Totals"
    Dim r As Long

r = Cells(Rows.Count, "B").End(xlUp).Row

Range("F2").AutoFill Destination:=Range("F2", ("F4:F" & EndRow))

Cells(3, "F").Value = "Adj. Cost Base"
    Cells(EndRow + 1, 3).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 4).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 6).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 7).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 8).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(1, 5).Value = Title
    'Range("A" & Rows.Count).End(xlUp).Select
    Sheets("Sheet3").Range(Cells(nextrow, "B"), Cells(nextrow, "H")).Select
     With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = xlAutomatic
        .Weight = xlThick
    End With

Range("A" & Rows.Count).End(xlUp).Select 'Selects the last cell in column A (used to deselect previous
'Unload Me

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

REPORTS.CommandButton3.Visible = True

End Sub
Sheets("Sheet3").Cells(nextrow, "E").Value = Range(Cells(i, "K"), Cells(i, "K")).Value 'ACB/Share This is the line giving me issues.

On sheet 1 the value is displayed to 4 decimal points but on sheet 3 there are 4 decimal places but the last two are both zero's
Any Ideas on why this is happening

I have C:MY DOCUMENTSTARGET.XLS opened to a worksheet called RESULTS. The
cell highlight is residing in B90.

There's another file C:MY DOCUMENTSSOURCE.XLS that also has a worksheet
called RESULTS.

Without opening SOURCE.XLS, I want to copy the cell formats from B90:G175
from SOURCE to TARGET. (How) Can I do that in a macro without opening

I found something in Excel Help about copying a formula from one workbook to
another but that's not quite what I'm looking for. Thanks, in advance, for
any assistance.

I have a work in progress and I do need the help of the people here.

In the image below, say I need to copy the row of "C_C_FOB_AEL" and "C_C_FOB_LAM" consecutively to another workbook.

I initially did a code such that it will copy all ranges in use but since the other rows are not needed, might as well drop the trash (not to mention that the data is so huge now).

My current code located in the workbook where it will be pasted is something like this:

 CopyAndPaste2(WholePath, TargetFilePath1, TargetFilename1, SheetName) 
    Dim wsobj As Object 
    Dim rngobj As Object 
    Dim TargetWS As Worksheet 
    Set wsobj = xlobj.Worksheets(SheetName) 
    Set rngobj = wsobj.Range("A:A").Find("C_C_FOB_AEL", LookIn:=xlValues) 
    sUsedRange = rngobj.Address 
    sUsedRange = "$A$3:$M$3" 
    Set TargetWS = Sheets(SheetName) 
    With TargetWS.Range(sUsedRange) 
        .FormulaR1C1 = "=IF('" & TargetFilePath1 & "[" & TargetFilename1 & "]" & SheetName & "'!RC="""", NA()," & _ 
        "'" & TargetFilePath1 & "[" & TargetFilename1 & "]" & SheetName & "'!RC)" 
         'Delete all Error cells
        On Error Resume Next 
        .SpecialCells(xlCellTypeFormulas, xlErrors).Clear 
        On Error Goto 0 
         'Change all formulas to Values only
        .Value = .Value 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see, I am having troubles in the following areas:
properly telling what strings to find (in this case only, C_C_FOB_AEL is searched)identifying the correct row (currently, it just returns the $A$3, I have to hardcode the $A$3:$M$3 part)once the correct row is chosen, it will be pasted to that same row. What if i want it to be pasted to say A1:M1 instead?
Thank you very much for your response.

I am also attaching the workbook where this code maybe found.

The workbook is a little messy now so please be gentle.

I'm a macro noob. Just so you know.

Part of a macro I'm using looks like this:

Dim cl1 As Range, rng1 As Range 
Dim r As Long, column As Long 
Set ws1 = Worksheets(1) 
Set ws2 = Worksheets(2) 
Set rng1 = ws1.Range("a1", Range("a65536").End(xlUp)) 
For Each cl1 In rng1 
    r = cl1.Row 
    column = cl1.column 
    If cl1.Value  ws2.Cells(r, column) Then 
        ws2.Cells(r, column).EntireRow.Insert 
        ws2.Cells(r, column).Value = cl1.Value 
    End If 
Next cl1 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which works perfectly for inserting entire rows - so far the macro finds gaps in the data on sheet 2, inserts blank rows for those gaps, then extrapolates the missing information. I would like to change it so that instead of inserting entire rows, it will only insert a row of cells ranging from column A to I. A small change in this line:
ws2.Cells(r, column).EntireRow.Insert
...might be all I need

A better solution would be how can I copy 3 columns of formulas (and a chart!) from sheet 3 and paste them onto sheet 2.

In case it matters, this is what I'm trying to do:
I'm making charts, based on data that is 3672 rows long and 9 columns (A to I) wide. The chart specifically uses information only from columns J to L which, in turn, are filled with formulae that reflects data in columns A to I. I figured the easiest thing to do is leave the formula in columns J to L and an already made chart (blank, since there's no data yet), then paste data into columns A to I - which completes the chart. The reason I can't do this now is because when I run the macro, it deletes entire rows, which also deletes formula from columns J to L.

Another possibility I was wondering is if I could have a chart and formulas on sheet 3, then when the macro is done finding gaps and whatnot, it would copy the chart and formula from sheet 3 onto sheet 2 - starting at cell J1. Or perhaps pasting just a couple rows of formula, then employing some kind of filldown function. Or, select all cells from A:1 to I:3672, then paste them into cell A:1 on sheet 3, and have chart/formula on sheet 3, then have the macro select sheet 3 as active so you can see it without clicking on that page.

thx for any help

I've got a spreadsheet that lists prices per 1000 for various skus. The prices are linked to another spreadsheet that actually calculates them. What I'm having an issue with is when I attempt to copy that information from one worksheet to another. If I just do a straight copy, I get a reference error because it copies the link. When I do a 'paste special' to get the values and formatting, the cell on the worksheet displays a dollar value formatted $#,###.## but the formula bar shows the calculated value with 4 decimal places. For example the cell in the spreadsheet shows $30.63, but when I select that cell the formula bar shows 30.6295 How do I get the formula bar to reflect the currency format--so that the formula bar also shows $30.63 (or even 30.63) without hard coding the numbers?


Thanks for helping me guys! I am kind of new to VBA. Just kind of am learning on the fly. I have a Workbook that is intended to track overtime within different departments. I have 7 departments and one master listing of all associates within our organization. The master list details the employees name, their employee ID and their department. Each Department tab has a place where information is going to pulled to see overtime.

I want a macro to look at the Employees tab, Column D (Dept No) and segregate all the associates that belong in the different departments. For instance, Cheese Department will have 3 differenet departments (RO4353, MU7348, and DE5625). Most other departments only have one department number. Anyway I want the macro to look up the three different department numbers and put them in the Cheese tab starting after the grayed out part (cell a17). Then I want to do the same thing for all the other departments. That's what I need help doing. To make more space I deleted the other departments from the worksheet.

Then I will pull a pay tab in but just couldn't put it on here because of confidentiality issues. I'm not sure if there is still passwords on them but the password to get into the file is Overtime.

Current VBA code is:

    Dim LastRow As Long 
    Application.ScreenUpdating = False 
     'To Clear Contents from Last Pay Period
    Sheets("Total OT and Cost").Select 
     'To prepare Employee Sheet
    ActiveCell.FormulaR1C1 = "=IF([Employees.xls]Sheet1!RC=0,"""",[Employees.xls]Sheet1!RC)" 
    Selection.AutoFill Destination:=Range("A2:A308") 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Employees.xls]Sheet1!C1:C2,2,FALSE)" 
    Selection.AutoFill Destination:=Range("B2:B308") 
    ActiveCell.FormulaR1C1 = "=IF([Employees.xls]Sheet1!RC[-2]=0,"""",[Employees.xls]Sheet1!RC[-2])" 
    Selection.AutoFill Destination:=Range("C2:C308") 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],[Employees.xls]Sheet1!C1:C3,3,FALSE)" 
    Selection.AutoFill Destination:=Range("D2:D308") 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],[Employees.xls]Sheet1!C1:C4,4,FALSE)" 
    Selection.AutoFill Destination:=Range("E2:E308") 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     'Put formula in to segregate employees into different departments
    Dim ShSource As Worksheet 
    Dim ShTarget As Worksheet 
    Dim Rng As Range 
    Dim r As Long 
    Dim Cell As Range 
    LastRow = Range("C" & Rows.Count).End(xlUp).Row 
    Set ShTarget = Sheets("Cheese") 
    Set ShSource = Sheets("Employees") 
    For Each Cell In Range("d2:d" & LastRow) 
        If Cell.Text = "RO4353" Then 
            With Cell.EntireRow.Cells(1, 1) 
                If WorksheetFunction.CountIf(ShTarget.Columns(1), .Value) = 0 Then _ 
                .Copy ShTarget.Cells(r, 1) 
                r = r + 1 
            End If 
        End With 
    End If 
Next Cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for your help! This will basically cut down work for me by 8 hours a week...

Hey Anyone,

I'm just trying to figure out how to roud a formula.

When you type a formula, the answer is shown in the cell with however many decimal places you've formatted that particularm cell to right?

So when I copy a formula from one worksheet to another and paste it as a value, is there anyway I can round the amount that shows in the formula bar? I need to be able to do this for purchase card bills at work. Hopefully someone can help me! I'm lost!!!

I'm having a lot of trouble with the Range formula. I think I understand it... although I could be wrong... basically I'm trying to copy the cell contents from one worksheet to another using the Range method along with the Cells method as such:

Sheets("Output").Range(Cells(1, 1), Cells(m,n)).Value = Sheets("Input").Range(Cells(1, 1),Cells(m, n)).Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

There seems to be a problem with using Cells inside the Range method in this way... it looks like it should work, but it keeps giving me errors... Please help!

am trying to take data from a worksheet and create a macro to copy the data, format it and place it in a different worksheet.
The data worksheet has column headerssuch as acct. Name1 number address city St Zip Phone. I need to take the data and copy it to a new worksheet.
I need to add a column called lettercode and have it prompt for a letter code from the user that is populated down the entire column. Then the acct. columnn needs to be pasted under the number column. The Name1 Column needs to become two column firstName_MiddleName and Lastname. The Name1 one first middle name or middle initial and last name that need to be seperated. Then the address city St Zip Phone columns are then copied.
I have been searching and that found some code to start with but it doesn't get me where I want to be.

Sub copy2()
Dim wb As Workbook, ws As Worksheet, wsDest As Worksheet
Dim rLook As Range, rCell As Range, sCell As String
Dim iCnt As Long, sFirstAddy As String
Const strColHead As String = "Yellow" 'your column header
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
Set rLook = ws.Rows(1)
Set rCell = rLook.Find(what:=strColHead, after:=rLook.Cells(rLook.Rows.Count, rLook.Columns.Count), searchdirection:=xlNext,
sCell = rCell.Address
iCnt = 1
Set wsDest = wb.Worksheets.Add(after:=ws)
Do Until sCell = vbNullString Or sCell = sFirstAddy
If sFirstAddy = vbNullString Then sFirstAddy = sCell
ws.Columns(rCell.Column).copy Destination:=wsDest.Columns(iCnt)
iCnt = iCnt + 1
sCell = vbNullString
Set rCell = rLook.FindNext(after:=rCell)
sCell = rCell.Address
Application.CutCopyMode = False
Sub split()

Dim MySheet As Worksheet
Dim RowNumber As Integer

Const ColumnA = 1
Const ColumnB = 2
Const ColumnC = 3
Set MySheet = Workbooks("Book1.xls").Worksheets("Sheet1")
With MySheet
RowNumber = 1
While Not IsEmpty(.Cells(RowNumber, ColumnA))
.Cells(RowNumber, ColumnB) = Left(.Cells(RowNumber, ColumnA), 3)
.Cells(RowNumber, ColumnC) = Mid(.Cells(RowNumber, ColumnA), 4)
RowNumber = RowNumber + 1
End With


End Sub
=IFERROR(LEFT(B2,FIND(" ",B2)-1),B2)
=IF(LEN(b2)-LEN(SUBSTITUTE(b2," ",""))>1,MID(b2,FIND(" ",b2)+1,FIND("
",b2,FIND(" ",b2)+1)-(FIND(" ",b2)+1)),"")

LEN(SUBSTITUTE(B2," ",""))))),"")


Hi. I'm using Excel 2010. My goal is to compare each cell on one sheet with the same cell on a second sheet (e.g, Sheet1 A1 to Sheet2 A1), and all cells much match exactly. The reasoning is that we have data that is transcribed from paper and entered on both sheets. One person enters it on Sheet1, and a second person enters it on Sheet2. This is done to ensure accuracy and integrity of that data conversion to electronic format. My hope is to find a way to set up conditional formatting so that IF a value on one sheet is different from that on the other, then the cells on both sheets will be highlighted yellow.

The formula I currently have setup in Sheet1: =NOT(EXACT(A1,'Sheet2'!A1)). The formula in Sheet 2 is similar, except it is pointing at Sheet1:=NOT(EXACT(A1,'Sheet1'!A1)). In the 'Applies to' box in the Conditional Formatting Rules Manager, I've got =$A:$IV.

This works beautifully as long as only data is entered. If by chance, however, columns or rows are deleted (which they oftentimes need to be during the reconciliation process), then the formulas and/or the regions they apply to are thrown completely off. Additionally, if/when data is copied from one location to another, it changes the 'Applies to' area.

Is there a way within the Conditional Formatting to apply the formula statically to the entire worksheet no matter what is copied/cut/inserted/pasted/deleted? I tried an Index formula but couldn't get it to work. I have posted this also to Excel Forum (, and I did get one response; however, that response did not work either.

I would appreciate any advice. Thanks.


I currently have the following code to copy and transpose data from one worksheet to another:

Sheet3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I would like to do is add to this code. I would like to add the following formula to the cell at the end of that pasted row.


In this case the Range needs to be Row D and the last row of sheet3.

Any help would be good.

Good Morning,

I'm using the following code to copy and paste values from one worksheet to another but I need it to copy the values of the cell as its a simple additional formula. I think its solved by inserting .Value somewhere but not sure where.

Range("C6").Copy Destination:=Sheets("Report").Range("D" & Rows.Count).End(xlUp).Offset(1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Help would be greatly appreciated

I have a spreadsheet that is driving me absolutely nuts. I have used an index/match formula to get the data I want from one worksheet to another, but I have come across a problem that I'm hoping someone has a simple solution to.My primary data sheet has data that is stored under dates in columns (eg columns A-F=Name, Surname, Number, etc; G1=11/7/2011, G2=Location1, G3=Location2,...; H1=12/7/2011, H2=Location1, H3=Location2...). I have no control over this first data sheet, and it's format is fixed.My second sheet uses an Index/Match formula is designed to show a person's details based on Date and Location. The formula I am using is: =INDEX(Observers!A$3:A$403,MATCH($C$1,Observers!$G$3:$G$403,0)) where $C$1=location.To make life easier, rather than work out a formula that *also* included the date, I thought I'd simply try to increment the column values for on the range Observers!$G$3:$G$403 on each successive row. I've tried to work out an Indirect/Address formula for this, but cannot seem to get it to work for a data range (I've been able to do it for specific target cells).Can anyone offer suggestions to help with this formula? I am have posted a copy of the spreadsheet as an attachment.Thanks in advance.

I have copied a set of values (numbers and %'s) from one worksheet to another using paste special, values.

My destination worksheet has cells formatted so that all values are displayed without any decimal points. However, the % values imported do have many 'hidden' decimal points.

I am trying to set up a formula to show the difference in % figure displayed in 2 columns, so the result of my formula makes sense with the %'s as they appear, as opposed to how they actually are.

For example - cell A1 shows 3% (but the value is actually 2.7%).

Cell A2 shows 6% (but the value is actually 6.4%).

I want a formula in cell A3 which returns a value showing the increase in % to be 3% (ie. 6-3), as opposed to 4% (ie. 6.4-2.7).

I tried this, but it didn't work:-


....but I may be barking up completely the wrong tree.

Can anyone help, please?


I would like to refer a cell from one worksheet to another. This is
relatively simple and I can just use for example "=Sheet1!A1" when referring
to that cell as a formula. This copies the contents of that cell to the new
cell. All good so far.

I also want it to bring the formatting and cell colour across, but I can't
seem to find any way to do this. I thought it would have bveen simple, but
I'm getting nowhere with this.

To give an example:
On Sheet 1, Column 1 Row A, I have the word hello.
On Sheet 2, Column 1, Row A, I have the reference "=Sheet1!A1"
If I cange the fill colour to red in the cell in sheet 1, I want the cell on
sheet 2 to reflect that.

Can anyone help?


I have two worksheets. I need to compare both worksheets then output a report that shows the difference between the sheets. I have that much done. I can run a compare and then populate an 'output' worksheet with the changed cells.

My issue is that I want the script to recognize the changed cell and copy the entire row that that discrepency cell is in, and copy that whole row to the output worksheet. I'm attempting it in a loop. It has to compare all the cells in the rows of both worksheets. Follows is the script. Sorry if it's sloppy but I'm fairly new with VBA. Any help would be much appreciated.

Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Dim chnCell As Long, sameCell As Long
Dim rwRange As Range, clRange As Range

If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
If rng1.Areas.Count > 1 Or rng2.Areas.Count > 1 Then
MsgBox "Can't compare multiple selections!", _
vbExclamation, "Compare Worksheet Ranges"
Exit Sub
End If
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
With rng1
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With rng2
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & _
Format(c / maxC, "0 %") & "..."
For r = 1 To maxR
On Error Resume Next
cf1 = rng1.Cells(r, c).FormulaLocal
cf2 = rng2.Cells(r, c).FormulaLocal
On Error GoTo 0

If cf1 <> cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", _
vbInformation, "Compare Worksheet Ranges"
Sheets("Sheet1").Copy Before:=Workbooks("RadarSen_r1.xls").Sheets(1)
Workbooks(2).Close False

End Sub

Hi there, and thank you in advance for any help you can give me regarding my problem!

I am trying to copy certain cell-referencing formulae from one workbook to another, without the original being referenced in the amended formula.

The Detailed Situation
I am running a small travel business, which is – along with a few other programs – pretty much run from Excel in terms of our paper invoices, bookings, etc.
There is an Excel workbook for each customer, with each workbook consisting of five or six separate worksheets, detailing various pieces of information related to the booking.
The two worksheets in question are ‘booking’ and ‘itinerary’. The latter worksheet contains a number of important fields (flight number, time, customer name, etc.), which are referenced to the relevant cells on the ‘booking’ worksheet. The current system being that my staff populate the booking worksheet whilst speaking to the customer, and the vital parts of the itinerary worksheet are automatically populated as a result.

The Detailed Problem
I have recently, due to changes not relevant to this forum, had to alter the format and look of the ‘itinerary’ worksheet. On the master booking workbook (the blank template which the staff use when a new booking is made), the new itinerary is written, but still obviously contains certain fields which refer to information entered on the ‘booking’ worksheet. This all runs fine, and there is no problem with new customers.

Old customers, however, are a different story.
Such is the nature of my business that actual bookings – from the initial call to the posting of tickets – can take several weeks. I thus have a large number of currently ‘live’ clients, that is to say clients who have booked but have yet to receive their details in the post. Each of these live clients has a workbook that contains the old style itinerary worksheet.

Now my problem is that I need to be able to send the new itinerary format to these clients, along with saving an updated copy of their workbook when I do.
I can delete the itinerary section on these old bookings, but when I try to copy/paste the new itinerary format to them I run into a problem with referencing.

To explain this a little more clearly, let’s take a hypothetical example:

Mr Smith booked a holiday with me four days ago. My staff entered his details using the blank booking form with still contained the old ‘itinerary’ worksheet.
Today, I receive the various invoices and tickets from the travel agencies I work with, so am now ready to send everything to Mr Smith. Included in this should be an invoice in the new style.
I open Mr Smith’s Excel workbook, jump to the old itinerary worksheet, and wipe it out, ready to be updated to the new style.
I now open up the blank booking form workbook, jump to the itinerary worksheet, and copy/paste it to Mr Smith’s itinerary worksheet.

Now the problem lies in the way the final formula gets written. Let’s say that cell E6 on the booking worksheet holds the customer’s title.
On the new booking form, with the updated itinerary, the cell which will display the customer’s title contains the formula =booking!E6
But once I copy/paste the new itinerary details across to an old form, that cell reads =’[BookingForm.xls]booking’!E6

The Desired Solution
So, obviously, an ideal situation would be some method by which I could graft the new itinerary format onto current clients (which number about 120-130), without then manually having to go through all the important data fields (about 17 in total), deleting the [BookingForm.xls] part.

Can anyone please advise?

I copy and paste cells from one worksheet to another. I made sure to open the second worksheet form File-> Open in the first worksheet. It sometimes happens that when I paste in the second worksheet, the value does not paste, it just give me a blank. In those instances, I tried paste special. This opened the little window that ask how to paste links (as objects / Bitmaps...) and not the normal Paste -> All / Formulas / Values / Formats.... window.
If i now save my worksheet, and reopen it, it tells me that it is corrupt.
Why can this be. Seems to happen randomly, making it more difficult to trace...

No luck finding an answer? You could always try Google.