Looping Through Multiple Sheets

[Apollo6]

I have a workbook(Individual_Doctors) that has several sheets(each named by doctor's last name) and a seperate workbook(Data) with one sheet containing the doctor's full name and total charges. The sheet with charge information would have the doctors name in column A and the charges in column B, down the sheet and separated by a blank row:

Column A Column B
1 Jones, John 50000
2
3 Smith, Fred 10000
4
5 Miller, Tom 12000

I want to create a loop that opens the Individual_Doctors workbook and loops through the sheets one by one, for example selects the sheet "Jones", then opens the Data workbook and looks through the column A for the selected sheet's name Like ("Jones&quot. Then grabs the data from column B(50000) and paste it back into the selected sheet(Jones) back in the Individual_Doctors workbook.

It seems like it should be possible but I'm having trouble getting the loop to work. Any suggestions would be appreciated.

 

[Guest_imported]

You should try something along the lines of

Application.Workbooks.Open FileName:="Book1.XLS"
Application.Workbooks.Open FileName:="Book2.XLS"
Application.Workbooks(1).Activate
Dim oSheet As Worksheet
For Each oSheet In Application.Workbooks(1).Worksheets
Application.Workbooks(2).Activate
Range("A1&quot.Select
Cells.Find(What:=oSheet.Name, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
ActiveCell.Offset(0, 1).Select
Selection.Copy
Application.Workbooks(1).Activate
Sheets(oSheet.Name).Select
ActiveSheet.Paste
Next oSheet

I havent tested this so don't expect it to work first time

 

[Apollo6]

Olaf Bogus:

That works great!!! I had to customize it a little due to how the sheet data is laid out but, it loops right through them. Thanks for the tip!!!