Paste special value - Macro

[Sab_1]

I need help and thank you in advance.

How can I paste special value from specific range with formulas and sheet to another specific range without formulas and sheet?

Sab

[JBeaucaire]

So if you wanted to copy from A1:D5 from on Sheet1 to the currently selected cell on whatever the activesheet is:

Please Login or Register  to view this content.

[Doc.AElstein]

Hi Sab_1
_ I started before JB posted... so i finished !!

The following code will take this in the first Worksheet of the File the code is in
Using Excel 2007 32 bit

Row\Col	A
1	=1

Sheet1

And return you this in the Second Worksheet of the File the code is in
Using Excel 2007 32 bit

Row\Col	A
1	1

Sheet2

Code:
'

Please Login or Register  to view this content.

See also
http://www.excelforum.com/showthread...t=#post4414284
http://www.excelforum.com/showthread...95#post4256195
Post #25 http://www.mrexcel.com/forum/excel-q...e-value-3.html


Alan

[WasWodge]

Just for the hell of it another method (Btw, I don't really see the need for using Worksheets.Item(1) when you can just use Worksheets(1) or Sheets(1) )

Please Login or Register  to view this content.

[Doc.AElstein]

Hi WasWodge

Just for the hell of it another method ......

More the merrier

_..............

..... (Btw, I don't really see the need for using Worksheets.Item(1) when you can just use Worksheets(1) or Sheets(1) )

_...................No Big deal,
Mostly that is just a personal preference ....
_1a ) when answering or sharing codes here. It saves a bit of confusion using the Item Number rather than Worksheet names, especially as mine are in German ( “Tabelle1” instead of “Sheet1” ) etc.. although I guess you are also talking about using the item Number in this ( shorthand ) form Worksheets(1) or Sheets(1) , again referring to Item 1, the first tab from the left.

_1b) Sometimes people use the Code name Sheet1, which again confuses me with Sheets(1) or Sheets(“Sheet1”) or Worksheets(“Sheet1”). Again this chucks a Spanner in the works for me as I have
Tabelle1.....
http://www.excelforum.com/showthread...t=#post4399896

This version of your code would probably work for you:

Please Login or Register  to view this content.

It would not for me... I would need this:

Please Login or Register  to view this content.

_......

_2) I am a bit thick, so I always use things like Named:= arguments , or Worksheets.Item(1) rather than Worksheets(1) , so as to remind me what is going on, and helps cut down a bit on ‘commenting , which I do a lot, - again just a personal preference ( necessity for me to remember what is going on ! )

But
_3) But you can get caught out with Worksheets( ) sometimes when you use that as an alternative to referring to the sheet by Item Number _...
-..If you have a variable, var, that is a number, then depending how exactly you do things this_....

Worksheets(var)

_.....Will give you different results. - It may try to “get at” the Worksheet with item number of the number in var, or it may try to “get at” the Worksheet with a Name equal to a Number of the number in var.
So again just to be on the safe side I personally use the .Item( ) for item number and keep the
Worksheets( “ “)
For a name, even in the case of using a variable, and then a bit of belt and braces again_... I annoy people and do this
Worksheets( “” & var & “ “)
Again just to remind me I am giving ( Building ) a String in there
http://www.excelforum.com/excel-prog...ml#post4183474
http://www.excelforum.com/excel-prog...ml#post4183530


_..................................

But all no big deal, anyway that works , it’s all VBA anarchy, man ... But there are a lot of variations to consider ( confuse ... ) Lol...


Alan

[JBeaucaire]

Information overload.... I'm just saying...

[WasWodge]

_1b) Sometimes people use the Code name Sheet1, which again confuses me with Sheets(1) or Sheets(“Sheet1”) or Worksheets(“Sheet1”).

In real life I much prefer the Codename if referring to sheets in the same workbook as it is not affected by the renaming and moving of sheets.
I find Sheet1, Sheets(1), Worksheets(1) and Sheets("Sheets1") quite easily distinguishable and understanding the differences between them is important.

_3) But you can get caught out with Worksheets( ) sometimes when you use that as an alternative to referring to the sheet by Item Number _...
-..If you have a variable, var, that is a number, then depending how exactly you do things this_....

Worksheets(var)

_.....Will give you different results. - It may try to “get at” the Worksheet with item number of the number in var, or it may try to “get at” the Worksheet with a Name equal to a Number of the number in var.

If you have declared your variables correctly then...

If var = 1 (i.e. a number) then Sheets(var) will always refer to the sheets Index number i.e. Sheet(1).
If var = "1" (i.e. a string) then Sheets(var) will always refer to a sheet named "1" i.e. Sheet("1").
I don't see the confusion or get different results with the above (please post an example if you do as I will have to make a note of when this happens).

But then also I probably wouldn't call a variable "var" unless in was of variant type (like sometimes in an array).
IMHO a variable should be obvious as to what it is i.e. a range, a number(and normally what the number was referring to), a string etc. (i.e. myRng, Lrow (for long row), myStr etc...).

Btw. don't post here very often these days but I note the board still has a problem when using Go Advanced or Preview Post.

[WasWodge]

Information overload.... I'm just saying...

Couldn't edit my last post as the window kept coming up blank...

but probably also guilty... so sorry in advance Jerry

[Doc.AElstein]

Hi WasWodge

I think I agree ppretty well with all you say, - I can’t remember the exact example, but onceWorksheets( ) took a variable as an index number when I wanted a name , so a quick addition of a “” & cured it, so I just got in the habit of doing that. I think I was trying to fix a code for an OP and he had not dim’ed things too carefully !!!!!

In that link _.....
http://www.excelforum.com/excel-prog...ml#post4183530
_... I did a code example that shows how the thing can crop up. But basically the code confirms what you say, that if you make sure the thing is Dim ‘ed correctly then the problem does not come up. People do not always Dim properly, so then the problem can crop up.!!!!!

I do not think there is much difference in what we are saying. Just personal preferences.

_......................................

.....Btw. don't post here very often these days but I note the board still has a problem when using Go Advanced or Preview Post.

Yep, the fun distinguishing EF charactereristics are still here, we grow to love them, and just get used to workarounds.. check out some of the posts around the water Cooler and Suggestions for improvement Sub Forums.... Copying before Going Advance, or copying from a Reply with quote etc.. etc...
http://www.excelforum.com/suggestion...ml#post4377194
Alan

[WasWodge]

the fun distinguishing EF charactereristics are still here

Not how I would describe it but each to their own and I am sure the Tech Team are on the case

[xladept]

Why not resize all the way?

Please Login or Register  to view this content.

It's easy to write because you just have to copy-paste

[WasWodge]

Why not resize all the way?
....
It's easy to write because you just have to copy-paste

The code I posted is because I wouldn't normally know the resize without working it out (and if I had to work it out then I might as well work out the destination range and do a range.Value = range.Value which would be more efficient) but I would normally know the range to be "copied" or it might even be that nasty word Selection being used.

For instance I definitely wouldn't bother trying to work out the resize with something larger and not stating at A1 like

Please Login or Register  to view this content.

I would find it much easier to use .Rows.Count and .Columns.Count than working out the Resize in the above circumstance.

It also allows for more the copy range to be dynamic as in the circumstance below

Please Login or Register  to view this content.

I'll stop there before Jerry needs to do another...

Information overload.... I'm just saying...

[xladept]

Hi WasWodge,

I don't understand - if you copy Rows.Count,Columns.Count to anywhere but A1 won't that cause an overflow?

Instruct me

[WasWodge]

Not with the With statement and the . in front of Rows and Columns.
It gets the number of rows and columns in the "copy" range.

Perhaps the below makes it clearer what I meant

Please Login or Register  to view this content.

Edit or probably the below makes it clearer

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Sab_1]

Thanks all so much,

I used:

Please Login or Register  to view this content.

I hope now its ok.

[xladept]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Doc.AElstein]

Hi,
Just adding to the solution here and at tje same time clearing up some confusion ( of mine ) which may have meant I made some suggestions that are less relevant / useful..

Regarding the discussions around this:

.... (Btw, I don't really see the need for using Worksheets.Item(1) when you can just use Worksheets(1) or Sheets(1) )...

In real life I much prefer the Codename if referring to sheets in the same workbook as it is not affected by the renaming and moving of sheets.
I find Sheet1, Sheets(1), Worksheets(1) and Sheets("Sheets1") quite easily distinguishable and understanding the differences between them is important.

....

....
_...................No Big deal,
Mostly that is just a personal preference ....
_1a ) when answering or sharing codes here. It saves a bit of confusion using the Item Number rather than Worksheet names, especially as mine are in German ( “Tabelle1” instead of “Sheet1” ) etc.. although I guess you are also talking about using the item Number in this ( shorthand ) form Worksheets(1) or Sheets(1) , again referring to Item 1, the first tab from the left.

_1b) Sometimes people use the Code name Sheet1, which again confuses me with Sheets(1) or Sheets(“Sheet1”) or Worksheets(“Sheet1”). Again this chucks a Spanner in the works for me as I have
Tabelle1.....
http://www.excelforum.com/showthread...t=#post4399896

This version of your code would probably work for you:

Please Login or Register  to view this content.

It would not for me... I would need this:

Please Login or Register  to view this content.

_......

_2) I am a bit thick, so I always use things like Named:= arguments , or Worksheets.Item(1) rather than Worksheets(1) , so as to remind me what is going on, and helps cut down a bit on ‘commenting , which I do a lot, - again just a personal preference ( necessity for me to remember what is going on ! )

But
_3) But you can get caught out with Worksheets( ) sometimes when you use that as an alternative to referring to the sheet by Item Number _...
-..If you have a variable, var, that is a number, then depending how exactly you do things this_....

Worksheets(var)

_.....Will give you different results. - It may try to “get at” the Worksheet with item number of the number in var, or it may try to “get at” the Worksheet with a Name equal to a Number of the number in var.
So again just to be on the safe side I personally use the .Item( ) for item number and keep the
Worksheets( “ “)
For a name, even in the case of using a variable, and then a bit of belt and braces again_... I annoy people and do this
Worksheets( “” & var & “ “)
Again just to remind me I am giving ( Building ) a String in there
http://www.excelforum.com/excel-prog...ml#post4183474
http://www.excelforum.com/excel-prog...ml#post4183530
....

-.... with regard to all that, one of my reasoning’s was that _...
_1 )
Worksheets.Item(1)
_.... would make sure you are referencing by the item number, whereas I was arguing that _....

_ 2) Worksheets(1)
_..... ... Might refer to the item number of 1 or the Worksheet Name of “1” when doing this
Worksheets.Item(var)
-.......................depending in how var was Declared, ( Dim ed )

That last argument _2) is valid.... BUT::: ... I did not notice / realise that these versions of _1) are also valid

Worksheets.Item(var)
Worksheets.Item(“1”)

_... so this point iImade “ ... Worksheets.Item(1).... would make sure you are referencing by the item number....” was not quite right. Oops


_...........................

Just clearing that last point up, to avoid any confusion to anyone referencing this thread in the future ( Like what I just did )

Alan

[WasWodge]

All 3 of the methods have their place and appropriate time to use them.
I don't really see the confusion with 1 and "1" as one is a number and one a string exactly the same as in a spreadsheet and the variable can be easily distinguishable by using a good naming convention like strVar.
Having said that I would never name a sheet as just a number in the first place but then each to their own.
If when coding for myself or work and when appropriate I prefer to use the sheets CodeName as others are less likely to come up and say that their workbook isn't working.

[Doc.AElstein]

Hi WasWodge,
I think I more or less agree with you, as I did before, ( although my code names ( German ) will be different as I had mentioned )
I was just correcting my little misunderstandig, ...as I wrongly was under the impresion that the
.Item(Thisargument)
only took a number, that is to say Thisargument was a number. I realise now that was incorrect - Like if I do
.Item("SheetName")
then VBA will somehow as a result of seeing a string ( as it recognises typically from a " " pair ) , look for the Worksheet with the Name SheetName
Only when it sees a number does it attempt to look for the Worksheet with a Long type Number of that Item Number. - For example when it sees this
.Item(1.9)
it will coerce that 1.9 to 2 and look for the second tab counting fron the left Worksheet


Alan