Copy Data between sheets with a worksheet loop

Hi,

I am trying to get excel to loop through the worksheets in the workbook and copy data to another workbook.

eg.
Task of each loop:
Find the sheet in the destination workbook with which sheet name matches the Value of cell C9

Copy data from Source Worksheet to destination worksheet

Move to next worksheet in source workbook and repeat loop.

So depending on the value of C9 on each worksheet in source with dictate the sheet that received the data in the source...

I have had a bash at some code which I will post below, but I cant get it work just right. Possibly because the Source Workbook file name can be variable!

HELP please....

Sub endofmonth()
   Dim WS_Count As Integer
   Dim I As Integer

WSName = ThisWorkbook.Name

'Open Master workbook
Application.Workbooks.Open ("G:\Supervisors\rotas and holiday\WillsTest\Master.xls")

'Create worksheet loop

    WS_Count = ActiveWorkbook.Worksheets.Count

   ' Begin the loop.
   For I = 2 To WS_Count
'find corrosponding sheet
CurrentWs = ThisWorkbook.ActiveSheet.Range("C9").Value
Workbooks("Master.xls").Sheets(CurrentWs).Activate
    'Transpose

'Date
Workbooks(WSName).ActiveSheet.Range("C6").Copy
Workbooks("Master.xls").ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Script
Workbooks(WSName).ActiveSheet.Range("C8").Copy
Workbooks("Master.xls").ActiveSheet.Range("B4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'greeting
Workbooks(WSName).ActiveSheet.Range("C16").Copy
Workbooks("Master.xls").ActiveSheet.Range("C4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Script Leg
Workbooks(WSName).ActiveSheet.Range("C17").Copy
Workbooks("Master.xls").ActiveSheet.Range("D4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'data
Workbooks(WSName).ActiveSheet.Range("C18").Copy
Workbooks("Master.xls").ActiveSheet.Range("E4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Phonetics
Workbooks(WSName).ActiveSheet.Range("C19").Copy
Workbooks("Master.xls").ActiveSheet.Range("F4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Script adherence
Workbooks(WSName).ActiveSheet.Range("C20").Copy
Workbooks("Master.xls").ActiveSheet.Range("G4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Repeating
Workbooks(WSName).ActiveSheet.Range("C21").Copy
Workbooks("Master.xls").ActiveSheet.Range("H4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Upsell
Workbooks(WSName).ActiveSheet.Range("C22").Copy
Workbooks("Master.xls").ActiveSheet.Range("I4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Accurate Info provoded
Workbooks(WSName).ActiveSheet.Range("C23").Copy
Workbooks("Master.xls").ActiveSheet.Range("J4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Voice Quality
Workbooks(WSName).ActiveSheet.Range("C27").Copy
Workbooks("Master.xls").ActiveSheet.Range("L4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Call Control
Workbooks(WSName).ActiveSheet.Range("C28").Copy
Workbooks("Master.xls").ActiveSheet.Range("M4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Attentive
Workbooks(WSName).ActiveSheet.Range("C29").Copy
Workbooks("Master.xls").ActiveSheet.Range("N4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Signposting
Workbooks(WSName).ActiveSheet.Range("C30").Copy
Workbooks("Master.xls").ActiveSheet.Range("O4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Hold
Workbooks(WSName).ActiveSheet.Range("C31").Copy
Workbooks("Master.xls").ActiveSheet.Range("P4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Objection
Workbooks(WSName).ActiveSheet.Range("C32").Copy
Workbooks("Master.xls").ActiveSheet.Range("Q4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Sensitivity
Workbooks(WSName).ActiveSheet.Range("C33").Copy
Workbooks("Master.xls").ActiveSheet.Range("R4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Language
Workbooks(WSName).ActiveSheet.Range("C34").Copy
Workbooks("Master.xls").ActiveSheet.Range("S4").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next I

End Sub