Exporting data from master file to pre-populate many excel files from a template

thats right Naveed

Thought I'd contribute for fun...run the test method in 'Test' Standard Module.

Thanks vbc22 your's works perfectly too. I was curious as to how the link pairs worked?

I'm guessing all i'd need to do is modify that section of code for my actual files as my master file has over 100 columns and the template has 21 input fields.

Naveed i'm hoping you can help me amend your original code relating to an update made in the the "Financials" tab.

I've included my actual amended code below and a new attachment with the "Financials" tab amended. The data from the master file needs to be exported into the template horizontally rather than vertically (per my first attached template). The "RAG" import works perfectly

Sub Monthly_report_export()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
PR = ActiveWorkbook.Name
Dim sh As Worksheet
Dim LR As Long
Dim FR As Long
FPath = "D:\Documents and Settings\uczbmon\Desktop\New Folder (2)"
Set sh = Workbooks("" & PR & "").Sheets("Project Register")
With sh
    LR = .Range("L" & .Rows.Count).End(xlUp).Row
    RAG = Array("M", "L", "F", "T", "AF", "AG", "U", "V", "AC", "Z", "AA", "P", "BO", "BQ", "BR", "BT", "BV", "BX", "BZ", "CB", "CD")
    For j = 2 To LR
    Workbooks.Open FPath & "\" & "Monthly Report Template.xlsx"
    
MPR = ActiveWorkbook.Name
    FR = 2 'Workbooks("" & mybk & "").Sheets("RAG").Range("B" & Rows.Count).End(xlUp).Row
    'Workbooks("" & mymc & "").Sheets("Sheet1").Activate
    For i = LBound(RAG, 1) To UBound(RAG, 1)
        .Range(RAG(i) & j).Copy Workbooks("" & MPR & "").Sheets("RAG Status").Range("B" & FR)
            
            FR = FR + 1
        Next
        'Workbooks("" & mybk & "").Sheets("RAG").Range("A9").EntireRow.Insert shift:=xlDown
        FR = 2
    FIN = Array("BL", "BM", "BN", "BD", "BG")
    For k = LBound(FIN, 1) To UBound(FIN, 1)
        .Range(FIN(k) & j).Copy Workbooks("" & MPR & "").Sheets("Financials").Range("B" & FR)
        
        FR = FR + 1
    Next
    ActiveWorkbook.SaveAs FileName:=FPath & "\" & .Range("L" & j) & "_" & .Range("M" & j) & ".xls", FileFormat:=xlExcel8
    ActiveWorkbook.Close False
    Next
End With
MsgBox "Done ! " & "File Saved Here - " & FPath, vbInformation
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Thanks Naveed, code works perfectly for the template v2 but struggling to modify below bit of code relating to the financials tab for my actual file but i'll play around with it a bit and try figure it out.

Columns "L", "M" & "N" need to export to cells C17, D17 & E17
and Columns "J" & "K" need to export to cells B22 & C22 in financials tab

        FR = 3
        FR1 = 4
    fin = Array("L", "M", "N", "J", "K")
    For k = LBound(fin, 1) To UBound(fin, 1)
        .Range(fin(k) & j).Copy Workbooks("" & mybk & "").Sheets("Financials").Cells(FR1, FR)
        If FR = 5 Then
            FR = 3
            FR1 = FR1 + 3
        Else
            FR = FR + 1
        End If

Thanks again Naveed, the correct data from the master file is being exported to the template fine but the data for "J" and "K" each need to move one cell to the left i.e. starting in column B cell B22 and C22

is there a way to keep the cell formatting of the template as currently copying the format from the master file?

You are a machine Naveed ! Works exactly how i was hoping it would. Much appreciated.

Added to your reputation!