Excel Macro - copy cell contents to name a new sheet in another spreadsheet

[brandc]

Hi,

I need some help please.

I am attempting to create a new spreadsheet from the macro and name
all the sheets according
to data in the original spreadsheet.

The original spreadsheet can change every month - so I need - what is
"copied" to be the name of each sheet.

my attempt below - actual hardcodes the "result" - I need it to be
what was copied -

Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\Jan2004b.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

Windows("Jan2004.xls").Activate
Sheets("Numbers").Select
Range("A2").Select
Selection.Copy

Windows("Jan2004b.xls").Activate
Sheets.Add.Name = "1145"
' This needs to be the selection.copy above



Windows("Jan2004.xls").Activate
Range("A3").Select
Selection.Copy
Windows("Jan2004b.xls").Activate
Sheets.Add.Name = "1308"
' This needs to be the selection.copy above


Windows("Jan2004.xls").Activate
Range("A4").Select
Selection.Copy
Windows("Jan2004b.xls").Activate
Sheets.Add.Name = "1370"
' This needs to be the selection.copy above


Windows("Jan2004.xls").Activate

Could you please advise - it would be greatly appreciated


Regards
Christine Brand

 

[mudraker]

If I understand your problem correctly this should help you


Sub dddd()

Dim wsCopy As Worksheet
Dim wbNew As Workbook

Set wsCopy = Workbooks("Jan 2004.xls"). _
Sheets("Numbers")

Set wbNew = Workbooks.Add
ActiveWorkbook.SaveAs FileName:= _
"C:\temp\Jan2004b.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Debug.Print wbNew.Name

wbNew.Sheets.Add.Name = "1145"
wsCopy.Range("a2").Copy Destination:= _
wbNew.Sheets("1145").Range("a1")

wbNew.Sheets.Add.Name = "1308"
wsCopy.Range("a3").Copy Destination:= _
wbNew.Sheets("1308").Range("a1")

wbNew.Sheets.Add.Name = "1370"
wsCopy.Range("a4").Copy Destination:= _
wbNew.Sheets("1370").Range("a1")

wsCopy.Activate

End Su

 

[brandc]

Thanks Mudraker But!!!!!!

The numbers (names) of the sheets I need to create are
coming from the cells in the first spreadsheet.

ie.
Jan2004.xls
sheet (numbers)

a2 1145
a3 1308
a4 1370 etc

These numbers can change in the next month Feb2004 etc

So I need them to be dynamic in the macro

So when I say Copy a2
I then need the new sheet I create in the new spreadsheet
Jan2004b.xls
to be the VALUE that is in a2 (not a hardcoded value) as I am
getting now.

Next Month a2 may have a value of 1068 and if (1145) is
hardcoded in the macro, I wont be getting 1068.

Is this clear as mud?

Hope it is clearer - I appreciate any help - greatly.

Thanks
CB