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]

That syntax is invalid. It's better if you copy directly from your code. I think I know what it's supposed to look like, though.

Try this update:

Workbooks("Proposal.xlsm").Worksheets("Fixtures").Range ("B4:AC1000").Value = Workbooks("Catalog.xlsm").Worksheets("Fixtures").Range("B4:AC1000").Value

 

[thewiseguy]

That syntax is invalid. It's better if you copy directly from your code. I think I know what it's supposed to look like, though.

Try this update:

Workbooks("Proposal.xlsm").Worksheets("Fixtures").Range ("B4:AC1000").Value = Workbooks("Catalog.xlsm").Worksheets("Fixtures").Range("B4:AC1000").Value

worked perfectly, thank you for your help!

 

[6StringJazzer]

You're welcome!

 

[thewiseguy]

Is there anyway to alter this code so that when i change the name of "proposal" doc, the VBA will still run?

 

[6StringJazzer]

That depends. How will you change it? The name of the file has to be in the code. You either have to change the code to match when you change the name of the document, or you have to have a method for naming the document so the code can determine it dynamically.

Just as an example, if you use the current date in the filename:

Proposal 2024-08-27.xlsm

then each time it runs, the code can build the file name using the current date.

Another option is to have the code prompt you for the name each time it runs.

 

[thewiseguy]

That depends. How will you change it? The name of the file has to be in the code. You either have to change the code to match when you change the name of the document, or you have to have a method for naming the document so the code can determine it dynamically.

Just as an example, if you use the current date in the filename:

Proposal 2024-08-27.xlsm

then each time it runs, the code can build the file name using the current date.

Another option is to have the code prompt you for the name each time it runs.

what if i designated one cell as the name of the document, and when i saved it, the document automatically saved itself as whatever is in the designated cell??

 

[6StringJazzer]

So you're going to open a document, copy some stuff into it, then save it with a different name? Definitely doable but I would need to see the part of your code that saves the file. This goes beyond your original question.

 

[thewiseguy]

So you're going to open a document, copy some stuff into it, then save it with a different name? Definitely doable but I would need to see the part of your code that saves the file. This goes beyond your original question.

I don't have any code pertaining to saving

 

[thewiseguy]

OK so here is my entire query:

Currently I am keeping 2 worksheets: (1) "Proposal": this is my proposal generator. I input stuff and it spits out a proposal. (2) "Catalog": this is where I keep a catalog of all our products. I use the following code to copy over the various types of products. I do this so that I only have 1 document (catalog) to update on a continual basis:

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
End Sub


The problem I am having now is that if I save "proposal" to anything else, obviously the copy VBA wont work. I need to save this document to a new name, each time I use it b/c it is for a specific customer.

I guess the question is: Can I keep these 2 workbooks linked together so that the catalog can continuously be copied over to the proposal AND have the proposal document, be renamed??