VBA Copy Then Paste Speed ?

[Pinaceous]

Hi All,

I'm using Excel 2013 and I would like to copy a large area Table area on Sheet1 being, Table5[[Column2]:[Column20]] of area $B$10:$T$211, to Sheet 5!$C$2:$V$201 special paste 'text'.

I can of course 'record a macro' which is fine, but is there anyway to speed up this process more efficiently by use of a written code verse a scripted code? Right now it takes about 30 seconds to long and would like to reduce this time factor as much as possible.

Can you be of assistance?

-Pinaceous

 

[mole999]

haved a peek at this Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros

 

[rockon0921]

Hopefully this helps its quick

Sub CopyValue()


Worksheets(1).Range("b10:t211").Copy
Worksheets(5).Range("c2").PasteSpecial xlPasteValues


End Sub

 

[GTO]

Pinaceous,

I think you are off in row count, but for a generic example that assumes default CodeName(s):

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> example()<br>  Sheet3.Range("D2").Resize(Range("B10:T211").Rows.Count, Range("B10:T211").Columns.Count).Value = _<br>    Sheet2.Range("B10:T211").Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark

 

[Pinaceous]

Hi Mark,

Let me double check my range values and I'll give it a go!

Many thanks,

Pinaceous

 

[Pinaceous]

Thanks 'rockon0921'!

 

[Pinaceous]

Hi Mark,

If I wanted to do the reverse, where I'm copying text in sheet A to paste in sheet B (non-special) another words text to text paste; how would that be written?

Thanks again,

-P

 

[GTO]

Hi Mark,

If I wanted to do the reverse, where I'm copying text in sheet A to paste in sheet B (non-special) another words text to text paste; how would that be written?

Thanks again,

-P

I am afraid I am not understanding. Especially the verbiage in red and blue.

Mark

 

[Pinaceous]

Hi Mark,

Okay, I'll try and explain it below:

What I am trying to explain is in using the "Paste Options" in originally asking, basically, was to Copy a Table, its contents, and next to paste it on anther sheet in only transferring the "Values Only" and not the Table itself. So, now I am trying to see if I can, by use of a code, is to copy Values and Numbers, not of a table, to another sheet in pasting just the Values and Numbers, or can your code also do this as well??

Let me know if I am any clearer.

Thanks.

R/
Pin

 

[GTO]

...So, now I am trying to see if I can, by use of a code, is to copy Values and Numbers, not of a table, to another sheet in pasting just the Values and Numbers, or can your code also do this as well??

Let me know if I am any clearer.

Not 'tons' clearer to me at least, but so far, I believe the answer is yes. That is, in general, when you assign [Range 1 Value] to another Range, Excel will coerce strings to strings, Dates to Dates, and numeric values to numeric values. When you try this, are you getting odd results?

Mark