Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Extract (2003)

    I am trying to extract data but I am getting error messages before all the data is extracted.

    I have 2 blocks of code to extract data on different criteria, one to extract data according to SITE and one according to VEH. Both stop with errors but I do not understand why.

    I would appreciate it if somebody can point out my error.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Extract (2003)

    One of the values is "B/P". A sheet name cannot contain a slash /. Try changing

    wsNew.Name = c.Value

    to

    wsNew.Name = Replace(c.Value, "/", "")

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Extract (2003)

    Hans

    Thank you for the quick reply, I appreciate it.
    The first block now works fine, thank you.
    Any other caracters in general that will course problems with this type of code? I will instruct the data inputters not to use them.

    On the second extract block of code " Sub ExtractVeh()" I still get an error with the following line highlighted in yellow:

    Sheets("Heading").Copy After:=Worksheets(Worksheets.Count)

    Any reason for this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Extract (2003)

    The following characters are not allowed in a sheet name:<code> / : ? * [ ]</code>

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Extract (2003)

    Hans

    First I thought that there may be data recording errors, so I deleted chunks of data including the numbers where the code stop running and I notice that the error always occur after 54 or 55 new sheets has been inserted and the relavant data extracted.

    Is there any reason why this happen? I would appreciate your insight.

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Extract (2003)

    This is apparently a known problem - see Copying worksheet programmatically causes run-time error 1004 in Excel.

    The workaround is to create an Excel template (.xlt) containing the Heading sheet, then remove Heading from your workbook, and change the lines

    Sheets("Heading").Copy After:=Sheets(Sheets.Count)
    Set wsNew = Sheets(Sheets.Count)

    to

    Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count), Type:="Heading.xlt")

    If you store Heading.xlt in the same folder as your workbook, you don't have to specify the path.

    It works for me with this change.

  7. #7
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Extract (2003)

    Hans

    Thank you very much for the feedback, I followed your instructions and got the code working as you suggested.

    I want to enhance the code a little but do not know how to add the proper code to get the desired result. I attached a small extraction from the file to illustrate.
    What I want to do is to add code that will do the calculation as indicated in the yellow portion of sheet TP153. Note that the calculation can only start in the second line of extracted data since the first line obviously has no previous data to calculate from and must then calculate for each extracted row of data.

    Is this easy to accomplish? If so I would appreciate your help again.

    Regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Extract (2003)

    You can add the following code. Insert it above the line Next c:
    <code>
    If n > 16 Then
    wsNew.Range("I16:I" & (n - 1)).FormulaR1C1 = "=RC4-R[-1]C4"
    wsNew.Range("J16:J" & (n - 1)).FormulaR1C1 = "=RC9/RC7"
    End If
    </code>
    I used FormulaR1C1 because it's relatively easy to create RC-style formulas that refer to the same row or to the previous row etc.

  9. #9
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Extract (2003)

    Hans

    Thank you, I got it working now.
    I would really like to ask a few more questions if you do not mind.

    How can I format the answer given by :
    wsNew.Range("J16:J" & (n - 1)).FormulaR1C1 = "=RC9/RC7"
    to display only 2 decimal places (not rounded)

    The piece of code that extract list of vehicles:
    ws1.Columns("c:c").AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Range("L1"), Unique:=True

    What code can be added to this to sort it alphabelically?

    I would appreciate it if you could advice me on this.

    Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Extract (2003)

    1) You can add a line to format the cells:

    wsNew.Range("J16:J" & (n - 1)).NumberFormat = "0.00"

    2) After the line

    r = ws1.Cells(ws1.Rows.Count, "L").End(xlUp).Row

    insert the row

    ws1.Range("L2:L" & r).Sort Key1:=ws1.Range("L2")

  11. #11
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Extract (2003)

    Hans

    Thank you very much, that help a lot since the number of new sheets created is close to 200 and it make it much easier to locate a specific sheet.

    Going through the extracted sheets I noticed something that I do not understand. There are cases there the Vehicle number is not the same for instance HT52 and HT52D, yet when the info is extracted it extract the records of HT52D into HT52 and then extract it again into HT52D.
    How can this be prevented? There are quite a few instances where it happens.

    I attach a file to show the problem. Wrongly extracted info is highlighted in yellow.

    Your opinion will be appreciated.


    Regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Extract (2003)

    Advanced filter by default interprets criteria as "beginning with". So "HT52" is interpreted as "beginning with HT52". To select only HT52 itself, change the criteria to

    ="=HT52"

    To do this in the macro, change the line

    ws1.Range("M2").Value = c.Value

    to

    ws1.Range("M2").Formula = "=" & Chr(34) & "=" & c.Value & Chr(34)

    Chr(34) is the double quote character.

  13. #13
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Extract (2003)

    Hans

    Thank you very much. Sorry for the late respons.

    I was out of the office for some time and could not respond earlier.

    Everything works perfect so far, all thanks to you.

    How many sheets can be created in one workbook? Provided off course you do not run out of memory. I originally thought it was 256 untill I saw a bigger number extracted.

    If my master database runs over many months, is there a way to specify to extract only records between and including two dates?

    Regards

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Extract (2003)

    1) The number of worksheets in a workbook is limited only by the available memory, there is no 'hard' limit.

    2) Enter the start and end date in two cells, say R1 and R2.
    Add columns to the criteria area: in N1 and O1, enter the word Date.
    In N2, enter the formula =">="&R1
    In O2, enter the formula ="<="&R2
    Change the code to use M2:O2 as criteria range for AdvancedFilter.

    rng.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Diesel").Range("M1:O2"), _
    CopyToRange:=wsNew.Range("A14:H14"), _
    Unique:=False

    At the end of the code, clear columns M and N instead of deleting them:

    ws1.Columns("L:M").Clear

  15. #15
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Extract (2003)

    Hans

    Thank you, that works brilliantly.

    Is there a way that the first filter on column c;c can only extract the plant items that fill between the same criteria dates. What happens now is that the macro create a sheet for every item in the list regardless if there are any data for the item?

    Regards

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •