Copying data as values from one Sheet to another? using a Command Button VBA

**e4excel** · 10-19-2009, 11:52 AM

Originally Posted by Whizbang

Just to clarify, you want to create a new workbook that contains only the values of the existing workbook, right?

No Not Exactly, I have not attacheed the complete workbook as it contains Confidential information..Basically The Sheet which in the Demo file is a Template where Selective information of different accounts from different Sheets in the same workbook is pulled via Formulas..

Now, this sheet acts a Tool to pull out information form different Sheets and therefore is Dynamic.

But I want the Data to be copied into a New Sheet as Values, Now since this can be achieved through a VBA MAcro, I needed help..as I am no good in the same..at all..

What I can Do is to have one more file with the different Sheet names and these Sheet Names stored somewhere lets say Cell F7 in the Copy to Sheet and then the Macro copies the data to the desired Sheet which is mentioned in the ropdown in Cell F7..

**Whizbang** · 10-19-2009, 02:45 PM

Please see the attached. Is this approximately what you want?

**Whizbang** · 10-19-2009, 03:29 PM

This is a version of your "Copy to Sheet" that does approximately the same thing as my previous attachment.

**e4excel** · 10-20-2009, 12:12 PM

Hi Whizbang,

You have done a good job but its still not complete as there are still some things pending though the logic is in the right direction...

HTML Code:

Sub Macro1()

    Sheets("Customer Values Template").Copy After:=Sheets("Trial")
    Sheets("Customer Values Template (2)").Name = "Customer Values"
    Range("K3").Value = Sheets("Trial").Range("K3").Value
    Range("K4").Value = Sheets("Trial").Range("K4").Value
    Range("K5").Value = Sheets("Trial").Range("K5").Value
    Range("K6").Value = Sheets("Trial").Range("K6").Value
    Range("K7").Value = Sheets("Trial").Range("K7").Value
    Range("K8").Value = Sheets("Trial").Range("K8").Value
End Sub

I liked the idea of having an Emtpy Template which gets copied after Sheets Trial, this is something which I learned from you...of having a Sheet after a particular Sheet.

The most important thing is that the Data gets copied only for a few cells and not for the complete file, Like if Im typing anything in the cthe Receipts or Withdrawal Side its not getting copied and the the Name of the New sheet is Customer Values, I wanted it to be the same as selected in the Dropdown...I have a path in the dropdown then I want the last text after the "\" in the path as the New Sheet Name...

But what I want to do is almost he same but in a different File like if I have a File path mentioned in the Dropdown then can this data be copied in the other file which will nly have values...

**Whizbang** · 10-20-2009, 12:36 PM

Give me some time. I think I know what you want now.

As for why not all cells are copied, that is only because I did just a few to show you how to do it. You can adjust it to read the following to copy ALL cells:

Code:

 Cells.Value = Sheets("Trial").Cells.Value

Or modify the Range() part to specify a cell or range of cells (use whatever cell ranges your sheet needs):

Code:

 Range("K3:K17").Value = Sheets("Trial").Range("K3:K17").Value

This second way is the way I believe would be best for you, since your template already has the descriptive text in it, and you just want the cell values that contain a specific type of information.

**e4excel** · 10-20-2009, 03:47 PM

Originally Posted by Whizbang

Code:

 Cells.Value = Sheets("Trial").Cells.Value

Or modify the Range() part to specify a cell or range of cells (use whatever cell ranges your sheet needs):

Code:

 Range("K3:K17").Value = Sheets("Trial").Range("K3:K17").Value

This second way is the way I believe would be best for you, since your template already has the descriptive text in it, and you just want the cell values that contain a specific type of information.

You are right I would like to go with the second option, however since you mentioned both the options, I have a question for the first option, Is this the syntax for getting all the Cells Copied?

And as I have been asking is it possible to get to do this to another sheet on a different File, then how do i write the Syntax for the File Path...

My IDeas is to have a complete differnt file in the same folder as this Trial Sheet and Copy the Values into Different Sheets at will..Is this possible..

Better would be to have a Different File generated and all sheets copied to its sheets at will via a command button..?

Pleas let me know the possibilities as I am not conversant with VBA at all?

Thanks for all the help till now...just a little bit though

**Whizbang** · 10-20-2009, 04:09 PM

Originally Posted by e4excel

You are right I would like to go with the second option, however since you mentioned both the options, I have a question for the first option, Is this the syntax for getting all the Cells Copied?

Yes, but when you copy all cells, it is slow because it copies the blanks as well. That is one reason to stay away from it. Another is that cell merges can cause errors when you copy and paste.

Originally Posted by e4excel

And as I have been asking is it possible to get to do this to another sheet on a different File, then how do i write the Syntax for the File Path...

Yes, it is possible. The file needs to be opened to copy. Here is the syntax. You could shorten this a bit because you don't have to define the active window, but the below code works regardless of which window is active. Filepath can include relative or fixed file paths, but must be exact. If it is in the same directory use "Activeworkbook.path &" and then the filename in quotes, but if it is on a different server, it can get tricky.

Code:

Workbooks.Open Filename = Activeworkbook.path & "\Copy to Sheet.xls"
    Windows("Copy to Sheet.xls").Sheets("Trial").Range("A1").Copy
    Windows("Copy to Sheet(1).xls").Sheets("Trial").Range("A1").PasteSpecial xlFormulas

Originally Posted by e4excel

My IDeas is to have a complete differnt file in the same folder as this Trial Sheet and Copy the Values into Different Sheets at will..Is this possible..

Yes. I am working on this for you to use as an example.

**Whizbang** · 10-20-2009, 04:50 PM

Please see the two attached files. Are they approximately what you want?

**e4excel** · 10-21-2009, 03:37 PM

Dear Whizbang,

You are going in the right direction whizbang but there are some technical challenges because of which its slightly more difficult..

Let me explain some challenges...
The sheet which is to be copied is made up of selective data from other sheets and therefore the No of Rows and Columns are not fixed so unfortunately we cannot have a template of a fixed size copied...Sorry I realized it now, though I had liked the idea originally of having a template..

The Sheet to be copied is actually a Bifurcation of a Bank Statements as in I have replicated all Pass-Book entries or Bank Statements and these entries get seperated into Credit and Debit entries into this Sheet which I want to Store as Values in some other file with the name of the Account no..

Therefore the data changes with the amount of Credit and Debit entries in ach Account and so is the Dimension of the data to be copied and pasted as value..

If you check the file I have uploaded it has conditional formatting which makes up sub-blocks and blocks depending on the data entered.

I want the values as well as the Formatting , Column Width to be copied exactly as it is as a replica..

Please revert for any information..

**Whizbang** · 10-22-2009, 03:59 PM

I haven't forgot about you. I got busy at work and so haven't been able to devote myself to this problem as much as I'd like. I'll try to get something to you before the end of the week, however.

**e4excel** · 10-22-2009, 04:15 PM

Originally Posted by Whizbang

I haven't forgot about you. I got busy at work and so haven't been able to devote myself to this problem as much as I'd like. I'll try to get something to you before the end of the week, however.

No Problems Brother,
You mentioning that means a lot to me, In the meantime Im also trying to try to learn from Recordable Macros but unfortunately not able to customise them..
Wait for you...Please lemme know if you need to ask something

**Whizbang** · 10-22-2009, 05:48 PM

Please see the attached file. This does everything I think you want it to.

One thing I don't like about it is that it still uses a blank template. Since the the Trial worksheet grabs only the values of the customer sheet, it would seem a simple step to copy the Trial sheet directly and just remove the buttons, etc. But, because I set the code in the sheet to clear all values when the cel Q1 is clear, when you copy the sheet and then delete the information to the right of column O, the cells clear. I attempted to look for a way to copy a sheet without the underlying code, but couldn't find an easy solution.

So, for now it seems you'll have to copy the values twice. Once to view it in the trial sheet and once when you create the back-up sheet.

One potential work around would be to add the button to each customer sheet that then creates a new workbook and then copies the values.

**e4excel** · 10-22-2009, 06:37 PM

Hey Whizbang,

I am personally satisfied with your efforts till now but just one concern is whether this can be independent of the empty sheet..as My original sheet from where data is to be copied is going to change shape i.e Dimension with every new account pulled in it..and via conditional formatting it will only format boxes wherever there is data...

As in your case as you are making use of the Empty Sheet it will be difficult to get a different Sheet copied...Its just a concern as I still need to test it further...

But thnks a lot...hope it works well..
Still your efforts are worthy of appreciation for not onl helping but also staying till the en..

**Whizbang** · 10-23-2009, 10:52 AM

One option to avoid formatting is to have the macro in the customer workbook. Then you just navigate to the customer sheet you want, run the macro, and it will copy the sheet to the Trial workbook as values only.

The other option is placing the macro in the Trial sheet and selecting the customer from a list. However, I had originally assumed that you wanted to view the information prior to copying it. Parhaps this is not the case. If we were to avoid all issues with formatting, then the only thing I can see to do is to not be able to view the sheet from the Trial workbook before copying it.

Anyway, here is the two files. Both have macros in them. The Customers workbook macro is set to ctrl+z. The Trial workbook macro is set to the button.

I have attached both examples.

**e4excel** · 10-26-2009, 03:42 AM

Originally Posted by Whizbang

One option to avoid formatting is to have the macro in the customer workbook. Then you just navigate to the customer sheet you want, run the macro, and it will copy the sheet to the Trial workbook as values only.

The other option is placing the macro in the Trial sheet and selecting the customer from a list. However, I had originally assumed that you wanted to view the information prior to copying it. Parhaps this is not the case. If we were to avoid all issues with formatting, then the only thing I can see to do is to not be able to view the sheet from the Trial workbook before copying it.

Anyway, here is the two files. Both have macros in them. The Customers workbook macro is set to ctrl+z. The Trial workbook macro is set to the button.

I have attached both examples.

Sorry for the later response, I tried the File but it did not open and got an error END DEBUG, I could not really understand it completely, though I must say you have put a lot of efforts but it is still not meeting the requirements..

I feel there is not a need of Macros in both the Files, I will explain again as I feel that you have been putting a lot of efforts but unfortunately its getting complicated.

The Sheet to be copied or replicated is going to be different in size and Shape as it will have information form different Accounts and for different periods..

However, since this sheet will always be dynamic , I need to have an additional Sheet in the same file or preferably in a different file in the same folder, I would appreciate if you could help me with such a VBA code where the command button is present in the Main Sheet and it copies the entire data with conditional formating Borders and only as values in the other Sheet.

Please if you can explain the VBA code it will be convenient incase I need to modify as per the requirements I can do it then...

Thread: Copying data as values from one Sheet to another? using a Command Button VBA

Thread Tools

Display

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Re: Copying data as values from one Sheet to another? using a Command Button VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions