# Thread: multiple sheets extract (2003)

1. ## 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

2. ## 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. ## 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

4. ## 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. ## 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,

#### Posting Permissions

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