excel - VBA code to iterate through sheets and copy specific data to specific cells

Question

Link to sample workbook:http://www.wikisend.com/download/245286/MIS%20-%20July%20%20(1).xlsm

The workbook has vba code which deletes all sheeets and then creates new sheet by copying sheets from other workbooks.

I want another vba code to automatically generate Executive Summary sheet with all formating and data.Some formulas arent well defined so they will have to be edited manually later. As other worksheets witll change every month manual referencing wud be tedious

My current code:

    Sub WorksheetLoop()

        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Select
        Sheets(Sheets.Count).Name = "Executive Summary"


        Sheets("Executive Summary").Select
'Some formatting code on sheet Executive Summary

    Dim ws As Worksheet
       ' Begin the loop.
currentRow=6 'start entering data from row 6
     For Each ws In ThisWorkbook.Worksheets

'Assign cell at row=current row and col B reference to cell G5 at sheet ws
'Assign cell at row=current row and col F reference to cell J15 at sheet ws

'Assign cell at row=current row and col H reference to cell T5 at sheet ws


'increment Current row by 1
    'don't know what to do here    
    Next ws

'select entire region( all filled cells)
'format text, add border etc

    End Sub

Please tell me syntax of referencing

Answer 1

Solved the problem

Sub test()

    Sheets("Executive Summary").Select
    Range("A6:P23").Select
    Range("P6").Activate
    Selection.ClearContents
    'Dim ws As Worksheet
    Dim Row As Integer
    Row = 6
    For Each ws In ThisWorkbook.Worksheets
     If ws.Name <> "Executive Summary" Then
        Range("A" & Row).Value = "='" & ws.Name & "'!C3"
        Range("B" & Row).Value = "='" & ws.Name & "'!G5"
        Range("C" & Row).Value = "='" & ws.Name & "'!G39"
        Range("D" & Row).Value = "='" & ws.Name & "'!H39"
        Range("E" & Row).Value = "=D" & Row & "-C" & Row
        Range("F" & Row).Value = "=IF(A" & Row & "=" & Chr(34) & "POWER" & Chr(34) & ",+E" & Row & "*B" & Row & ",+E" & Row & "*B" & Row & "/100)"
        Range("G" & Row).Value = "=IF(A" & Row & "=" & Chr(34) & "POWER" & Chr(34) & ",+(J" & Row & "-D" & Row & ")*B" & Row & ",+(J" & Row & "-D" & Row & ")*B" & Row & "/100)"
        '"=(J" & Row & "-D" & Row & ")*B" & Row & "/100"
        Range("H" & Row).Value = "=I" & Row & "-F" & Row & "-G" & Row
        Range("I" & Row).Value = 0
        Range("J" & Row).Value = "='" & ws.Name & "'!I39"
        Range("K" & Row).Value = "='" & ws.Name & "'!L5"
        Range("L" & Row).Value = "='" & ws.Name & "'!K39"
        Range("M" & Row).Value = "=G" & Row
        Range("N" & Row).Value = "=O" & Row & "-L" & Row & "-M" & Row
        Range("O" & Row).Value = "=I" & Row
        Range("P" & Row).Value = "='" & ws.Name & "'!M39"
        Row = Row + 1
     End If

     Next ws

End Sub