• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy paste Issue

C

c_rahul001

New Member
When i copy some value from one sheet and paste to other sheet the format of the sheets get changed. That is cell size and font size get increased. I cant use paste special function so is there any other way to keep the size same. I am not worried about Column width.
 
Hi Rahul ,


Can you clarify why you cannot use Paste Special , especially when it can do exactly what you want done ?


Narayan
 
The sheet is password protected and hence cant copy normally hence has used VBA code to copy the cells and when i try to paste special does not receive the paste special table, but receive other table in which say's can paste file with unicode, text, csv file and etc...so when i paste the copied material with normal paste function then actual area of copied text get maximized and enlarged for which need lot of format changes in the cells to bring the text to normal size.


I dont know why it is so but i guess i used ctrl + Scroll button of mouse to maximize and minimize the size of excel sheet which would have changed the format for same.
 
Hi Rahul ,


I am sorry I cannot understand your problem ; probably someone else can help.


However , if you can upload your workbook , then I can certainly help.


Narayan
 
The unicode, text, csv file dialogue usually appears when you are trying to copy from a separate instance of XL. Is it possible to change this?

You mention that the copy is being controlled by VB, is there a portion of this code that might be affecting the font?
 
Hi Rahul,


Can you post a copy of the VBA Code which is doing the copying?
 
Hi Luke/Narayan, Thanks for your help.


I am sorry i cant upload the file as will lead to discrepancy. But i can upload the file without text where u can see the table size and column width how it look like before and after i paste them using the paste function. as i am new to the group is not aware of how to upload the file and from where. Can any1 help me in same.

There is no portion of macro which is affecting the font size. As macro written is only for copy and no other commands are used.
 
Sub Macro1()


If Range("C5") = "NOT OUT OF BOX" Then

Range("B9:C32").Select

Selection.Copy

Else

Range("B9:F24").Select

Selection.Copy

End If


End Sub
 
I assume you run this and then manually paste the copied data ?


When I do that the formatting comes along ok


can you step us through what your doing ?
 
ya i manually paste to other worksheet. i use copy button which is assigned to macro and then paste it to other worksheet which paste the data but it increases the size while pasting the data.
 
Are you sure that the font size is changing or is the second worksheet at a different Viewing Scale to the first and so it just appears bigger ?
 
Ya i am sure the size get increased.The second worksheet is not at a different Viewing Scale to the first. How can i upload the file, would have uploaded the file.
 
Please read the 3 Green sticky posts at: http://chandoo.org/forums/
 
Hi i have pasted the link using the dropbox feature i dont know will it work or not as never used before.


I have removed all the information from the file but kept the table format as it is. You can copy using the copy button after protecting the file and then try to paste it to next sheet or file and you will find the difference. Also try using paste special future.


https://dl.dropbox.com/u/84077811/copy%20issue.xlsm
 
Hey Rahul,


If you are only worried about the font size, it is absolutely working perfect for me when I manually paste the data(after copying the dat through code as you did) in other sheet. The font type and size remain the same, arial black and 11, respectively.


Is there anything else that you wanted to point out which is probably I am not able to understand?
 
Hey Rahul,


If you are only worried about the font size, it is absolutely working perfect for me when I manually paste the data(after copying the dat through code as you did) in other sheet. The font type and size remain the same, arial black and 11, respectively.


Is there anything else that you wanted to point out which is probably I am not able to understand?
 
Hey Rahul,


If you are only worried about the font size, it is absolutely working perfect for me when I manually paste the data(after copying the dat through code as you did) in other sheet. The font type and size remain the same, arial black and 11, respectively.


Is there anything else that you wanted to point out which is probably I am not able to understand?
 
Hey Rahul,


If you are only worried about the font size, it is absolutely working perfect for me when I manually paste the data(after copying the dat through code as you did) in other sheet. The font type and size remain the same, arial black and 11, respectively.


Is there anything else that you wanted to point out which is probably I am not able to understand?
 
Good day c_rahul001


I have downloaded your file, entered some data, used your copy button and pasted into sheet two, no problems at all.You must have some problem with your workbook or EXCEL its self. Have you tried doing a repair on EXCEL.
 
Yes Kaushik,


Even i can notice that the font size remains same on the sheet and also height and width of rows and column does not get change but what i can see through necked eyes is that the space taken by same data needs bigger space and area in terms of original data and cells size get increased not in terms of font size but as compared to original data. if we compare the area we can get the difference, please check i have pasted the copied data in sheet1 from sheet copy issue and u can find the difference what i get in the link below.


https://dl.dropbox.com/u/84077811/copy%20issue.xlsm
 
Your comments seem to contain a bit of anger, I have looked at your spread sheets….but not through “necked eyes”, and when I do what I and others have done one our machines this does NOT HAPPEN, everything copies and pastes as it should do
 
I am so sorry if you felt that i used harsh words or they seem to be have anger in it no such intention. Apologies for same.I am not able to tell u exactly what word i should have used to (view) hence used necked eyes thought if will use view then excel has view option in it and would have increased the misunderstanding and mislead-ed to other subject.


" Even i can notice that the font size remains same on the sheet and also height and width of rows and column does not get change but what i can see through necked eyes is that the space taken by same data needs bigger space "


I wanted to tell through above sentence that even i am noticing same thing that nothing gets changed in excel sheet related to font, font size, column width, row height but only the area of data in the sheet get increased when i copy paste the data in other sheet. I guess i was not able to convey it clearly and properly. Apologies for it again.


I guess my excel properties would have been changed or may have some issue will get it repair and would let you know. As every one is able to view the sheet without any change. thank you for your help and support, and do appreciate same.
 
c_rahul001

Many people think that all mistakes or erroneous errors are down to them and it is their fault that mistakes happen, most of the time this is true but you must remember that the operating system (Microsoft) and the software(excel) was written by humans and there is (thankfully) no such thing as a perfect human especially programmers, at times the best thing to do is step back have a coffee and look at the problem from a different view……and then get a sledge hammer and beat the crap out of the monitor .expensive yes, but the satisfaction is worth it
 
Hey Rahul,


I understand and got your point(and agreed too)of the statement "the space taken by same data needs bigger space and area in terms of original data and cells size get increased not in terms of font size but as compared to original data".Probably I got the solution of your problem.

Why don't you run another macro to format the paste area as same as the source area.I noticed that the only problem is with the row height and column width that changes once you paste the data in other sheet.I would request you to do the following to:

---place a button somewhere on the sheet where u paste the data

---Assign the following macro to the button:


sub FormatIssue()

'FORMAT THE ROW HEIGHT

Sheets("Sheet1").Select

Rows("1:30").Select (note: you can, however, make this range dynamic according to ur need)

Selection.RowHeight = 15.75 (note:I noticed that ur source sheet's row height is 15.75)

'FORMAT THE COLUMN WIDTH

Sheets("Copy issue").Select

Columns("B:C").Select

Selection.Copy

Sheets("Sheet1").Select

Columns("C:D").Select

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False


Sheets("Copy issue").Select

Columns("E:F").Select

Selection.Copy

Sheets("Sheet1").Select

Columns("F:G").Select

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

End sub


----After copying and pasting the data in other sheet(NOTE:AS OF NOW,PASTE THE DATA ANYWHERE IN COLUMN C,HENCE THE PASTED AREA WILL BE IN THE RANGE OF COL C TO G.HOWEVER, IF U LET ME KNOW UR EXACT WORK REQUIREMENT, THIS PASTED RANGE CAN BE MADE DYNAMIC, THEN U DO NOT NEED TO WORRY ABOUT WHERE U PASTE THE DATA, THEN WHEREVER U PASTE, IT WILL TAKE THE FORMAT AUTOMATICALLY), hit the button to run the macro.


It works perfectly fine for me.


Hope this helps.


Regards,

Kaushik
 
You must log in or register to reply here.
Back
Top