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

[pincivma]

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

 

[My Aswer Is This]

Try something like this:

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

 

[pincivma]

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

 

[My Aswer Is This]

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.

 

[pincivma]

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

 

[Fluff]

How about

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("B9:H428").SpecialCells(xlConstants).Copy _
Workbooks("book2.xlsm").Sheets("Sheet2").Range("B9")

 

[pincivma]

How about

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"

 

[Fluff]

In that case how about

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

 

[pincivma]

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.

 

[Fluff]

This should stop the alerts

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.