VBA to Paste Values

[thewiseguy]

Hi all,

Currently I am using the following VBA to copy from one workbook to another:

'Copy range to another workbook
Workbooks("Catalog.xlsm").Worksheets("Fixtures").Range("B4:AC1000").Copy("Proposal.xlsm").Worksheets("Fixtures").Range ("B4:AC1000")

How can i change this to paste values? I don't want the formats to come along into the other workbook.

Any help is greatly appreciated!

 

[6StringJazzer]

After you open the workbook you can save it with a different name. It is a better practice to use an object instead of repeating the name each time. Your code assumes that the proposal file is already open. I'll keep that assumption, but the code could open it for you.

Here is a rewrite of your code. You need to decide how you want to name the new copy. I have a placeholder for you.

Sub Copy_Method()

   'Copy range to another workbook using Range.Copy Method\
   Dim Proposal As Workbook
   Dim Catalog As Workbook
   Dim SheetName As Variant
   
   Set Proposal = Workbooks("Proposal.xlsm")
   Set Catalog = Workbooks("Catalog.xlsm")
   
   Proposal.SaveAs Filename:="My New Proposal.xlsm"
   
   For Each SheetName In Array("EXS Catalog", "Fixtures", "Lamps", "Retrofit_Kits", "No_Measure", "Accessories", "Controls", "Materials", "Recycling", "Rental", "Labor")
      Proposal.Worksheets(SheetName).Range("B4:AD1000").Value = Catalog.Worksheets(SheetName).Range("B4:AD1000").Value
   Next SheetName

End Sub

 

[thewiseguy]

After you open the workbook you can save it with a different name. It is a better practice to use an object instead of repeating the name each time. Your code assumes that the proposal file is already open. I'll keep that assumption, but the code could open it for you.

Here is a rewrite of your code. You need to decide how you want to name the new copy. I have a placeholder for you.

Sub Copy_Method()

   'Copy range to another workbook using Range.Copy Method\
   Dim Proposal As Workbook
   Dim Catalog As Workbook
   Dim SheetName As Variant
 
   Set Proposal = Workbooks("Proposal.xlsm")
   Set Catalog = Workbooks("Catalog.xlsm")
 
   Proposal.SaveAs Filename:="My New Proposal.xlsm"
 
   For Each SheetName In Array("EXS Catalog", "Fixtures", "Lamps", "Retrofit_Kits", "No_Measure", "Accessories", "Controls", "Materials", "Recycling", "Rental", "Labor")
      Proposal.Worksheets(SheetName).Range("B4:AD1000").Value = Catalog.Worksheets(SheetName).Range("B4:AD1000").Value
   Next SheetName

End Sub

Hi I appreciate your efforts on this. I am a little confused. Let me try to explain exactly what I am trying to accomplish.

"Catalog" and "Proposal" both get opened at the same time. I input data into "proposal" and then save "proposal" as something else...i.e "ABC.Company.Proposal." A week later I need to revise this proposal so I open up ""ABC.Company.Proposal" and I need to re-run the copy VBA b/c I need to input some new products into this existing proposal. Currently if I re-run the copy VBA it will not work, so if possible, I'd like it to be able to copy the data from "Catalog" back into the proposal.

Does this make more sense?

 

[thewiseguy]

**edit - so I used the following in place of your placeholder:

Proposal.SaveAs FileName:=Worksheets("Dashboard").Range("F5")

It worked perfectly. However, here is the next problem. I don't know what products I am going to be adding to "Catalog" until I open "Proposal" and start inputting data. So in an example, both docs are opened, i start to input data, then I realize I need a new product. I enter in the new product, run the VBA and there is my new product. I keep inputting data and realize I need a new product, but now I can run the VBA again b/c the name has changed. I guess I could always save it back to "Proposal" but is this the easiest way?

 

[6StringJazzer]

I am getting a little lost.

Are you saying that:

1. You open Proposal.xlsm
2. You save Proposal.xlsm as a new file, named by what's in cell F5 of...I don't know what. Which file contains sheet Dashboard? For now we'll call that file NewProp.xlsm.
3. Then your code copies a bunch of stuff from workbook Catalog to NewProp.xlsm.
4. All good.
5. Whoops! Now you need to update the Catalog file, and you need to copy that to NewProp.xlsm. But your code wants to open the original Proposal.xlsm to copy the data to, not the new copy.

That's a design flaw in the way you have structured your code. You need to separate the code that creates the new file with the code that copies content to the new file, and make it two separate operations. If NewProp.xlsm is still open this is a little easier and you can refer to the file as Workbooks(Worksheets("Dashboard").Range("F5")). I would set a variable

Dim NewProp As Workbook
Set NewProp = Workbooks(Worksheets("Dashboard").Range("F5"))

then the copy code can copy to NewProp.

 

[thewiseguy]

I am getting a little lost.

Are you saying that:

1. You open Proposal.xlsm
2. You save Proposal.xlsm as a new file, named by what's in cell F5 of...I don't know what. Which file contains sheet Dashboard? For now we'll call that file NewProp.xlsm.
3. Then your code copies a bunch of stuff from workbook Catalog to NewProp.xlsm.
4. All good.
5. Whoops! Now you need to update the Catalog file, and you need to copy that to NewProp.xlsm. But your code wants to open the original Proposal.xlsm to copy the data to, not the new copy.

That's a design flaw in the way you have structured your code. You need to separate the code that creates the new file with the code that copies content to the new file, and make it two separate operations. If NewProp.xlsm is still open this is a little easier and you can refer to the file as Workbooks(Worksheets("Dashboard").Range("F5")). I would set a variable

Dim NewProp As Workbook
Set NewProp = Workbooks(Worksheets("Dashboard").Range("F5"))

then the copy code can copy to NewProp.

hi again. i am getting a mismatch error on the 2nd line?

 

[thewiseguy]

Sub Copy_Method()
'Copy range to another workbook using Range.Copy Method

'Copy range to another workbook


Workbooks("Proposal.xlsm").Worksheets("EXS Catalog").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("EXS Catalog").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Fixtures").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Fixtures").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Lamps").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Lamps").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Retrofit_Kits").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Retrofit_Kits").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("No_Measure").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("No_Measure").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Accessories").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Accessories").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Controls").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Controls").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Materials").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Materials").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Recycling").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Recycling").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Rental").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Rental").Range("B4:AD1000").Value
Workbooks("Proposal.xlsm").Worksheets("Labor").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Labor").Range("B4:AD1000").Value


Dim NewProp As Workbook
Set Proposal.xlsm = Workbooks(Worksheets("Dashboard").Range("F5"))


End Sub

 

[6StringJazzer]

Set Proposal.xlsm = Workbooks(Worksheets("Dashboard").Range("F5"))

Compare that line of the code to the line of code I gave you.

But that's also not how I intended for you to use it. You should be doing this at the top, and then referring to NewProp, not Workbooks("Proposal.xlsm").

 

[thewiseguy]

sorry, im new to VBA. Now what am I doing wrong? Still getting the same error.

Sub Copy_Method()
'Copy range to another workbook using Range.Copy Method

'Copy range to another workbook

Dim NewProp As Workbook
Set NewProp = Workbooks(Worksheets("Dashboard").Range("F5"))


NewProp.Worksheets("EXS Catalog").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("EXS Catalog").Range("B4:AD1000").Value
NewProp.Worksheets("Fixtures").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Fixtures").Range("B4:AD1000").Value
NewProp.Worksheets("Lamps").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Lamps").Range("B4:AD1000").Value
NewProp.Worksheets("Retrofit_Kits").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Retrofit_Kits").Range("B4:AD1000").Value
NewProp.Worksheets("No_Measure").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("No_Measure").Range("B4:AD1000").Value
NewProp.Worksheets("Accessories").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Accessories").Range("B4:AD1000").Value
NewProp.Worksheets("Controls").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Controls").Range("B4:AD1000").Value
NewProp.Worksheets("Materials").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Materials").Range("B4:AD1000").Value
NewProp.Worksheets("Recycling").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Recycling").Range("B4:AD1000").Value
NewProp.Worksheets("Rental").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Rental").Range("B4:AD1000").Value
NewProp.Worksheets("Labor").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Labor").Range("B4:AD1000").Value





End Sub

 

[thewiseguy]

I thought this one would work but to no avail....

Sub Copy_Method()
'Copy range to another workbook using Range.Copy Method

'Copy range to another workbook

Dim NewProp As Workbook
Set NewProp = Workbooks(Worksheets("Dashboard").Range("F5"))


Workbooks("NewProp.xlsm").Worksheets("EXS Catalog").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("EXS Catalog").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Fixtures").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Fixtures").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Lamps").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Lamps").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Retrofit_Kits").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Retrofit_Kits").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("No_Measure").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("No_Measure").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Accessories").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Accessories").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Controls").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Controls").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Materials").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Materials").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Recycling").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Recycling").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Rental").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Rental").Range("B4:AD1000").Value
Workbooks("NewProp.xlsm").Worksheets("Labor").Range("B4:AD1000").Value = Workbooks("Catalog.xlsm").Worksheets("Labor").Range("B4:AD1000").Value





End Sub

 

[thewiseguy]

Compare that line of the code to the line of code I gave you.

But that's also not how I intended for you to use it. You should be doing this at the top, and then referring to NewProp, not Workbooks("Proposal.xlsm").

Hi again - I don't need a code to change the name of the workbook. I am only looking for the copy code to work in whatever workbook I am working in (b/c the names are always different).

Many thanks if you can take a stab at this!