Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Command Button Paste Values & Worksheet Export

    I would like to execute a macros that will
    1) Export the Monthly_Report worksheet and open a new Workbook for it
    2) Copy and Paste Special Values the cells with formula arrays that reference the Data_Entry worksheet.

    Any help is always appreciated.

    Thanks
    Amy
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Amy,

    1) As a starting point, do you already have any code at all, or do you need this done from scratch?
    2) Can you clarify where are the cells with formula arrays, and where do you want them pasted to?

    Gary

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Gary,
    1) I do not have any code other than what I could pull together with the Macro recorder (which is generally not useful)
    ActiveSheet.Shapes("Button 1").Select
    Selection.Find(What:="!", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, MatchCase:= _
    False, SearchFormat:=False).Activate
    Sheets("PWC EAC").Select

    So I need this from scratch.

    2) For the purposes of highlighting the cells with formula arrays that reference another sheet (Data_Entry), I have highlighted the cells in yellow on the Monthly_Report worksheet. The actual workbook that I will be putting this code into does NOT have yellow highlighted cells. I was thinking it might be possible to write the VBA script such that is is first searching and selecting all cells of the selected worksheet that reference cells from another worksheet (the Find criteria would look for Data_Entry! in the Formula of each cell). I just want to copy those specific cells and paste special->values on the Exported copy of the Monthly_Report worksheet so that it doesn't maintain links to the parent workbook.

    The attached is a general mock-up workbook so I am not posting business sensitive information.

    Thanks
    Amy
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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