Bloody Formulas! They never work when you want them to
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Bloody Formulas! They never work when you want them to

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. ****.
    _________________
    [b] Mark O'Brien

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

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Russell you are a life saver!

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark - I Don't really understand...

    WOuld it help if i gave the full macro?

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com