Help naming a sheet from a macro

[ireland87]

Hi

my macro currently creates a new sheet within another document called Analysis_v3.xlsm and gives it a name of yesterdays date

Workbooks.Open Filename:="filepath Analysis_v3.xlsm"
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = Format(Date - 1, "mm-dd")

however I would like the name of the new created sheet to come from a cell from within the workbook that exports the data.

anyone got any ideas?

 

[Scott T]

Change workbook and sheet name and cell to match your data

ActiveSheet.Name =Workbooks(wbname). Sheets("Sheet1").Range("A1")

 

[ireland87]

Change workbook and sheet name and cell to match your data

ActiveSheet.Name =Workbooks(wbname). Sheets("Sheet1").Range("A1")

Hi

can it look for a workbook with a name_*.xml?

for example:
ActiveSheet.Name = Workbooks(Summary Form_*.xml).Sheets("Input1").Range("A2")

Do i also need to put the full path of the workbook?
ActiveSheet.Name = Workbooks(C:/Summary Form_*.xml).Sheets("Input1").Range("A2")

thanks again for any help

 

[ireland87]

Heres the full macro if that helps?

Sub ExportSummary()


Application.ScreenUpdating = False


Set range1 = ThisWorkbook.Sheets("Data FORM").Range("A7:H" & Range("A27").End(xlUp).Row)
Set range2 = ThisWorkbook.Sheets("Data FORM").Range("A28:H" & Range("A65").End(xlUp).Row)


Set multiplerange = Union(range1, range2)




multiplerange.Select
multiplerange.Copy


Workbooks.Open Filename:="L:Path\Analysis_v3.xlsm"
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = Workbooks(Summary_Form.xlsm).Sheets("Input1").Range("A2")
ActiveSheet.Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Cells.Select
ActiveSheet.Cells.EntireColumn.AutoFit
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False


End Sub

 

[Scott T]

You are specifying a specific workbook otherwise the code does not know what workbook to use. You do not need the path, Summary_Form.xlsm should be open.

ActiveSheet.Name = Workbooks("Summary_Form.xlsm").Sheets("Input1").Range("A2")