CODEWITHSUNDEEP
vbaexcelcopy
Sona123
Sona123

Reputation: 91

VBA macro copy column from one sheet to another with blanks in between

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!

Upvotes: 0

Views: 3464

Answers (1)

YowE3K
YowE3K

Reputation: 23994

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.)

Upvotes: 1

Related Questions