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

Mariner

New Member
Joined
Dec 12, 2005
Messages
24
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,
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

starl

Administrator
Joined
Aug 16, 2002
Messages
6,001
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 12, 2005
Messages
24
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

Administrator
Joined
Aug 16, 2002
Messages
6,001
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Administrator
Joined
Aug 16, 2002
Messages
6,001
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

nevermind - got it
 

starl

Administrator
Joined
Aug 16, 2002
Messages
6,001
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
Code:
    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

New Member
Joined
Dec 12, 2005
Messages
24

ADVERTISEMENT

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

Administrator
Joined
Aug 16, 2002
Messages
6,001
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,633
Messages
5,832,780
Members
430,167
Latest member
Gogogoben

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top