Referencing date and worksheet tab
 

I would like to take a main workbook and right a code into my current code
that would look at the date on the main workbook and cross refernce that to
the data workbook. The data workbook already has the tabs named for each day
of the month (1,2,3,4...). I have a main worksheet that has the =now()
producing the date in format dd. How do I make this dynamic so that it will
look at different tabs that corrispond with the current date??
This is the current code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/5/2006 by Eric Wagner
'

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls"
Sheets("1").Select
ActiveWindow.SmallScroll Down:=13
Range("C58").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.ActivateNext
Range("G6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ActivateNext
ActiveWindow.Close
End Sub

--
EW - Analyst

Referencing date and worksheet tab
 

Maybe something like this:

Option Explicit
sub testme()

dim MainWks as worksheet
Dim DateCell as range
dim DateWks as worksheet
dim DateWkbk as workbook

set mainwks = thisworkbook.worksheets("Main")
set datecell = mainwks.range("a1")

set datewkbk = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls")

set datewks = datewkbk.worksheets(cstr(datecell.text))

'do what you need to do to datewks??
datewks.range("a1:b99").copy _
destination:=mainwks.range("x99")

datewkbk.close savechanges:=false

end sub

Untested, but it compiled ok.

ewagz wrote:

I would like to take a main workbook and right a code into my current code
that would look at the date on the main workbook and cross refernce that to
the data workbook. The data workbook already has the tabs named for each day
of the month (1,2,3,4...). I have a main worksheet that has the =now()
producing the date in format dd. How do I make this dynamic so that it will
look at different tabs that corrispond with the current date??
This is the current code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/5/2006 by Eric Wagner
'

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls"
Sheets("1").Select
ActiveWindow.SmallScroll Down:=13
Range("C58").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.ActivateNext
Range("G6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ActivateNext
ActiveWindow.Close
End Sub

--
EW - Analyst

--

Dave Peterson

Referencing date and worksheet tab
 

The date portion worked great! Is there a way to have it paste the data in
the last part of the code to paste special with value only?

'do what you need to do to datewks??
DateWks.Range("C58").Copy _
Destination:=MainWks.Range("G6")

DateWkbk.Close savechanges:=False
--
EW - Analyst


"Dave Peterson" wrote:

Maybe something like this:

Option Explicit
sub testme()

dim MainWks as worksheet
Dim DateCell as range
dim DateWks as worksheet
dim DateWkbk as workbook

set mainwks = thisworkbook.worksheets("Main")
set datecell = mainwks.range("a1")

set datewkbk = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls")

set datewks = datewkbk.worksheets(cstr(datecell.text))

'do what you need to do to datewks??
datewks.range("a1:b99").copy _
destination:=mainwks.range("x99")

datewkbk.close savechanges:=false

end sub

Untested, but it compiled ok.

ewagz wrote:

I would like to take a main workbook and right a code into my current code
that would look at the date on the main workbook and cross refernce that to
the data workbook. The data workbook already has the tabs named for each day
of the month (1,2,3,4...). I have a main worksheet that has the =now()
producing the date in format dd. How do I make this dynamic so that it will
look at different tabs that corrispond with the current date??
This is the current code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/5/2006 by Eric Wagner
'

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls"
Sheets("1").Select
ActiveWindow.SmallScroll Down:=13
Range("C58").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.ActivateNext
Range("G6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ActivateNext
ActiveWindow.Close
End Sub

--
EW - Analyst

--

Dave Peterson

Referencing date and worksheet tab
 

datewks.range("C58").copy
mainwks.range("g6").pastespecial paste:=xlpastevalues

or

mainwks.range("g6").value = datewks.range("C58").value



ewagz wrote:

The date portion worked great! Is there a way to have it paste the data in
the last part of the code to paste special with value only?

'do what you need to do to datewks??
DateWks.Range("C58").Copy _
Destination:=MainWks.Range("G6")

DateWkbk.Close savechanges:=False
--
EW - Analyst

"Dave Peterson" wrote:

Maybe something like this:

Option Explicit
sub testme()

dim MainWks as worksheet
Dim DateCell as range
dim DateWks as worksheet
dim DateWkbk as workbook

set mainwks = thisworkbook.worksheets("Main")
set datecell = mainwks.range("a1")

set datewkbk = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls")

set datewks = datewkbk.worksheets(cstr(datecell.text))

'do what you need to do to datewks??
datewks.range("a1:b99").copy _
destination:=mainwks.range("x99")

datewkbk.close savechanges:=false

end sub

Untested, but it compiled ok.

ewagz wrote:

I would like to take a main workbook and right a code into my current code
that would look at the date on the main workbook and cross refernce that to
the data workbook. The data workbook already has the tabs named for each day
of the month (1,2,3,4...). I have a main worksheet that has the =now()
producing the date in format dd. How do I make this dynamic so that it will
look at different tabs that corrispond with the current date??
This is the current code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/5/2006 by Eric Wagner
'

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls"
Sheets("1").Select
ActiveWindow.SmallScroll Down:=13
Range("C58").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.ActivateNext
Range("G6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ActivateNext
ActiveWindow.Close
End Sub

--
EW - Analyst

--

Dave Peterson


--

Dave Peterson

Referencing date and worksheet tab
 

The first code worked great! Thanks for the help!
--
EW - Analyst


"Dave Peterson" wrote:

datewks.range("C58").copy
mainwks.range("g6").pastespecial paste:=xlpastevalues

or

mainwks.range("g6").value = datewks.range("C58").value



ewagz wrote:

The date portion worked great! Is there a way to have it paste the data in
the last part of the code to paste special with value only?

'do what you need to do to datewks??
DateWks.Range("C58").Copy _
Destination:=MainWks.Range("G6")

DateWkbk.Close savechanges:=False
--
EW - Analyst

"Dave Peterson" wrote:

Maybe something like this:

Option Explicit
sub testme()

dim MainWks as worksheet
Dim DateCell as range
dim DateWks as worksheet
dim DateWkbk as workbook

set mainwks = thisworkbook.worksheets("Main")
set datecell = mainwks.range("a1")

set datewkbk = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls")

set datewks = datewkbk.worksheets(cstr(datecell.text))

'do what you need to do to datewks??
datewks.range("a1:b99").copy _
destination:=mainwks.range("x99")

datewkbk.close savechanges:=false

end sub

Untested, but it compiled ok.

ewagz wrote:

I would like to take a main workbook and right a code into my current code
that would look at the date on the main workbook and cross refernce that to
the data workbook. The data workbook already has the tabs named for each day
of the month (1,2,3,4...). I have a main worksheet that has the =now()
producing the date in format dd. How do I make this dynamic so that it will
look at different tabs that corrispond with the current date??
This is the current code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/5/2006 by Eric Wagner
'

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Name\Desktop\LA Test\LA test.xls"
Sheets("1").Select
ActiveWindow.SmallScroll Down:=13
Range("C58").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.ActivateNext
Range("G6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ActivateNext
ActiveWindow.Close
End Sub

--
EW - Analyst

--

Dave Peterson


--

Dave Peterson