• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

EXcel VBA

I have an Excel workbook with two sheets (Master & Subset). I need to copy rows from the Master sheet to the Subset sheet which have a date in a particular field (Column O). The data starts in row 9 and extends to row 1980.

I have tried several methods and have not met with success. Finally I implemented the code below with the intent to copy all the rows from Master to Subset and then filter in Subset for the column above. The effect would be that only the rows with dates in column O would be displayed in the Subset sheet.

My Problem is the filter is excluding the first row in Subset. So i am getting an extra row in my result which most times should have been filtered out.


Sub CopyRange()

Sheets("Master").Select
    Range("A9:P1980").Select
    Selection.Copy

    Sheets("Subset").Select
    Range("A9").Select
    ActiveSheet.Paste
    Sheets("subset").Range("A9:P1980").AutoFilter Field:=15, Criteria1:="<>"

End Sub

How can I make this work? Is there another way to achieve my aim?
0
Markwedd
Asked:
Markwedd
  • 4
  • 3
1 Solution
 
SANTABABYCommented:
Not sure why you don't want apply the filter on the entire column(in subset sheet)? any partucular reason?
Would you prefer the code to copy only the qualified rows to subset sheet?

Is it possible to atach a sample, hopefully that's answer my questions.

Thanx,
SB
0
 
MarkweddAuthor Commented:
I only applied the filter on the partial column to avoid getting portions of the header in the mix.

I am open to any solution which allows me to achieve my aim.

I could only attach a workbook without data.
0
 
SANTABABYCommented:
Do you need row 1-8 in the subset?
I realize that you have sensitive data in your spreadsheet. Could you please attach a workbook with made up data(just enough to describe the problem)?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
MarkweddAuthor Commented:
ROw 1-8 is the header. Please see attached workbook.
TTLC-Master.xls
0
 
SANTABABYCommented:
Please try the following CopyRange routine(replace you current code), which will keep your header rows (1-8) intact and filter the subsequent rows. Please let me know if this not what you wanted.

Sub CopyRange()

Sheets("Master").Select
    Range("A9:P1980").Select
    Selection.Copy

    Sheets("Subset").Select
    Sheets("Subset").AutoFilterMode = False
    Range("A9").Select
    ActiveSheet.Paste
    'Sheets("subset").Range("A1:P1980").AutoFilter Field:=15, Criteria1:="<>"
    Sheets("subset").Range("A8:P1980").AutoFilter Field:=15, Criteria1:="<>"

End Sub
0
 
MarkweddAuthor Commented:
Thank you SANTABABY! That worked.

Could you tell me what I would have to do to delete the fields with blank instead of just removing them from view.
0
 
SANTABABYCommented:
Try the following please:

Sub CopyRange()

Sheets("Master").Select
    Range("A9:P1980").Select
    Selection.Copy

    Sheets("Subset").Select
    Sheets("Subset").AutoFilterMode = False
    Range("A10").Select
    ActiveSheet.Paste
    'Sheets("subset").Range("A1:P1980").AutoFilter Field:=15, Criteria1:="<>"
    Range("A9").Clear
    Sheets("subset").Range("A9:P1980").Select
    Selection.AutoFilter Field:=15, Criteria1:="="
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Selection.EntireRow.Delete
    Sheets("Subset").AutoFilterMode = False


End Sub
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now