Example in VBA and Macros book - p.307 Filter and Copy Data

[Mariner]

Hi,

Just bought the book on Friday. I'm trying to streamline the process of cutting and pasting. It seems that a macro with a do while loop would do the trick; first, however, I need to be able to filter and copy data into a different worksheet within the same spreadsheet.

An example on page 307 of the book looks like a homerun, but I keep receiving databug errors on either of the following two fields:

rnStart.AutoFilter Field:=1, Criteria1:="AA" & i

rnData.SpecialCells(xlCellTypeVisible).Copy

For my Macro, my "Article" heading is "Asset Class." Assets are either 1PV1PD, 1PV2NP, 1PV4UD, and 1PV5OT. To simplify, I changed their names to be 1PV1, 1PV2, 1PV3, and 1PV4.

So like the example in the book, I had a similar filter criterion:

rnStart.AutoFilter Field:=1, Criteria1:="1PV" & i

Still, there is a bug.

All I'm trying to do is pull oil and gas data from a field run and separate it by class (e.g., Proved Developed Producing, Proved Developed Non-Producing, Proved Undeveloped) into a different spreadsheet in the same file.

Originally, I thought a simple VLOOKUP table would do the trick, however, the date column has to be in ascending order throughout the data range. For the data I have, it's ascending up until the class changes, i.e., for PDP data, I have 1/31/06 numbers thru 7/31/08. The next row begins with the PDNP data and starts over at 1/31/06.

Thanks in advance for any help that can be provided.

Best,

 

[starl]

Mariner, have you looked at the downloadable sample files?

Without seeing your workbook and the entire section of code pertaining to this, or the error message that you are getting, I can't say what the problem is. The code from the book is fine - so there must be a setup on your sheet that's different, or a typo in the code.. you need to provide more information.

 

[Mariner]

Yes, I've been tinkering with the example on page 307 (located in the ProjectFilesChapter 13 file under the FilterCopy tab) to fit what I need.

Can I attach the file in this thread or should I email the file as an attachment to you?

The following Macro is what I used:

Sub Filter_NewSheet()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range, rnData As Range
Dim i As Long

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")

With wsSheet
'Make sure that the first row contains headings.
Set rnStart = .Range("B2")
Set rnData = .Range(.Range("B2"), .Range("G65536").End(xlUp))
End With

Application.ScreenUpdating = True

For i = 1 To 5
'Here we filter the data with the first criterion.
rnStart.AutoFilter Field:=1, Criteria1:="1PV" & i
'Copy the filtered list
rnData.SpecialCells(xlCellTypeVisible).Copy
Worksheets.Add Before:=wsSheet
'Name the added new worksheets.
ActiveSheet.Name = "1PV" & i
'Paste the filtered list.
Range("B2").PasteSpecial xlPasteValues
Next i

'Reset the list to its original status.
rnStart.AutoFilter Field:=1
With Application
'Reset the clipboard.
.CutCopyMode = False
.ScreenUpdating = False
End With

End Sub

Thanks in advance,

 

[starl]

code looks fine - it must be the workbook.. you can send it to me at the tracy address on the last page of the book - before the index

 

[starl]

nevermind - got it

 

[starl]

1. You don't have any data on your "Sheet1" - rename it to "Example Data"
2. Since you are basing your filter on the 2nd column of your data, your Field:=2
3. There is no error handling in the sample - so if you are missing a number (you only have 1,2,5), the program will bomb. I suggest putting a check in there.. like this:

    If wsSheet.Range("G65536").End(xlUp).Row <> 1 Then
        rnData.SpecialCells(xlCellTypeVisible).Copy
        Worksheets.Add Before:=wsSheet
        'Name the added new worksheets.
        ActiveSheet.Name = "1PV" & i
        'Paste the filtered list.
        Range("B2").PasteSpecial xlPasteValues
    End If

 

[Mariner]

Tracy,

Thank you so much for your help. It’s nice to actually see the power of a macro executed to the point where it can actually help me out in my work duties.

It seems that adding worksheets isn’t really what I should be doing; all I need to do is copy the data from the Example Data tab into the respective field tabs, e.g., Rice 316 and Sam 22. Is this a quick fix in the code, or is there a good example in your book that mimics this process?

Also, since the data that comes in for a class varies in date length across different properties, i.e., proved developed producing figures go through July 31, 2008 for Rice 316, however, they only go through March 31, 2007 for Sam 22, should I build in a do while loop to take care of this inconsistency?

And finally, I made the category classes consistent with the example in your book so that it would be “easier” to practice. Instead of 1PV1, 1PV2, 1PV4 and 1PV5 the class categories are actually 1PV1PD, 1PV2NP, 1PV4UD, and 1PV5OT. Since each still begins with “1PV”, will this be a relatively minor fix or will I have to incorporate the retrieving text function(s)?

Thanks again for all your help.

Regards,

 

[starl]

Mariner, everything you've mentioned is fairly involved, especially since I'm very busy right now. What you could do is post in the main Excel Questions forum. I'd post a piece of your data (refer to Colo's HTML Mker below for that ability) and the current macro you're using. Then explain how you need it tweaked. Hopefully, one of the experts in the forum will have time to help you.

 

[Mariner]

Thanks a bunch. I'll do it.

Best,