none
VBA to copy data to a template file then save it as a new file RRS feed

  • Question

  • Hi, I'm hoping to get some help here to automate the work that I have to do every 2 weeks.  The work is repetitive and hopefully a VBA script will help shorten sometimes a day's work to minutes.

    I'm using Excel 2007. There are 2 files.

    File A: This file is generated every 2 weeks. The data I want from this file is in Col B. Number of rows depending on transaction volume of that week. So minimum row could be 1, but so far never exceed 300 rows. It could be more in the future.

    File B: It's a template file, many fixed data in this file. Only 3 cells need changes. The 3 cells are Row 2 of Col B, C and F. All 3 cells will have the same data/value I copy from File A.

    This is what I want to do:

    1. Open File A, copy data/value in Col B, Row 2.
    2. Open File B (template), paste the data to Row 2 of Col B, C and F.
    3. Save File B as new file, in a designated folder e.g. "Books". New filename to use the exact value I copy from File A. E.g., if the value is "Books - ANA", then the filename will be "Books - ANA.xls".
    4. Repeat step 1. Copy data in Col B, Row 3 in File A.
    5. Repeat step 2.
    6. Repeat step 3.
    7. Repeat step 1. If there's no data in Col B, Row 4 in File A, stop the process.

    Thank you.
    Tuesday, September 10, 2013 1:49 AM

Answers

  • If I understand the requirement correctly with respect the destination of the date in the second and subsequent workbooks then the following should work if run from the open worksheet.

    Put the true paths and filename where indicated. If the values always go in row 2 of the destination worksheets rather than the same row as they appear in the source sheet then change

    .Range("B" & i) = xlSheet.Range("B" & i)

    to

    .Range("B2") = xlSheet.Range("B" & i)

    and similarly the following 2 lines.

    Sub CreateBooks()
    Dim xlBook As Workbook
    Dim xlNewBook As Workbook
    Dim xlSheet As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Const strPath As String = "C:\Path to Save Files\"
    Const strFileB As String = "C:\Path\FileB name.xlsx"
        Set xlBook = ActiveWorkbook
        Set xlSheet = xlBook.Sheets(1)
        LastRow = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
        For i = 2 To LastRow
            Set xlNewBook = Workbooks.Add(Template:=strFileB)
            With xlNewBook.Sheets(1)
                .Range("B" & i) = xlSheet.Range("B" & i)
                .Range("C" & i) = xlSheet.Range("B" & i)
                .Range("F" & i) = xlSheet.Range("B" & i)
            End With
            xlNewBook.SaveAs strPath & CStr(xlSheet.Range("B" & i)) & ".xlsx"
            xlNewBook.Close 0
            Set xlNewBook = Nothing
        Next i
        Set xlBook = Nothing
        Set xlSheet = Nothing
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by Chih Hung Friday, September 13, 2013 2:55 AM
    Wednesday, September 11, 2013 11:55 AM

All replies

  • What you ask is straightforward, but can you clarify a few things?

    You want to copy a single value from File A cell B2 and put that same value in each of the cells B2, C2 and F2 of File B? Then save that file with the Value from B2?

    What type of content is in B2?

    Then you say to repeat, so is the new data going in the same File B or as implied a new File B i.e are we producing one file with all the changes, or as many files as there are rows in column B, each saved with the name of the source value?

    And if it is going in different files which row is it going to in the different files? Your message seems to indicate the same row as the source row, which suggests that you are producing one file, rather than many files.


    Graham Mayor - Word MVP
    www.gmayor.com

    Tuesday, September 10, 2013 11:17 AM
  • Thanks for replying.

    Example of data in Col B of File A:

    Book - ALTA
    Book - ANA
    Book - ASK
    Book - ATML

    If I have 4 rows of data in File A, I should have 4 new files, each file saved with the same name as the source data/value.

    File B acts like a template, it will not be saved.

    Wednesday, September 11, 2013 7:07 AM
  • If I understand the requirement correctly with respect the destination of the date in the second and subsequent workbooks then the following should work if run from the open worksheet.

    Put the true paths and filename where indicated. If the values always go in row 2 of the destination worksheets rather than the same row as they appear in the source sheet then change

    .Range("B" & i) = xlSheet.Range("B" & i)

    to

    .Range("B2") = xlSheet.Range("B" & i)

    and similarly the following 2 lines.

    Sub CreateBooks()
    Dim xlBook As Workbook
    Dim xlNewBook As Workbook
    Dim xlSheet As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Const strPath As String = "C:\Path to Save Files\"
    Const strFileB As String = "C:\Path\FileB name.xlsx"
        Set xlBook = ActiveWorkbook
        Set xlSheet = xlBook.Sheets(1)
        LastRow = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
        For i = 2 To LastRow
            Set xlNewBook = Workbooks.Add(Template:=strFileB)
            With xlNewBook.Sheets(1)
                .Range("B" & i) = xlSheet.Range("B" & i)
                .Range("C" & i) = xlSheet.Range("B" & i)
                .Range("F" & i) = xlSheet.Range("B" & i)
            End With
            xlNewBook.SaveAs strPath & CStr(xlSheet.Range("B" & i)) & ".xlsx"
            xlNewBook.Close 0
            Set xlNewBook = Nothing
        Next i
        Set xlBook = Nothing
        Set xlSheet = Nothing
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by Chih Hung Friday, September 13, 2013 2:55 AM
    Wednesday, September 11, 2013 11:55 AM
  • Hi

    Do you have access to a Microsoft Sql Server Integration Server?

    Reason I ask is that we usually develop SSIS packages to automate things like this - instead of using VBA embedded in workbook - because then you would still have to open the workbook and manually run this.

    With SSIS it can be scheduled and run automated daily etc....


    I.W Coetzer

    Wednesday, September 11, 2013 12:07 PM
  • Thank you Graham.  The script works.
    Friday, September 13, 2013 2:55 AM
  • No, we don't use MSSQL server.

    The code Graham wrote already helps cut down a lot time to manually create new files.

    Friday, September 13, 2013 2:58 AM
  • Hello, Thank you for the code, it was a huge help. But I have a question. How do I transfer data from, let's say the second sheet of a first workbook to the third sheet of another workbook just one specific data? Thank you. 
    Tuesday, April 10, 2018 5:56 AM