Results 1 to 18 of 18

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

Threaded View

  1. #9
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

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

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto copy data from other Excel files to master file...
    By NeoNirav in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2013, 12:13 AM
  2. Vlookup query : How to pull data from raw excel files into a master excel file?
    By jamesjamesiata in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2013, 06:26 AM
  3. [SOLVED] Import data from excel files to append master file
    By kdawgpl9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2013, 04:32 PM
  4. Macro to populate a word template and feed an excel master sheet
    By hieldy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2013, 05:40 AM
  5. Replies: 1
    Last Post: 03-13-2012, 09:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1