Solved

EXcel VBA

Posted on 2012-03-13
7
345 Views
Last Modified: 2012-03-13
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
Comment
Question by:Markwedd
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 37716427
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
 

Author Comment

by:Markwedd
ID: 37716666
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 37716831
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Markwedd
ID: 37717176
ROw 1-8 is the header. Please see attached workbook.
TTLC-Master.xls
0
 
LVL 10

Accepted Solution

by:
SANTABABY earned 500 total points
ID: 37717579
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
 

Author Closing Comment

by:Markwedd
ID: 37717639
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 37717891
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now