B
brandc
All messages from thread
Message 1 in thread
From: (e-mail address removed) ([email protected])
Subject: Excel Macro - copy cell contents to name a new sheet in
another spreadsheet
View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-05-11 22:20:16 PST
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
Post a follow-up to this message
Message 2 in thread
From: mudraker < ([email protected])
Subject: Re: Excel Macro - copy cell contents to name a new sheet in
another spreadsheet
View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-05-11 23:10:55 PST
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 Sub
---
Message posted
Post a follow-up to this message
Message 3 in thread
From: (e-mail address removed) ([email protected])
Subject: Re: Excel Macro - copy cell contents to name a new sheet in
another spreadsheet
View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-05-13 16:52:11 PST
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
Message 1 in thread
From: (e-mail address removed) ([email protected])
Subject: Excel Macro - copy cell contents to name a new sheet in
another spreadsheet
View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-05-11 22:20:16 PST
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
Post a follow-up to this message
Message 2 in thread
From: mudraker < ([email protected])
Subject: Re: Excel Macro - copy cell contents to name a new sheet in
another spreadsheet
View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-05-11 23:10:55 PST
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 Sub
---
Message posted
Post a follow-up to this message
Message 3 in thread
From: (e-mail address removed) ([email protected])
Subject: Re: Excel Macro - copy cell contents to name a new sheet in
another spreadsheet
View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-05-13 16:52:11 PST
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