Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Copy only texts and values only but not formulas from one workbook to another
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy only texts and values only but not formulas from one workbook to another

    Is there a macro that copies only text and values but leaves the formulas in their cells when copying ranges from one workbook to another? Some ranges have formula in them but I do not want to copy them from one workbook to another.

    ODIN was kind enough to send me the code that copies formulas from workbook1 to workbook2. Here is the code he gave me. I modified it a bit. Now I need a macro that copies everything else but formulas from workbook1 to workbook2. Just to be clear, Workbook1 range("B9:H428) has both text and numbers and formulas. I want to copy over everything in Range("B9:H428) from workbook1 to workbook2 except formulas. In other words, if a formula is in say cell D15 in workbook1, this cell does not get copied over to cell D15 in workbook2. Cell D15 in workbook2 might have a different formula in there and I want that formula to stay in Cell D15.

    Sub Test()

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Workbooks("worbook2").Worksheets("Sheet1").Range("B9:H428").Formula = Workbooks("workbook1").Worksheets("Sheet1").Range("B9:H428").Formula

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    Try something like this:
    Code:
    Private Sub CommandButton1_Click()
    'Modified  2/21/2019  6:26:36 PM  EST
    Workbooks("worbook2").Worksheets("Sheet1").Range("B9:H428").Copy
    Workbooks("workbook1").Worksheets("Sheet1").Range("B9:H428").PasteSpecial xlPasteFormats
    Workbooks("workbook1").Worksheets("Sheet1").Range("B9:H428").PasteSpecial xlPasteValues
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    I tried your macro and it converts the formulas in Workbook1 as values. I don't want the formulas converted into values. I want the formulas in workbook1 to stay as formulas. All I want is to copy Range("B9:H428) from workbook2 into Workbook1 but copy everything in that range except formulas. But I do not want formulas in workbook1 converted into values. Those formulas should stay as formulas. Let me put it a different way. Worksheet2 has IF formulas in Range("B9:H428") that I do not want copied over into workbook1. Now Workbook1 has Match Index formulas in the Range("B9:H428") that I want to be left alone. Is there a macro can do that??

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    So if the cell has a formula copy nothing. Not the value not the formatting not the results of the formula.

    This is beyond my knowledgebase.
    I will continue to monitor this thread to see what I can learn.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    YES!!!. This is also beyond my knowledge of macro programming too. Is there anyone out there that has a macro that can do what I want to achieve??

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    How about
    Code:
    Workbooks("Book1.xlsm").Sheets("Sheet1").Range("B9:H428").SpecialCells(xlConstants).Copy _
    Workbooks("book2.xlsm").Sheets("Sheet2").Range("B9")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Dec 2004
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Workbooks("Book1.xlsm").Sheets("Sheet1").Range("B9:H428").SpecialCells(xlConstants).Copy _
    Workbooks("book2.xlsm").Sheets("Sheet2").Range("B9")
    Hi Fluff

    I tried your code and I get Run time error 1004 "This Action wont work on multiple selections"

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    In that case how about
    Code:
    Dim rng As Range
    For Each rng In Workbooks("Book1.xlsm").Sheets("sheet1").Range("B9:H428").SpecialCells(xlConstants).Areas
       rng.Copy Workbooks("Book2.xlsm").Sheets("sheet2").Range(rng.Address)
    Next rng
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Dec 2004
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    Hi Fluff
    1. We are getting closer but not just quite there. The first issue is this. When I run the macro I get a lot of pop ups that say this
    "A sheet or formula you want to copy contains the name "Mixer" which already exists in the destination sheet. Do you want to use the name defined in the destination sheet, click YES. To rename the range referred to in the destination sheet click NO and enter a different name in the conflict dialog box". I counted around 40 or so of these pop up boxes with different names for me to click YES or NO too. I just kept on clicking YES since I do not want to change any names. Is there a way to keep these pop ups from popping up since I do not want to change the names.

    2 In the source workbook I have a lot of drop down boxes but when the data gets copied over to the destination workbook the drop down boxes still show the drop down arrow but the drop down list is no longer there and I need the drop down list to also show up in the destination workbook.

    Both the source workbook and the destination workbook have the same data. The only data that is different are the formulas. In the source workbook I have VLookUp formulas and in the destination workbook I have Index Match formulas. I want to keep the Index Match formulas in the Destination workbook. I do not want them overridden with the VLookUp formulas from the source workbook.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Copy only texts and values only but not formulas from one workbook to another

    This should stop the alerts
    Code:
    Dim rng As Range
    Application.DisplayAlerts = False
    For Each rng In Workbooks("Book1.xlsm").Sheets("sheet1").Range("B9:H428").SpecialCells(xlConstants).Areas
       rng.Copy Workbooks("Book2.xlsm").Sheets("sheet2").Range(rng.Address)
    Next rng
    Application.DisplayAlerts = True
    But I have no idea how to handle the data validation.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •