none
Automatically create new Excel file from template using data from a selected row in a separate Excel file RRS feed

  • Question

  • I am fairly new to Macro and VBA in Excel. I am trying to reduce the amount of work involved in a task that is performed often in our office. Currently, the task involves using 2 separate Excel files. One file is a tracking sheet and the second is the document that gets sent out to the client. The second document has all the same information that is inserted into the tracking sheet. What I would like to do is have the information that is in one row on the tracking sheet populate into a new excel file that is created automatically from an existing template.

    I have been able to make this partially work. What I have been able to achieve is have the cell that is selected in the tracking sheet (the description field) copy into the new file that is created from the template that is on our server.

    Sub NewRFI()
    '
    ' NewRFI Macro
    '

    '
        MsgBox Selection.Address(ReferenceStyle:=xlA1, _
                               RowAbsolute:=False, ColumnAbsolute:=False)
        Selection.Copy
        Workbooks.Add Template:= _
            "template file path goes here"
        Range("C14").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Range("C14").Select
        Application.CutCopyMode = False
    End Sub

    What I need to do is get the rest of the information from that same row in the tracking sheet to copy to the new file as well. The other information includes the document number (one cell to the left of the selected cell), who it is from (one cell to the right of the selected cell), and the date (two cells to the right of the selected cell). As stated, this information is all in the same row on the tracking sheet. Each piece of information needs to be copied to a certain cell in the new file and I do not want the source formatting to be copied to the new file.

    Ideally, I would like it to operate like so: select the first cell in the row (in this case would be the document number field). Then click a button to run the macro that will then copy the information from that row in the tracking sheet to specific cells in the new file that is created from the template.

    This process will save us from having to either manually copy and paste the information into the new file or re-type the same information that is already in the tracking sheet.

    This is probably a simple thing to do but it would be great if anybody had some insight on this issue. Thanks in advance!


    Saturday, February 14, 2015 7:07 AM

Answers

  • Try code like the following. You will have to change the target cells (D25, F1 and H4) to the ones you want, I just used some arbitrary cells as example.

    Sub NewRFI()
        Dim rngCell As Range
        Set rngCell = ActiveCell
        MsgBox Selection.Address(ReferenceStyle:=xlA1, _
                               RowAbsolute:=False, ColumnAbsolute:=False)
        Workbooks.Add Template:="template file path goes here"
        Range("C14").Value = rngCell.Value
        ' Change the target cells as needed
        Range("D25").Value = rngCell.Offset(0, -1).Value
        Range("F1").Value = rngCell.Offset(0, 1).Value
        Range("H4").Value = rngCell.Offset(0, 2).Value
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by L.HlModerator Tuesday, February 24, 2015 6:05 AM
    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 9:13 AM
    Saturday, February 14, 2015 10:55 AM

All replies

  • Try code like the following. You will have to change the target cells (D25, F1 and H4) to the ones you want, I just used some arbitrary cells as example.

    Sub NewRFI()
        Dim rngCell As Range
        Set rngCell = ActiveCell
        MsgBox Selection.Address(ReferenceStyle:=xlA1, _
                               RowAbsolute:=False, ColumnAbsolute:=False)
        Workbooks.Add Template:="template file path goes here"
        Range("C14").Value = rngCell.Value
        ' Change the target cells as needed
        Range("D25").Value = rngCell.Offset(0, -1).Value
        Range("F1").Value = rngCell.Offset(0, 1).Value
        Range("H4").Value = rngCell.Offset(0, 2).Value
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by L.HlModerator Tuesday, February 24, 2015 6:05 AM
    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 9:13 AM
    Saturday, February 14, 2015 10:55 AM
  • Thanks! I will give this a try. I appreciate the help!
    Wednesday, February 18, 2015 5:46 AM