Dear Forum,
I have a Sheet in which the data is calculated by changing the Sheet reference and also by the period..
Now I need to transfer this data as values, however there are certail cells which are merged and therefore I get a prompt as I cannot paste as values using Paste Special-> Values..
I would be glad if someone could help me perform this operation by virtue of a Command Button...I am a total novice in VBA..
Last edited by e4excel; 10-09-2009 at 10:24 AM.
Use of merged cell should be avoided as they generally cause more problems than they are worth.
If possible, select the merged cells, un-merge then and then use the "Center Across Selection" option for the horizonatal alignment. This will get rid of the pastespelcial error. This solution only works for horizontallt merged cells, not vertical merged.
You would have to upload a sample workbook to get code tailored to your sheet.I would be glad if someone could help me perform this operation by virtue of a Command Button...I am a total novice in VBA..
Last edited by Palmetto; 10-17-2009 at 02:52 PM.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Dear Palmetto,
Due to the confidential nature of the file I am unable to upload the file and it shall take a lot of time to create a Dummy...
Is there a way I can get the exact copy of the original sheet as values as the cells cannot be unmerged as they are merged owing to the requirement of the file..
Thanks for the offer..
Last edited by e4excel; 10-11-2009 at 02:52 AM. Reason: Spelling mistake
Bump thread..!
Please does anyone have some suggestion or a solution or even a workaround...its very tedious to copy the sheet as values as there are some Named references which keep on getting copied everytime...
I am looking for some suggestions to copy the Sheet as I have a template where I get the Data in a Dynamic form but need to be stored seperately as Value, but due to the Name Defines and Some merged cells cause a problem to copy the data...
I know this is difficult to understand without a FIle , I will just a upload a Dummy file to give an idea...
Looking at the file One can understand the reason why certain cells have been merged as its the design's demand.
Can someone put a Command button and Copy it to another Sheet 2..
You want to copy a sheet without carrying over the formulas, and have only the values?
select all the cells with data, CTRL + C for copy, go to a new workbook, right click on a cell, choose PASTE SPECIAL, and choose VALUES ONLY.
This will paste all the cell values displayed with no formulas of any sort present.
Edit:
See attached. Copies the worksheet to a new sheet, renames it according to its codename (Couldn't think of anything else). Copies the page, and pastes values only on top of itself.
Last edited by mewingkitty; 10-17-2009 at 12:48 PM.
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Mew,
I am aware of the Cntrl + C, Paste Special -> Values, however the file contains formulas and some cells merged and also Named Ranges which conflict in copying into another sheet..
The Uploaded file is a demo without any formulas as its not possible to upload the actual file due to sensitive information but whe I tried to copy it was not possible and I need to get the value Sheets for several such accounts..
Its a tedious task to keep on doing it each cell-wise, would have been easier with the help of VBA -> Command Button could have achieved the same, am getting a difficulty due to the merged cells which cannot be changed as its the design's requirement and also soemtime due to the Name Defines...
So any suggestion would be handy...
I didn't say copy it to another sheet
I said copy the SHEET, then copy the VALUES on top of themselves to remove formulas.
Did you see the edit to my last post? It includes a working VBA example.
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Below is Mew's code amended.
Code:Private Sub CommandButton1_Click() Application.ScreenUpdating = False Application.DisplayAlerts = False Sheets("Trial").Copy After:=Sheets(1) Sheets("Trial (2)").Select Sheets("Trial (2)").Name = Sheets("Trial (2)").CodeName With ActiveSheet .Cells.Copy .Cells.PasteSpecial xlPasteValues End With Application.CutCopyMode = False Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Hi Mew,
I had not checked it earlier but it works well but the only thing is that I get the Command Button in the other sheet as well..can that be prevented to be appearing in the other Sheet.
And can I get the command to copy in a predefined or prenamed Sheet so that each account information is copied in the appropriate account.I am sorry if this was coming as a new requirement, but I thought I would be able to modify your code which I cant bcos of alien coding..
Half-baked knolwedge in VBA...
Can you please explain the coloured lines...I thought that I would able to tweak on the code but your code is beyond my capacity in terms of my VBA knowledge which is just 1% as of now.Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Trial").Select
Sheets("Trial").Copy After:=Sheets(1)
Sheets("Trial (2)").Select
Worksheets("Trial (2)").Range(Sheets("Trial (2)").Cells(1, 1), Sheets("Trial (2)").Cells(29, 15)).Select
Selection.Copy
Sheets("Trial (2)").Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Trial (2)").Cells(1, 1).Activate
Sheets("Trial (2)").Name = Sheets("Trial (2)").CodeName
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
This is a new development so please dont feel Im burdeining you in any way, If I make a new file where I want all the Values and Name each Sheet with the account no, then can the information from the Main Sheet be made to go in the Sheet of Appropriate account..?
Just wanted to customise certain things on my own but your VBA code is not my cup of tea brother...!
Last edited by e4excel; 10-17-2009 at 03:28 PM.
These generally speed up code. Screenupdating means it won't show changes, it will just do them, then pop them all in at the end. There's also a command to disable automatic calculations for the duration of a sub. Note they're both set back to true at the end of the sub.Code:Application.ScreenUpdating = False Application.DisplayAlerts = False
Since we've made a copy of the sheet named "Trial", this is what it will be named, this therefore selects the sheet which we've just created.Code:Sheets("Trial (2)").Select
This selects cell(1, 1) which is A1 on sheet "Trial (2), the new sheet which we've just madeCode:Worksheets("Trial (2)").Cells(1, 1).Activate
This is renaming the sheet. I suppose I didn't really have to do this but I wanted to show that you can rename it to whatever you want. In this case the "codename" of the sheet is the name associated with it by Excel. you could change this - Sheets("Trial (2)").CodeName - into - "Cat" and it would name the sheet "Cat".Code:Sheets("Trial (2)").Name = Sheets("Trial (2)").CodeName
Let me know if that helps. I tried to cover those as best as I could and I hope that helps you out some!
mew!
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Thanks for taking out time to explain the code , that was very useful but can you also help me for the other reequirments:Originally Posted by mewingkitty
My other qurey was can I have another File ready with all the account nos as the names of the Sheets and I can just paste Values from this Sheet to the other file by just providing the path somewhere in this Main Sheet...
I also do not want the Command Button to appear on the Copied Sheet...can that be removed?
I didn't take the time to read the last couple of posts, but it seems to me the easiest way is to just protect all cells in the sheet to have hidden formulas. That way, when you copy, you can ONLY copy the values.
Code:Cells.Locked = True Cells.FormulaHidden = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Cells.Copy Sheets("Sheet2").Paste ActiveSheet.Unprotect
Ok I sahll Try that for sure whizbang, but can I paste this data in a particular sheet via command button...
I have two files one in which I have all the account information getting pulled in this particular Sheet and now in another file I have just the values so I want to create the File with the names of Account nos and then want the data to be pasted in the appropriate sheet selected in the Main Sheet from where I would be pasting the data and there's one more difficulty of removing the Command button in the Copied Sheet..
Just to clarify, you want to create a new workbook that contains only the values of the existing workbook, right?
Simply change the "Test.xls" and "Test2.xls" to the appropriate filenames and filepaths. To manage the merged cells and other formatting, you may want to create an empty template file and instead of "Woorkbooks.Add" above, you would do " Workbooks.Open Filename:="Test2.xls" "Code:Workbooks.Add ActiveWorkbook.SaveAs Filename:="Test2.xls", FileFormat:=xlNormal Windows("Test.xls").Activate Cells.Locked = True Cells.FormulaHidden = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Cells.Copy Windows("Test2").Activate ActiveSheet.Paste ActiveWorkbook.Save Windows("Test.xls").Activate ActiveSheet.Unprotect
Then just assign this macro to your command button. To prevent the button from copying over, right-click on the button, select "Format control". Then go to the "Properties" tab and select "Don't move or size with cell" and click "Ok"
Last edited by Whizbang; 10-19-2009 at 11:34 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks