Export and Name Copy of Data within File 2

[cghoga]

Greetings,

Many thanks in advance for your help.

I have a report template in Excel and I added a button with macro that copies and pastes the desired portion of the template to a new Excel file.

My question is how could I automatically save the file per a value in the template and the file would remain open?

For example, if the company name cell in the template is'John Doe' and the report type cell is 'CTD' the file name would say John Doe CTD CurrentDate.xls.

Here is the code I have so far.

Columns("A:K").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.Zoom = 75

Thanks for any and all help!

 

[SkipVought]

Hi,

Please use Code Tags (TGML)

    Dim wb as workbook, sCoName as string, sRptTyp as string

'assuming that the value is in the next row in the same column...
    sCoName =  Cells.find("company name").offset(1).value
    sRptTyp =  Cells.find("report type").offset(1).value

    Columns("A:K").Copy
    set wb = Workbooks.Add
    with wb.Sheets(1).Cells(1,1)
      .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
      .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    end with

    wb.saveas name:=sCoName & " " & sRptTyp & " " & format(date, "yyyymmdd") & ".xls"

of course, you also need a path.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cghoga]

Groovy Skip, thanks a lot!

Will use code tags from now on.

Thanks again