Date change macro between worksheets
 

I am trying to change the date on a newly created worksheet by adding t
a value contained in another open worksheet.

I keep having subscript errors because I don't know how to switc
between worksheets.

Here is an example of code:

Worksheets(BackupFileName).Range("B4").Value
Worksheets(NewFileName).Range("B4").Value + 1

BackupFileName and NewFileName are both strings, can someone help m
reference them in my code?

Thank

--
Message posted from http://www.ExcelForum.com

Date change macro between worksheets
 

if, by another open worksheet, you mean a completely different file, i think
you need to use the WORKBOOKS property (see your excel help): -

Workbooks("BOOK1").ActivateThis example opens the workbook Large.xls.

Workbooks.Open filename:="LARGE.XLS"i'm sure somone will be along shortly to
fill in the rest of the details (and if they do, could they please try to
answer my post earlier today regarding active directory)!

cheers,

tim

"cphenley " wrote in message
...
I am trying to change the date on a newly created worksheet by adding to
a value contained in another open worksheet.

I keep having subscript errors because I don't know how to switch
between worksheets.

Here is an example of code:

Worksheets(BackupFileName).Range("B4").Value =
Worksheets(NewFileName).Range("B4").Value + 1

BackupFileName and NewFileName are both strings, can someone help me
reference them in my code?

Thanks


---
Message posted from http://www.ExcelForum.com/

Date change macro between worksheets
 

Hi,

I think you may be referring to changing the value in a cell of a worksheet
in one workbook, with the value in a cell of a worksheet from another
workbook (open or closed).

So, assuming the two workbooks are called 'Book1.xsl' & 'Book2.xls' then you
need to reference the workbooks through the workbooks collection...

In Excel XP this VBA code works:


Dim BackupFileName As String
Dim NewFileName As String

NewFileName = "Book2"
BackupFileName = "Book1"

Workbooks(BackupFileName).Worksheets(1).Range("B4" ).Value =
Workbooks(NewFileName).Worksheets(1).Range("B4").V alue + 1

<<<

Notice:
* that I have referenced the workbooks without the extension ( so 'Book1'
instead of 'Book1.xls')
* that I have referenced the worksheets collection also...but if you have
named ranges you don't need to reference the worksheet as these would be
unique within the workbook.

U could reference the worksheets by name in quotes also, or as a string like
with the workbooks...

HTH

Philip

"cphenley " wrote:

I am trying to change the date on a newly created worksheet by adding to
a value contained in another open worksheet.

I keep having subscript errors because I don't know how to switch
between worksheets.

Here is an example of code:

Worksheets(BackupFileName).Range("B4").Value =
Worksheets(NewFileName).Range("B4").Value + 1

BackupFileName and NewFileName are both strings, can someone help me
reference them in my code?

Thanks


---
Message posted from http://www.ExcelForum.com/

Date change macro between worksheets
 

* that I have referenced the workbooks without the extension ( so 'Book1'
instead of 'Book1.xls')

this will cause an error for some, perhaps most people based on their
windows setting (for hidden extensions).

First, since the default name of a new workbook would be Book1 without
extension, it is better to use something different. Assume the files have
been saved as wkbk1.xls and wkbk2.xls

Dim BackupFileName As String
Dim NewFileName As String

NewFileName = "wkbk2.xls"
BackupFileName = "wkbk1.xls"

Workbooks(BackupFileName).Worksheets(1).Range("B4" ).Value = _
Workbooks(NewFileName).Worksheets(1).Range("B4").V alue + 1

Would work regardless of the windows setting for hidden file extensions.

--
Regards,
Tom Ogilvy


"Philip" wrote in message
...
Hi,

I think you may be referring to changing the value in a cell of a
worksheet
in one workbook, with the value in a cell of a worksheet from another
workbook (open or closed).

So, assuming the two workbooks are called 'Book1.xsl' & 'Book2.xls' then
you
need to reference the workbooks through the workbooks collection...

In Excel XP this VBA code works:


Dim BackupFileName As String
Dim NewFileName As String

NewFileName = "Book2"
BackupFileName = "Book1"

Workbooks(BackupFileName).Worksheets(1).Range("B4" ).Value =
Workbooks(NewFileName).Worksheets(1).Range("B4").V alue + 1

<<<

Notice:
* that I have referenced the workbooks without the extension ( so 'Book1'
instead of 'Book1.xls')
* that I have referenced the worksheets collection also...but if you have
named ranges you don't need to reference the worksheet as these would be
unique within the workbook.

U could reference the worksheets by name in quotes also, or as a string
like
with the workbooks...

HTH

Philip

"cphenley " wrote:

I am trying to change the date on a newly created worksheet by adding to
a value contained in another open worksheet.

I keep having subscript errors because I don't know how to switch
between worksheets.

Here is an example of code:

Worksheets(BackupFileName).Range("B4").Value =
Worksheets(NewFileName).Range("B4").Value + 1

BackupFileName and NewFileName are both strings, can someone help me
reference them in my code?

Thanks


---
Message posted from http://www.ExcelForum.com/

Date change macro between worksheets
 

Thanks for the help, but I haven't been able to successfully implement
your suggestions.

My main problem is that I can't define the names of the two workbooks
as suggested because they change every time the program is run.

I think posting the code is easier than an explination, I apologize for
the excess:


Sub SelectNewFile()
Dim NewFileName As String, OldFileName As String, Fdate As String,
NewName As String
Dim OldName As String, awb As Workbook, BackupFileName As String, i
As Integer
Dim OK As Boolean, astrLinks As Variant, iCtr As Long, NewW As
String

If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
BackupFileName = awb.Name
OK = False
On Error GoTo NotAbleToSave
If Dir("I:\Pyro-Process reports\SIC-2\" & BackupFileName) < ""
Then
Kill "I:\Pyro-Process reports\SIC-2\" & BackupFileName
End If
With awb
Application.StatusBar = "Saving this workbook..."
Save
Application.StatusBar = "Saving this workbook backup..."
SaveCopyAs "I:\Pyro-Process reports\SIC-2\" &
BackupFileName
OK = True
End With
End If

NotAbleToSave:
Set awb = Nothing
Application.StatusBar = False

If Range("B4").Value = "" Then
Range("B4").Value = InputBox("Please enter the new date",
"Date")
Else
Range("B4").Value = Range("B4").Value + 1
End If
Range("B4").NumberFormat = "mm-dd-yy;@"
Fdate = Format(Range("B4"), "mm_dd_yy")
NewFileName = "SIC_2-" & Fdate & ".xls"

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)

If IsArray(astrLinks) Then
For iCtr = LBound(astrLinks) To UBound(astrLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(iCtr), _
Type:=xlLinkTypeExcelLinks
Next iCtr
End If

Set awb = ActiveWorkbook

' Open new file
Workbooks.Open "I:\Pyro-Process reports\SIC-2\template.xls"
-
NewW = "template.xls"

Workbooks(NewW).Worksheets(1).Range("B4").Value = _
Workbooks(BackupFileName).Worksheets(1).Range("B4" ).Value 1

-
' Save as new file using date
ActiveWorkbook.SaveAs "I:\Pyro-Process reports\SIC-2\" &
NewFileName

' Close old file
awb.Close SaveChanges:=True

Calculate

End Sub


Hopefully that explains what I am trying to do better than I can
describe. If anyone can explain or show me how to implement the date
change, I would be very grateful.

Thanks.


---
Message posted from http://www.ExcelForum.com/

Date change macro between worksheets
 

Sub SelectNewFile()
Dim NewFileName As String, OldFileName As String
Dim Fdate As String, NewName As String
Dim OldName As String, awb As Workbook
Dim BackupFileName As String, i As Integer
Dim OK As Boolean, astrLinks As Variant
Dim iCtr As Long, NewW As String
Dim awb As Workbook, sh As Worksheet
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
End If
BackupFileName = awb.Name
OK = False
On Error GoTo NotAbleToSave
If Dir("I:\Pyro-Process reports\SIC-2\" _
& BackupFileName) < "" Then
Kill "I:\Pyro-Process reports\SIC-2\" & BackupFileName
End If
With awb
Application.StatusBar = "Saving this workbook..."
Save
Application.StatusBar = "Saving this workbook backup..."
awb.SaveCopyAs "I:\Pyro-Process reports\SIC-2\" & _
BackupFileName
OK = True
End With

NotAbleToSave:
Application.StatusBar = False
Set sh = ActiveSheet
If Range("B4").Value = "" Then
Range("B4").Value = _
InputBox("Please enter the new date", _
"Date")
Else
Range("B4").Value = Range("B4").Value + 1
End If
Range("B4").NumberFormat = "mm-dd-yy"
Fdate = Format(Range("B4"), "mm_dd_yy")
NewFileName = "SIC_2-" & Fdate & ".xls"

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:= _
xlLinkTypeExcelLinks)

If IsArray(astrLinks) Then
For iCtr = LBound(astrLinks) To UBound(astrLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(iCtr), _
Type:=xlLinkTypeExcelLinks
Next iCtr
End If


' Open new file
Workbooks.Open "I:\Pyro-Process reports\SIC-2\template.xls"
NewW = "template.xls"

Workbooks(NewW).Worksheets(1) _
.Range("B4").Value = _
sh.Range("B4").Value + 1


' Save as new file using date
ActiveWorkbook.SaveAs "I:\Pyro-Process reports\SIC-2\" & _
NewFileName

' Close old file
' if the code is in awb, then the code stops when
' awb is closed
awb.Close SaveChanges:=True

Calculate

End Sub

--
Regards,
Tom Ogilvy

"cphenley " wrote in message
...
Thanks for the help, but I haven't been able to successfully implement
your suggestions.

My main problem is that I can't define the names of the two workbooks
as suggested because they change every time the program is run.

I think posting the code is easier than an explination, I apologize for
the excess:


Sub SelectNewFile()
Dim NewFileName As String, OldFileName As String, Fdate As String,
NewName As String
Dim OldName As String, awb As Workbook, BackupFileName As String, i
As Integer
Dim OK As Boolean, astrLinks As Variant, iCtr As Long, NewW As
String

If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
BackupFileName = awb.Name
OK = False
On Error GoTo NotAbleToSave
If Dir("I:\Pyro-Process reports\SIC-2\" & BackupFileName) < ""
Then
Kill "I:\Pyro-Process reports\SIC-2\" & BackupFileName
End If
With awb
Application.StatusBar = "Saving this workbook..."
Save
Application.StatusBar = "Saving this workbook backup..."
SaveCopyAs "I:\Pyro-Process reports\SIC-2\" &
BackupFileName
OK = True
End With
End If

NotAbleToSave:
Set awb = Nothing
Application.StatusBar = False

If Range("B4").Value = "" Then
Range("B4").Value = InputBox("Please enter the new date",
"Date")
Else
Range("B4").Value = Range("B4").Value + 1
End If
Range("B4").NumberFormat = "mm-dd-yy;@"
Fdate = Format(Range("B4"), "mm_dd_yy")
NewFileName = "SIC_2-" & Fdate & ".xls"

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)

If IsArray(astrLinks) Then
For iCtr = LBound(astrLinks) To UBound(astrLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(iCtr), _
Type:=xlLinkTypeExcelLinks
Next iCtr
End If

Set awb = ActiveWorkbook

' Open new file
Workbooks.Open "I:\Pyro-Process reports\SIC-2\template.xls"
-
NewW = "template.xls"

Workbooks(NewW).Worksheets(1).Range("B4").Value = _
Workbooks(BackupFileName).Worksheets(1).Range("B4" ).Value 1

-
' Save as new file using date
ActiveWorkbook.SaveAs "I:\Pyro-Process reports\SIC-2\" &
NewFileName

' Close old file
awb.Close SaveChanges:=True

Calculate

End Sub


Hopefully that explains what I am trying to do better than I can
describe. If anyone can explain or show me how to implement the date
change, I would be very grateful.

Thanks.


---
Message posted from http://www.ExcelForum.com/

Date change macro between worksheets
 

Thanks Tom!

I had no idea how to reference between worksheets and workbooks before
Now I have a working example and the knowledge

--
Message posted from http://www.ExcelForum.com