How can I read from different Excel sheets and write to another sheet with different format.

How can I read from different Excel sheets and write to another sheet with different format.

Please find the Excel sheet attached. I need to read from the two sheets (HWYH and UTMN) and produce the sheet (All).

Regards,

Dallag
GRMD.xlsx
Mohammed DallagPetroleum ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Run the CombineOnAll macro in this workbook.
Q-28241650.xlsm
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
I am geeting the attached error.

Regards,

Dallag
9-17-2013-4-56-47-PM.jpg
0
Martin LissOlder than dirtCommented:
I see that "Exit For" is highlighted. It would be very unusual if that line were causing an error so please double check that that's the right line. But in any case what is the text of the error?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mohammed DallagPetroleum ConsultantAuthor Commented:
I am not getting any error. It is just freezing and when I went back and checked the "All" sheet, I found only have of the data copied (the one from the "HWYH" sheet).
0
Martin LissOlder than dirtCommented:
It ran OK for me but I can do it a different way. Will there always be just 4 wells on the sheets?
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
no those only samples and in each sheets the wells are growing with time. We need to make the "New Well" as the a flag to stop reading the wells from each sheet.
0
Martin LissOlder than dirtCommented:
That's what the current code does.

Did you make any changes to the data in my workbook before you ran the macro?
0
Martin LissOlder than dirtCommented:
Try this modification.


Sub CombineOnAll()

Dim lngLastRowAll As Long
Dim lngRow As Long
Dim lngAllRow As Long
Dim lngCol As Long
Dim lngLastCol As Long
Dim wsHWYH As Worksheet
Dim wsUTMN As Worksheet

Set wsHWYH = Sheets("HWYH")
Set wsUTMN = Sheets("UTMN")

lngAllRow = 2
With Sheets("All")
    lngLastRowAll = .UsedRange.Rows.Count
    .Range("A2:E" & lngLastRowAll).ClearContents
    lngLastCol = wsHWYH.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    For lngCol = 2 To lngLastCol Step 3
        If wsHWYH.Cells(1, lngCol).Value = "New Well" Or _
           wsHWYH.Cells(1, lngCol).Value = "" Then
            Exit For
        End If
        For lngRow = 4 To wsHWYH.UsedRange.Rows.Count
            .Cells(lngAllRow, 1).Value = wsHWYH.Cells(1, lngCol).Value
            .Cells(lngAllRow, 2).Value = wsHWYH.Cells(lngRow, 1).Value
            .Cells(lngAllRow, 3).Value = wsHWYH.Cells(lngRow, lngCol).Value
            .Cells(lngAllRow, 4).Value = wsHWYH.Cells(lngRow, lngCol + 1).Value
            .Cells(lngAllRow, 5).Value = wsHWYH.Cells(lngRow, lngCol + 2).Value
            lngAllRow = lngAllRow + 1
        Next
    Next
    lngLastCol = wsUTMN.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    For lngCol = 2 To lngLastCol Step 3
        If wsUTMN.Cells(1, lngCol).Value = "New Well" Or _
           wsUTMN.Cells(1, lngCol).Value = "" Then
            Exit Sub
        End If
        For lngRow = 4 To wsUTMN.UsedRange.Rows.Count
            .Cells(lngAllRow, 1).Value = wsUTMN.Cells(1, lngCol).Value
            .Cells(lngAllRow, 2).Value = wsUTMN.Cells(lngRow, 1).Value
            .Cells(lngAllRow, 3).Value = wsUTMN.Cells(lngRow, lngCol).Value
            .Cells(lngAllRow, 4).Value = wsUTMN.Cells(lngRow, lngCol + 1).Value
            .Cells(lngAllRow, 5).Value = wsUTMN.Cells(lngRow, lngCol + 2).Value
            lngAllRow = lngAllRow + 1
        Next
    Next
End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Will there be more sheets?
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
yes and I just run the Macro and it was OK. I will just copy and paste the sheet part in the code.

Thank you for your help
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
MartinLiss,

Thank you so much for your quick and excellent help

Regards,

Dallag
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.