Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    multiple sheets extract (2003)

    Hi all,

    I have a workbook with number of sheets, each sheet has a name which represents a field name, I want to extract in a separate sheet in the book all the fields wells and their corresponding number entered in the special completion equipment columns m:z. The starting line is always one line after the column heading (Well No.) which is always in column E. Something similar to what is in sheet all.

    tia
    Attached Files Attached Files
    TIA
    dubdub

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

    Re: multiple sheets extract (2003)

    Try this:

    Sub FillAll()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim rngW As Range
    Dim r1 As Long
    Dim r2 As Long
    Dim t As Long
    Set wshT = Worksheets("all")
    wshT.Range("3:65536").Clear
    t = 3
    For Each wshS In Worksheets
    If Not wshS.Name = "all" Then
    Set rngW = wshS.Cells.Find _
    (What:="Well No.", LookIn:=xlValues, _
    LookAt:=xlPart)
    If Not rngW Is Nothing Then
    r1 = rngW.Row + 2
    r2 = wshS.Range("A65536").End(xlUp).Row
    wshS.Range("B" & r1 & ":B" & r2).Copy _
    Destination:=wshT.Range("A" & t)
    wshS.Range("E" & r1 & ":E" & r2).Copy _
    Destination:=wshT.Range("B" & t)
    wshS.Range("M" & r1 & ":Z" & r2).Copy _
    Destination:=wshT.Range("C" & t)
    t = t + r2 - r1 + 1
    End If
    End If
    Next wshS
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: multiple sheets extract (2003)

    Many thanks HansV, but I have to do a lot of editing to clean many un-wanted lines, so, I need your help to limit the extract for each field based on four headings in column a, one i have already included
    TIA
    dubdub

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

    Re: multiple sheets extract (2003)

    If you had provided all information in your first post, I could have taken it into account in my previous reply. I will leave now leave it to you as an excercise, you have the basic framework.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: multiple sheets extract (2003)

    Hi Hans,
    This is not an excuse, and you have all the reasons for your reply, but the workbook has many sheets, and it happened that I have opened only the clean ones.
    Regards,
    TIA
    dubdub

Posting Permissions

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