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

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

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
>

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

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
>