VBA Copy all rows have data in column A (non – continuos) in to new workbook

I have a workbook “DT” . In this workbook has one sheet named “KHOI LUONG” with data as following :

col A col B col C col D col E etc….
AK.2134 WORK 1 230 34
work 2 12 3
AI.4567 work 3 45 8
work 4 24 3
……more …..


I want to copy rows on col A has data and paste into another new workbook as folowing


AK.2134 WORK 1 230 34
AI.4567 work 3 45 8


anyone help me a macro to do this work ? very thanks
my table has about 2000 rows

Please help me ! – Binh Tay!

Top Contributor Asked on June 11, 2016 in VBA: Macros.
Add Comment
3 Answer(s)
Best answer

Hi Binh Tay,

Welcome to anlysistabs.
As I understand, you want to copy the rows where there is period (.) in the column A (into new workbook)
Here is the macro:

Sub Filter_the_dataAndCopyPaste()
Sheets("KHOI LUONG").Range("A:A").AutoFilter Field:=1, Criteria1:="=*.*"
Sheets("KHOI LUONG").Range("A1", ActiveCell.SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible).Copy
Set wb = Workbooks.Add
Application.CutCopyMode = False
End Sub


Keymaster Answered on June 12, 2016.
Add Comment

I am very thanks PNRao . It has worked fine after I modify sheet name to “BKL” and column B:B
Thanks – Binh Tay!

Expert Answered on June 12, 2016.
Add Comment

Glad you found this useful, Thanks-PNRao!

Keymaster Answered on June 12, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.