I have an input sheet that allows a user to fill in various time information
In this same sheet I have a row that has all the headings I want to to save
in a database. In the next row I have the formulas that references the input
cells

I have another sheet that I want to saves a record for each day.
It has all the above headings

Is there a macro that will copy the record to the database and have it
included in the database range?

oldjay

Maybe this page will help
http://www.rondebruin.nl/copy1.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Oldjay" <(E-Mail Removed)> wrote in message news:B2C01821-8061-4328-A861-(E-Mail Removed)...
>I have an input sheet that allows a user to fill in various time information
> In this same sheet I have a row that has all the headings I want to to save
> in a database. In the next row I have the formulas that references the input
> cells
>
> I have another sheet that I want to saves a record for each day.
> It has all the above headings
>
> Is there a macro that will copy the record to the database and have it
> included in the database range?
>
> oldjay
>
>

Thanks Helped alot

"Oldjay" wrote:

> I have an input sheet that allows a user to fill in various time information
> In this same sheet I have a row that has all the headings I want to to save
> in a database. In the next row I have the formulas that references the input
> cells
>
> I have another sheet that I want to saves a record for each day.
> It has all the above headings
>
> Is there a macro that will copy the record to the database and have it
> included in the database range?
>
> oldjay
>
>

I spoke too soon - Here is what I have got .I get a compile error on "Last row"
Sub or Function not defined
Ron ???

Sub copy_6_Values_PasteSpecial()

Dim destrange As Range

Range("AC151:AO151").Copy

If Selection.Areas.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
Set destrange = Sheets("Records").Range("PT_Data" &
LastRow(Sheets("Records")) + 1)
Selection.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub



"Oldjay" wrote:

> Thanks Helped alot
>
> "Oldjay" wrote:
>
> > I have an input sheet that allows a user to fill in various time information
> > In this same sheet I have a row that has all the headings I want to to save
> > in a database. In the next row I have the formulas that references the input
> > cells
> >
> > I have another sheet that I want to saves a record for each day.
> > It has all the above headings
> >
> > Is there a macro that will copy the record to the database and have it
> > included in the database range?
> >
> > oldjay
> >
> >

Hi Oldjay

Copy the LastRow function from my site also in the module

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Oldjay" <(E-Mail Removed)> wrote in message news:F6B2DC9A-B777-4C6A-9202-(E-Mail Removed)...
>I spoke too soon - Here is what I have got .I get a compile error on "Last row"
> Sub or Function not defined
> Ron ???
>
> Sub copy_6_Values_PasteSpecial()
>
> Dim destrange As Range
>
> Range("AC151:AO151").Copy
>
> If Selection.Areas.Count > 1 Then Exit Sub
>
> Application.ScreenUpdating = False
> Set destrange = Sheets("Records").Range("PT_Data" &
> LastRow(Sheets("Records")) + 1)
> Selection.Copy
> destrange.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
> Application.ScreenUpdating = True
> End Sub
>
>
>
> "Oldjay" wrote:
>
>> Thanks Helped alot
>>
>> "Oldjay" wrote:
>>
>> > I have an input sheet that allows a user to fill in various time information
>> > In this same sheet I have a row that has all the headings I want to to save
>> > in a database. In the next row I have the formulas that references the input
>> > cells
>> >
>> > I have another sheet that I want to saves a record for each day.
>> > It has all the above headings
>> >
>> > Is there a macro that will copy the record to the database and have it
>> > included in the database range?
>> >
>> > oldjay
>> >
>> >

This is wrong

> Set destrange = Sheets("Records").Range("PT_Data" &
> LastRow(Sheets("Records")) + 1)

Use it like this to copy in the first empty row in Column A

Set destrange = Sheets("Records").Range("A" & _
LastRow(Sheets("Records")) + 1)

See
http://www.rondebruin.nl/copy1.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Oldjay" <(E-Mail Removed)> wrote in message news:F6B2DC9A-B777-4C6A-9202-(E-Mail Removed)...
>I spoke too soon - Here is what I have got .I get a compile error on "Last row"
> Sub or Function not defined
> Ron ???
>
> Sub copy_6_Values_PasteSpecial()
>
> Dim destrange As Range
>
> Range("AC151:AO151").Copy
>
> If Selection.Areas.Count > 1 Then Exit Sub
>
> Application.ScreenUpdating = False
> Set destrange = Sheets("Records").Range("PT_Data" &
> LastRow(Sheets("Records")) + 1)
> Selection.Copy
> destrange.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
> Application.ScreenUpdating = True
> End Sub
>
>
>
> "Oldjay" wrote:
>
>> Thanks Helped alot
>>
>> "Oldjay" wrote:
>>
>> > I have an input sheet that allows a user to fill in various time information
>> > In this same sheet I have a row that has all the headings I want to to save
>> > in a database. In the next row I have the formulas that references the input
>> > cells
>> >
>> > I have another sheet that I want to saves a record for each day.
>> > It has all the above headings
>> >
>> > Is there a macro that will copy the record to the database and have it
>> > included in the database range?
>> >
>> > oldjay
>> >
>> >

Sorry to be so dumb but I can't find the LastRow function on your site

"Ron de Bruin" wrote:

> This is wrong
>
> > Set destrange = Sheets("Records").Range("PT_Data" &
> > LastRow(Sheets("Records")) + 1)
>
> Use it like this to copy in the first empty row in Column A
>
> Set destrange = Sheets("Records").Range("A" & _
> LastRow(Sheets("Records")) + 1)
>
> See
> http://www.rondebruin.nl/copy1.htm
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Oldjay" <(E-Mail Removed)> wrote in message news:F6B2DC9A-B777-4C6A-9202-(E-Mail Removed)...
> >I spoke too soon - Here is what I have got .I get a compile error on "Last row"
> > Sub or Function not defined
> > Ron ???
> >
> > Sub copy_6_Values_PasteSpecial()
> >
> > Dim destrange As Range
> >
> > Range("AC151:AO151").Copy
> >
> > If Selection.Areas.Count > 1 Then Exit Sub
> >
> > Application.ScreenUpdating = False
> > Set destrange = Sheets("Records").Range("PT_Data" &
> > LastRow(Sheets("Records")) + 1)
> > Selection.Copy
> > destrange.PasteSpecial xlPasteValues, , False, False
> > Application.CutCopyMode = False
> > Application.ScreenUpdating = True
> > End Sub
> >
> >
> >
> > "Oldjay" wrote:
> >
> >> Thanks Helped alot
> >>
> >> "Oldjay" wrote:
> >>
> >> > I have an input sheet that allows a user to fill in various time information
> >> > In this same sheet I have a row that has all the headings I want to to save
> >> > in a database. In the next row I have the formulas that references the input
> >> > cells
> >> >
> >> > I have another sheet that I want to saves a record for each day.
> >> > It has all the above headings
> >> >
> >> > Is there a macro that will copy the record to the database and have it
> >> > included in the database range?
> >> >
> >> > oldjay
> >> >
> >> >
>
>
>

On top of the page you can read this


The Example macro's use one of the functions below, the macro's will not work if you
don't copy them in the module also.

The functions needed for :
1: Finding the last row with data
2: Finding the last column with data

Where do I place the macro's and the functions?
1. Alt-F11
2. Insert>Module from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA
'*********************************************************
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
'*********************************************************

You can also check one row or column to find the last cell with a value.


Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
This will give you the last row with data in Column A + 1 Row

Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset(0, 1).Column
This will give you the last column with data in Row 1 + 1 column

Be aware that if you copy a range with also empty cells in it, It is possible that
the next time you copy to Sheets("Sheet2")some lines will be overwritten.
Use the Functions to avoid this kind of problems.



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Oldjay" <(E-Mail Removed)> wrote in message news:6082A430-58E2-4CE7-9BE2-(E-Mail Removed)...
> Sorry to be so dumb but I can't find the LastRow function on your site
>
> "Ron de Bruin" wrote:
>
>> This is wrong
>>
>> > Set destrange = Sheets("Records").Range("PT_Data" &
>> > LastRow(Sheets("Records")) + 1)
>>
>> Use it like this to copy in the first empty row in Column A
>>
>> Set destrange = Sheets("Records").Range("A" & _
>> LastRow(Sheets("Records")) + 1)
>>
>> See
>> http://www.rondebruin.nl/copy1.htm
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Oldjay" <(E-Mail Removed)> wrote in message news:F6B2DC9A-B777-4C6A-9202-(E-Mail Removed)...
>> >I spoke too soon - Here is what I have got .I get a compile error on "Last row"
>> > Sub or Function not defined
>> > Ron ???
>> >
>> > Sub copy_6_Values_PasteSpecial()
>> >
>> > Dim destrange As Range
>> >
>> > Range("AC151:AO151").Copy
>> >
>> > If Selection.Areas.Count > 1 Then Exit Sub
>> >
>> > Application.ScreenUpdating = False
>> > Set destrange = Sheets("Records").Range("PT_Data" &
>> > LastRow(Sheets("Records")) + 1)
>> > Selection.Copy
>> > destrange.PasteSpecial xlPasteValues, , False, False
>> > Application.CutCopyMode = False
>> > Application.ScreenUpdating = True
>> > End Sub
>> >
>> >
>> >
>> > "Oldjay" wrote:
>> >
>> >> Thanks Helped alot
>> >>
>> >> "Oldjay" wrote:
>> >>
>> >> > I have an input sheet that allows a user to fill in various time information
>> >> > In this same sheet I have a row that has all the headings I want to to save
>> >> > in a database. In the next row I have the formulas that references the input
>> >> > cells
>> >> >
>> >> > I have another sheet that I want to saves a record for each day.
>> >> > It has all the above headings
>> >> >
>> >> > Is there a macro that will copy the record to the database and have it
>> >> > included in the database range?
>> >> >
>> >> > oldjay
>> >> >
>> >> >
>>
>>
>>

Ron When I run this macro it goes all the way thru

sourceRange.CopySet destrange = Sheets("Records").Range("A" & _
LastRow(Sheets("Records")) + 1)

and then it runs the LastRow function until "End Function" then it gives the
error

"Run-time error '438'
Object doesn't support this property or method

Can you help me?
oldjay

Function LastRow(sh As Worksheet)

"Ron de Bruin" wrote:

> On top of the page you can read this
>
>
> The Example macro's use one of the functions below, the macro's will not work if you
> don't copy them in the module also.
>
> The functions needed for :
> 1: Finding the last row with data
> 2: Finding the last column with data
>
> Where do I place the macro's and the functions?
> 1. Alt-F11
> 2. Insert>Module from the Menubar
> 3. Paste the Code there
> 4. Alt-Q to go back to Excel
> 5. Alt-F8 to run the subs
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> See David McRitchie's site if you just started with VBA
> '*********************************************************
> Function LastRow(sh As Worksheet)
> On Error Resume Next
> LastRow = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
>
> Function Lastcol(sh As Worksheet)
> On Error Resume Next
> Lastcol = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
> '*********************************************************
>
> You can also check one row or column to find the last cell with a value.
>
>
> Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> This will give you the last row with data in Column A + 1 Row
>
> Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset(0, 1).Column
> This will give you the last column with data in Row 1 + 1 column
>
> Be aware that if you copy a range with also empty cells in it, It is possible that
> the next time you copy to Sheets("Sheet2")some lines will be overwritten.
> Use the Functions to avoid this kind of problems.
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Oldjay" <(E-Mail Removed)> wrote in message news:6082A430-58E2-4CE7-9BE2-(E-Mail Removed)...
> > Sorry to be so dumb but I can't find the LastRow function on your site
> >
> > "Ron de Bruin" wrote:
> >
> >> This is wrong
> >>
> >> > Set destrange = Sheets("Records").Range("PT_Data" &
> >> > LastRow(Sheets("Records")) + 1)
> >>
> >> Use it like this to copy in the first empty row in Column A
> >>
> >> Set destrange = Sheets("Records").Range("A" & _
> >> LastRow(Sheets("Records")) + 1)
> >>
> >> See
> >> http://www.rondebruin.nl/copy1.htm
> >>
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Oldjay" <(E-Mail Removed)> wrote in message news:F6B2DC9A-B777-4C6A-9202-(E-Mail Removed)...
> >> >I spoke too soon - Here is what I have got .I get a compile error on "Last row"
> >> > Sub or Function not defined
> >> > Ron ???
> >> >
> >> > Sub copy_6_Values_PasteSpecial()
> >> >
> >> > Dim destrange As Range
> >> >
> >> > Range("AC151:AO151").Copy
> >> >
> >> > If Selection.Areas.Count > 1 Then Exit Sub
> >> >
> >> > Application.ScreenUpdating = False
> >> > Set destrange = Sheets("Records").Range("PT_Data" &
> >> > LastRow(Sheets("Records")) + 1)
> >> > Selection.Copy
> >> > destrange.PasteSpecial xlPasteValues, , False, False
> >> > Application.CutCopyMode = False
> >> > Application.ScreenUpdating = True
> >> > End Sub
> >> >
> >> >
> >> >
> >> > "Oldjay" wrote:
> >> >
> >> >> Thanks Helped alot
> >> >>
> >> >> "Oldjay" wrote:
> >> >>
> >> >> > I have an input sheet that allows a user to fill in various time information
> >> >> > In this same sheet I have a row that has all the headings I want to to save
> >> >> > in a database. In the next row I have the formulas that references the input
> >> >> > cells
> >> >> >
> >> >> > I have another sheet that I want to saves a record for each day.
> >> >> > It has all the above headings
> >> >> >
> >> >> > Is there a macro that will copy the record to the database and have it
> >> >> > included in the database range?
> >> >> >
> >> >> > oldjay
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>
>

I used the following Macro to transfer the data
Thanks

Sub Transfer_Records()
'
Application.Goto Reference:="Export_Data"
Selection.Copy
Sheets("Records").Select
Range("A15").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

Selection.PasteSpecial Paste:=xlValues

End Sub

"Oldjay" wrote:

> Ron When I run this macro it goes all the way thru
>
> sourceRange.CopySet destrange = Sheets("Records").Range("A" & _
> LastRow(Sheets("Records")) + 1)
>
> and then it runs the LastRow function until "End Function" then it gives the
> error
>
> "Run-time error '438'
> Object doesn't support this property or method
>
> Can you help me?
> oldjay
>
> Function LastRow(sh As Worksheet)
>
> "Ron de Bruin" wrote:
>
> > On top of the page you can read this
> >
> >
> > The Example macro's use one of the functions below, the macro's will not work if you
> > don't copy them in the module also.
> >
> > The functions needed for :
> > 1: Finding the last row with data
> > 2: Finding the last column with data
> >
> > Where do I place the macro's and the functions?
> > 1. Alt-F11
> > 2. Insert>Module from the Menubar
> > 3. Paste the Code there
> > 4. Alt-Q to go back to Excel
> > 5. Alt-F8 to run the subs
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > See David McRitchie's site if you just started with VBA
> > '*********************************************************
> > Function LastRow(sh As Worksheet)
> > On Error Resume Next
> > LastRow = sh.Cells.Find(What:="*", _
> > After:=sh.Range("A1"), _
> > Lookat:=xlPart, _
> > LookIn:=xlFormulas, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlPrevious, _
> > MatchCase:=False).Row
> > On Error GoTo 0
> > End Function
> >
> > Function Lastcol(sh As Worksheet)
> > On Error Resume Next
> > Lastcol = sh.Cells.Find(What:="*", _
> > After:=sh.Range("A1"), _
> > Lookat:=xlPart, _
> > LookIn:=xlFormulas, _
> > SearchOrder:=xlByColumns, _
> > SearchDirection:=xlPrevious, _
> > MatchCase:=False).Column
> > On Error GoTo 0
> > End Function
> > '*********************************************************
> >
> > You can also check one row or column to find the last cell with a value.
> >
> >
> > Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> > This will give you the last row with data in Column A + 1 Row
> >
> > Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset(0, 1).Column
> > This will give you the last column with data in Row 1 + 1 column
> >
> > Be aware that if you copy a range with also empty cells in it, It is possible that
> > the next time you copy to Sheets("Sheet2")some lines will be overwritten.
> > Use the Functions to avoid this kind of problems.
> >
> >
> >
> > --
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> >
> > "Oldjay" <(E-Mail Removed)> wrote in message news:6082A430-58E2-4CE7-9BE2-(E-Mail Removed)...
> > > Sorry to be so dumb but I can't find the LastRow function on your site
> > >
> > > "Ron de Bruin" wrote:
> > >
> > >> This is wrong
> > >>
> > >> > Set destrange = Sheets("Records").Range("PT_Data" &
> > >> > LastRow(Sheets("Records")) + 1)
> > >>
> > >> Use it like this to copy in the first empty row in Column A
> > >>
> > >> Set destrange = Sheets("Records").Range("A" & _
> > >> LastRow(Sheets("Records")) + 1)
> > >>
> > >> See
> > >> http://www.rondebruin.nl/copy1.htm
> > >>
> > >>
> > >>
> > >> --
> > >> Regards Ron de Bruin
> > >> http://www.rondebruin.nl
> > >>
> > >>
> > >>
> > >> "Oldjay" <(E-Mail Removed)> wrote in message news:F6B2DC9A-B777-4C6A-9202-(E-Mail Removed)...
> > >> >I spoke too soon - Here is what I have got .I get a compile error on "Last row"
> > >> > Sub or Function not defined
> > >> > Ron ???
> > >> >
> > >> > Sub copy_6_Values_PasteSpecial()
> > >> >
> > >> > Dim destrange As Range
> > >> >
> > >> > Range("AC151:AO151").Copy
> > >> >
> > >> > If Selection.Areas.Count > 1 Then Exit Sub
> > >> >
> > >> > Application.ScreenUpdating = False
> > >> > Set destrange = Sheets("Records").Range("PT_Data" &
> > >> > LastRow(Sheets("Records")) + 1)
> > >> > Selection.Copy
> > >> > destrange.PasteSpecial xlPasteValues, , False, False
> > >> > Application.CutCopyMode = False
> > >> > Application.ScreenUpdating = True
> > >> > End Sub
> > >> >
> > >> >
> > >> >
> > >> > "Oldjay" wrote:
> > >> >
> > >> >> Thanks Helped alot
> > >> >>
> > >> >> "Oldjay" wrote:
> > >> >>
> > >> >> > I have an input sheet that allows a user to fill in various time information
> > >> >> > In this same sheet I have a row that has all the headings I want to to save
> > >> >> > in a database. In the next row I have the formulas that references the input
> > >> >> > cells
> > >> >> >
> > >> >> > I have another sheet that I want to saves a record for each day.
> > >> >> > It has all the above headings
> > >> >> >
> > >> >> > Is there a macro that will copy the record to the database and have it
> > >> >> > included in the database range?
> > >> >> >
> > >> >> > oldjay
> > >> >> >
> > >> >> >
> > >>
> > >>
> > >>
> >
> >
> >