Copy and Paste using assign value method

[gd6noob]

Just a quick question for a quicker method for copy and pasting.

Normally I use something like

Range(Range("A5"), Range("A5").End(xlDown)).copy

to copy and pasting to other sheets.. but I find it slow something. The amount of Data varies but usually 30,000-50,000. I was reading online for a faster way and found assigning values would be the fastest way to do this but its usually a set range but my data is different everyday.

How would I go about doing this? Also is it possible to assign the value to more then 1 sheet in one go?

What would be the best method be?


Thanks in advanced.

 

[vcoolio]

Hello gd6,

You could use Auto Filter or perhaps even Advanced Filter but it may depend on what exactly you are intending to do.
Are you using a criteria in Column A on which you base the copy/paste or are you copy/pasting the entire dataset?

Perhaps you could elaborate further for us and supply a sample for us to see what you would like to do.

Cheerio,
vcoolio.

 

[gd6noob]

Hello gd6,

You could use Auto Filter or perhaps even Advanced Filter but it may depend on what exactly you are intending to do.
Are you using a criteria in Column A on which you base the copy/paste or are you copy/pasting the entire dataset?

Perhaps you could elaborate further for us and supply a sample for us to see what you would like to do.

Cheerio,
vcoolio.

Hi vcoolio,

Thank you for your reply.
No need for filters, its pretty straight forward copy/paste. Sometimes I need to paste some columns into multiple sheets...

Here is a small sample, for this particular dataset, there are 32,000 rows and I will need columns A and B on 1 sheet and just column A on another sheet.

Book1
	A
1	Employee Id
2	108580270
3	104650662
4	105293101
5	106347095
6	108912308
7	108949639
8	100715221
9	102838831
10	103139081
Sheet1

Regards,

 

[My Aswer Is This]

You said:
to copy and pasting to other sheets.. but I find it slow something.
So we are trying to show you another quicker way using Filter.

And from what your showing just one column of data I have no ideal what your wanting.
Can you please provide more details.
You only showed one line of code:
Range(Range("A5"), Range("A5").End(xlDown)).copy
Copy and paste where?
And what would second line of code be if you wrote it the same way

 

[Fluff]

Maybe something like

Sub gd6noob()
   Dim Ary As Variant
   
   With Sheets("sheet1")
      Ary = .Range("A5", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   Sheets("Sheet2").Range("A5").Resize(UBound(Ary)).Value = Ary
End Sub

 

[gd6noob]

Maybe something like

Sub gd6noob()
   Dim Ary As Variant
  
   With Sheets("sheet1")
      Ary = .Range("A5", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   Sheets("Sheet2").Range("A5").Resize(UBound(Ary)).Value = Ary
End Sub

Awesome, this worked... Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.