0

I need to copy a column (say A) from sheet ABC to another column say B in sheet DEF.

I have been using this code:

Sheets("ABC").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("DEF").Activate
ActiveSheet.range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False'

This works fine, but it does not copy any further values if a blank value comes in between. Could you please help me out with this?

I need the entire column A in sheet ABC to be there in column B in DEF

Thanks a lot for your help!

Community
  • 1
  • 1
Sona123
  • 91
  • 4
  • 13

1 Answers1

1

The following code creates a variable containing a reference to the source range, then sets the destination range's Value to the source range's Value:

Dim SrcRng As Range
With Worksheets("ABC")
    Set SrcRng = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With
Worksheets("DEF").Range("B1").Resize(SrcRng.Rows.Count, 1).Value = SrcRng.Value

It also avoids using Select and Activate. (See How to avoid using Select in Excel VBA macros.)

YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Thanks!Worked perfectly fine! – Sona123 Jul 25 '17 at 07:49
  • Wouldn't it be easier using `.EntireColumn`? I would say there is no significant difference in speed (I must say I didn't test it) but a significant easier to read code. But I let me proof wrong. – Pᴇʜ Jul 25 '17 at 07:51
  • @Peh I was worried that a `Column.Value = Column.Value` might be slow. I'll give it a test and see what happens. – YowE3K Jul 25 '17 at 09:03
  • 1
    @Peh - OK, just tested, I'll stick with my current answer. I set up 1000 cells in column A, and copied those 1000 cells' values to column B - it took 0.008 seconds. I then copied column A to column C - that took 0.758 seconds - so about 100 times slower. Also, copying the entire column made Excel think the last used cell was in row 1048576, which would probably fix itself once the file was saved and re-opened, but could be a short-term issue. – YowE3K Jul 25 '17 at 09:08