Bloody Formulas! They never work when you want them to

alexb · Mar 11th, 2002, 01:31 PM

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 · Mar 11th, 2002, 01:53 PM

Quote:

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 · Mar 11th, 2002, 01:55 PM

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. Damn.

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-03-11 12:56 ]

alexb · Mar 11th, 2002, 01:56 PM

Thank you Russell you are a life saver!

alexb · Mar 11th, 2002, 01:57 PM

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 · Mar 11th, 2002, 01:59 PM

Mark - I Don't really understand...

WOuld it help if i gave the full macro?

alexb · Mar 11th, 2002, 02:03 PM

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 · Mar 11th, 2002, 02:05 PM

Quote:

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 · Mar 11th, 2002, 02:05 PM

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

alexb · Mar 11th, 2002, 02:09 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 01:31 PM	#1
alexb New Member Join Date: Mar 2002 Posts: 17	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...

Mar 11th, 2002, 01:55 PM	#3
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	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. Damn. _________________ [b] Mark O'Brien [ This Message was edited by: Mark O'Brien on 2002-03-11 12:56 ]

Mar 11th, 2002, 01:56 PM	#4
alexb New Member Join Date: Mar 2002 Posts: 17	Thank you Russell you are a life saver!

Mar 11th, 2002, 01:57 PM	#5
alexb New Member Join Date: Mar 2002 Posts: 17	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

Mar 11th, 2002, 01:59 PM	#6
alexb New Member Join Date: Mar 2002 Posts: 17	Mark - I Don't really understand... WOuld it help if i gave the full macro?

Mar 11th, 2002, 02:03 PM	#7
alexb New Member Join Date: Mar 2002 Posts: 17	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

Mar 11th, 2002, 02:05 PM	#9
alexb New Member Join Date: Mar 2002 Posts: 17	Sorry - Yes she does... She lied to me the first time.

Mar 11th, 2002, 02:09 PM	#10
alexb New Member Join Date: Mar 2002 Posts: 17	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.