vba Questions

[tico_ocit]

Code is generally slowed considerably by 'activating' and 'selecting'. Most time that is not necessary.

I don't know whether you actually need to activate ALL.xls to start with as I don't know what is in FTALLV however these lines of code

Range("A11:BD11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Can be replaced by these (not exactly sure of the name of that second workbook - spelling or extension - so check), avoiding any activation or selection or copy.

Dim rws As Long

With Workbooks("ALL.xls").Sheets("FT")
  rws = .Range("A11:BD11").End(xlDown).Row - 10
  Workbooks("COMPARSION.xls").Sheets("FT COMPARE").Range("B23").Resize(rws, 56).Value = .Range("A11:BD11").Resize(rws).Value
End With

Sorry for entering the conversation, but we are always learning. @Peter_SSs can you answer me two questions? 1. I always use .activate instead of .select, because .select doesn't work, do you know why? 2. I didn't know this propoerty of range, "resize", it's great! But, the .value=.value only works if you are copying just the values, right?? Because I always can use range(A1:A3").copy Sheets("sheet2").range("B1:B3"). Is it faster? Or the same?
Thank you!

 

[Peter_SSs]

Firstly, I have moved your question to its own thread rather than hijacking another active one.

1. I always use .activate instead of .select, because .select doesn't work, do you know why?

It depends on what you are trying to activate/select. In most case you do not need to do either.

But, the .value=.value only works if you are copying just the values, right??

Correct

Because I always can use range(A1:A3").copy Sheets("sheet2").range("B1:B3"). Is it faster? Or the same?

Faster or the same as what?

If you mean compared to
Sheets("sheet2").Range("B1:B3").Value = Range(A1:A3").Value
then the question is not one of speed because the two codes do different things. The second copies values only
The first copies values or formulas depending what is in the source range. It also copies things like Formatting, Conditional Formatting, Data Validation, Comments, ... etc

 

[tico_ocit]

Faster or the same as what?

If you mean compared to
Sheets("sheet2").Range("B1:B3").Value = Range(A1:A3").Value
then the question is not one of speed because the two codes do different things. The second copies values only
The first copies values or formulas depending what is in the source range. It also copies things like Formatting, Conditional Formatting, Data Validation, Comments, ... etc

I understand, I started using .value=.value due to speeding. On the example that I gave: range(A1:A3").copy Sheets("sheet2").range("B1:B3") , it is possible, in the same line of code, choosing what to copy? Format, values, formulas?

Thank you for moving, and thank you for answering.

 

[Peter_SSs]

On the example that I gave: range(A1:A3").copy Sheets("sheet2").range("B1:B3") , it is possible, in the same line of code, choosing what to copy? Format, values, formulas?

No. for that you need to Copy then on a separate code line use PasteSpecial to choose the elements to paste.

 

[tico_ocit]

No. for that you need to Copy then on a separate code line use PasteSpecial to choose the elements to paste.

That's what I thought. Thank you!

 

[Peter_SSs]

Cheers