Bloody Formulas! They never work when you want them to

[alexb]

I have a work that I have created a macro to go with.

The macro cuts and pastes 1 of the sheets into a new workbook that I can email to a customer without him seeing the other sheets. However, when I email it internally (to check it works) my mate comes up with a prompt that says "this information is linked to another workbook - Do you want to update yes or no". If he clicks Yes it reverts back to the wrong priceing, customer name etc... How can I stop him haveing the prompt?

It is fine before he clicks yes...

 

[Russell Hauf]

On 2002-03-11 12:31, alexb wrote:
I have a work that I have created a macro to go with.

The macro cuts and pastes 1 of the sheets into a new workbook that I can email to a customer without him seeing the other sheets. However, when I email it internally (to check it works) my mate comes up with a prompt that says "this information is linked to another workbook - Do you want to update yes or no". If he clicks Yes it reverts back to the wrong priceing, customer name etc... How can I stop him haveing the prompt?

It is fine before he clicks yes...

You could cut and paste values when you copy the one sheet.

Selection.PasteSpecial Paste:=xlValues

Hope this helps,

Russell

 

[Mark O'Brien]

Are there formulas on this sheet that you're emailing that are still linked to other sheets in your original workbook?

If it's not necessary for the client to see or use the formulas, but just look at the pricing, then instead of copying the the whole sheet, you might want to programatically do a "copy|paste special|paste values" to stop the linking.


If I wrote a shorter answer then mine would have came in ahead of Russell's. He even gave you some code as well. ****.
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-11 12:56

 

[alexb]

Thank you Russell you are a life saver!

 

[alexb]

Only, How does my macro know which vales I want to have as a paste special? Will this automatically do all of them?

And one more thing - Where abouts would I put this within my Macro?

Sorry to sound lie the village idiot

 

[alexb]

Mark - I Don't really understand...

WOuld it help if i gave the full macro?

 

[alexb]

And - to make things even more interesting it only happens on one persons PC - I justr sent it to another and she got the same link but no matter what she cliked the information didn't change... This time

 

[Russell Hauf]

On 2002-03-11 12:57, alexb wrote:
Only, How does my macro know which vales I want to have as a paste special? Will this automatically do all of them?

And one more thing - Where abouts would I put this within my Macro?

Sorry to sound lie the village idiot

You could just paste the whole thing as values, unless the people you are sending this to need to be able to change certain values that are used in formulas...otherwise, just paste the whole thing as values.

If you show me the code where you are copying your one sheet, I can help you better.

Thx,

Russell

 

[alexb]

Sorry - Yes she does... She lied to me the first time.

 

[alexb]

OK then.

This is what I have. I hope it make more sense to you than it does me...

Sub email()
'
' email Macro
' Macro recorded 3/11/2002 by Base-Displays
'

'
Sheets("Sheet 1").Select
Range("E16:E73").Select
Selection.Copy
Sheets("sheet11").Select
Range("E22").Select
ActiveSheet.Paste
Sheets("Sheet 1").Select
ActiveWindow.SmallScroll Down:=82
Range("E87:E112").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("sheet11").Select
ActiveWindow.SmallScroll Down:=73
Range("E95").Select
ActiveSheet.Paste
Range("C92").Select
ActiveWindow.ScrollRow = 1
Range("A1:E160").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("B3").Select
Columns("A:A").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 26.43
Columns("B:B").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 14.71
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 64.14
Columns("C:C").ColumnWidth = 65.29
Columns("C:C").ColumnWidth = 66
Columns("C:C").ColumnWidth = 66.86
Columns("C:C").ColumnWidth = 67.71
Columns("D:D").Select
Columns("C:C").ColumnWidth = 69.57
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.Zoom = 75
Range("F18").Select
ActiveWindow.SmallScroll Down:=50
ActiveSheet.Shapes("Picture 6").Select
Selection.ShapeRange.ScaleWidth 0.92, msoFalse, msoScaleFromTopLeft
ActiveWindow.ScrollRow = 34
ActiveWindow.SmallScroll Down:=31
Rows("95:118").Select
Selection.RowHeight = 12.75
Range("D95").Select
ActiveWindow.SmallScroll Down:=23
Range("E120").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = " "
Range("D124").Select
ActiveWindow.ScrollRow = 121
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=123
ActiveWindow.ScrollRow = 1
Range("A1").Select
End Sub


Many thanks.