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

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
172
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,082
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
 

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
172
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,082
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

Board Regular
Joined
Dec 12, 2004
Messages
172
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
How about
Code:
Workbooks("Book1.xlsm").Sheets("Sheet1").Range("B9:H428").SpecialCells(xlConstants).Copy _
Workbooks("book2.xlsm").Sheets("Sheet2").Range("B9")
 

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
172
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"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
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
 

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
172
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,569
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,078,516
Messages
5,340,875
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top