I have created some Macros using the already posted suggestions but I am
having trouble finding one that fits my current need. I am hoping that
someone out here can help me.

I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
couple of tasks. First it would need to perform the copy. The copy would need
to be the values only of the cells. Then the next time that I would need to
copy the data it would need to find the next empty row on the Work Sheet
"Time record." Could someone please help me.

Thank you,
Ted

iRow = 1 + Sheets("Time Record").Range("A65536").End(xlUp)
Sheets("Time Sheet").Range("A11:X26").Copy _
Sheets("Time Record").Range("A" & iRow)

or

iRow = 1 + Sheets("Time Record").Range("A65536").End(xlUp)
Sheets("Time Sheet").Range("A11:X26").Copy
Sheets("Time Record").Range("A" & iRow).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Hth,
Merjet

the below untested pseudocode should get you going:

Sub copydata()
Dim sh1 as Worksheet, sh2 as Worksheet
Set rng1 as range, rng2 as Range
set sh1 = Worksheets("Time Sheet")
set sh2 = Worksheets("Tme Record")
set rng1 = sh1.Range("A11:X26")
set rng2 = GetRealLastCell(sh2)
set rng2 = sh2.cells(rng2.row+1,1)
rng1.copy
rng2.pasteSpecial xlValues
End Sub

Public GetRealLastCell(sh as Worksheet) as Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", Sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function

--
Regards,
Tom Ogilvy


"Theo Degr" wrote:

> I have created some Macros using the already posted suggestions but I am
> having trouble finding one that fits my current need. I am hoping that
> someone out here can help me.
>
> I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
> Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
> couple of tasks. First it would need to perform the copy. The copy would need
> to be the values only of the cells. Then the next time that I would need to
> copy the data it would need to find the next empty row on the Work Sheet
> "Time record." Could someone please help me.
>
> Thank you,
> Ted

You should always post YOUR coding efforts for comments. Sounds like
homework but

Try this idea from anywhere in the workbook withOUT selections of any kind.
Change to suit. Sheet5 is your sourcesheet. change to yours., etc.
Make VERY sure that your source range and copyto range are the SAME size.
a2:a6=lr+5

Sub copyvaluestodest()
With Sheets("yyy") 'destination sheet
lr = .Cells(Rows.Count, "f").End(xlUp).row + 1
..Range(.Cells(lr, "f"), .Cells(lr + 5, "f")).Value = _
Sheets("sheet5").Range("a2:a6").Value
End With
End Sub


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Theo Degr" <Theo (E-Mail Removed)> wrote in message
news:12C23CAA-B04A-496D-9F34-(E-Mail Removed)...
>I have created some Macros using the already posted suggestions but I am
> having trouble finding one that fits my current need. I am hoping that
> someone out here can help me.
>
> I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
> Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
> couple of tasks. First it would need to perform the copy. The copy would
> need
> to be the values only of the cells. Then the next time that I would need
> to
> copy the data it would need to find the next empty row on the Work Sheet
> "Time record." Could someone please help me.
>
> Thank you,
> Ted

Tom,

Thank you. I am getting a Compile Error when entering the following Line:

Public GetRealLastCell(sh as Worksheet) as range

The word "as" is highlighted I am not sure why.


"Tom Ogilvy" wrote:

> the below untested pseudocode should get you going:
>
> Sub copydata()
> Dim sh1 as Worksheet, sh2 as Worksheet
> Set rng1 as range, rng2 as Range
> set sh1 = Worksheets("Time Sheet")
> set sh2 = Worksheets("Tme Record")
> set rng1 = sh1.Range("A11:X26")
> set rng2 = GetRealLastCell(sh2)
> set rng2 = sh2.cells(rng2.row+1,1)
> rng1.copy
> rng2.pasteSpecial xlValues
> End Sub
>
> Public GetRealLastCell(sh as Worksheet) as Range
> Dim RealLastRow As Long
> Dim RealLastColumn As Long
> On Error Resume Next
> RealLastRow = _
> sh.Cells.Find("*", Sh.Range("A1"), , , xlByRows, xlPrevious).Row
> RealLastColumn = _
> sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
> set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
> End Function
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Theo Degr" wrote:
>
> > I have created some Macros using the already posted suggestions but I am
> > having trouble finding one that fits my current need. I am hoping that
> > someone out here can help me.
> >
> > I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
> > Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
> > couple of tasks. First it would need to perform the copy. The copy would need
> > to be the values only of the cells. Then the next time that I would need to
> > copy the data it would need to find the next empty row on the Work Sheet
> > "Time record." Could someone please help me.
> >
> > Thank you,
> > Ted

Public GetRealLastCell(sh as Worksheet) as range

Should be

Public Function GetRealLastCell(sh as Worksheet) as range

--
Regards,
Tom Ogilvy


"Theo Degr" wrote:

> Tom,
>
> Thank you. I am getting a Compile Error when entering the following Line:
>
> Public GetRealLastCell(sh as Worksheet) as range
>
> The word "as" is highlighted I am not sure why.
>
>
> "Tom Ogilvy" wrote:
>
> > the below untested pseudocode should get you going:
> >
> > Sub copydata()
> > Dim sh1 as Worksheet, sh2 as Worksheet
> > Set rng1 as range, rng2 as Range
> > set sh1 = Worksheets("Time Sheet")
> > set sh2 = Worksheets("Tme Record")
> > set rng1 = sh1.Range("A11:X26")
> > set rng2 = GetRealLastCell(sh2)
> > set rng2 = sh2.cells(rng2.row+1,1)
> > rng1.copy
> > rng2.pasteSpecial xlValues
> > End Sub
> >
> > Public GetRealLastCell(sh as Worksheet) as Range
> > Dim RealLastRow As Long
> > Dim RealLastColumn As Long
> > On Error Resume Next
> > RealLastRow = _
> > sh.Cells.Find("*", Sh.Range("A1"), , , xlByRows, xlPrevious).Row
> > RealLastColumn = _
> > sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
> > set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
> > End Function
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Theo Degr" wrote:
> >
> > > I have created some Macros using the already posted suggestions but I am
> > > having trouble finding one that fits my current need. I am hoping that
> > > someone out here can help me.
> > >
> > > I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
> > > Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
> > > couple of tasks. First it would need to perform the copy. The copy would need
> > > to be the values only of the cells. Then the next time that I would need to
> > > copy the data it would need to find the next empty row on the Work Sheet
> > > "Time record." Could someone please help me.
> > >
> > > Thank you,
> > > Ted

Tom,

Thank you. The Macro Runs with that code change. The only problem is that
when I run it again it overwrites the data that was previously copied to
"Time Record." It does not appear that it is searching for an empty Row.

"Tom Ogilvy" wrote:

> Public GetRealLastCell(sh as Worksheet) as range
>
> Should be
>
> Public Function GetRealLastCell(sh as Worksheet) as range
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Theo Degr" wrote:
>
> > Tom,
> >
> > Thank you. I am getting a Compile Error when entering the following Line:
> >
> > Public GetRealLastCell(sh as Worksheet) as range
> >
> > The word "as" is highlighted I am not sure why.
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > the below untested pseudocode should get you going:
> > >
> > > Sub copydata()
> > > Dim sh1 as Worksheet, sh2 as Worksheet
> > > Set rng1 as range, rng2 as Range
> > > set sh1 = Worksheets("Time Sheet")
> > > set sh2 = Worksheets("Tme Record")
> > > set rng1 = sh1.Range("A11:X26")
> > > set rng2 = GetRealLastCell(sh2)
> > > set rng2 = sh2.cells(rng2.row+1,1)
> > > rng1.copy
> > > rng2.pasteSpecial xlValues
> > > End Sub
> > >
> > > Public GetRealLastCell(sh as Worksheet) as Range
> > > Dim RealLastRow As Long
> > > Dim RealLastColumn As Long
> > > On Error Resume Next
> > > RealLastRow = _
> > > sh.Cells.Find("*", Sh.Range("A1"), , , xlByRows, xlPrevious).Row
> > > RealLastColumn = _
> > > sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
> > > set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
> > > End Function
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Theo Degr" wrote:
> > >
> > > > I have created some Macros using the already posted suggestions but I am
> > > > having trouble finding one that fits my current need. I am hoping that
> > > > someone out here can help me.
> > > >
> > > > I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
> > > > Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
> > > > couple of tasks. First it would need to perform the copy. The copy would need
> > > > to be the values only of the cells. Then the next time that I would need to
> > > > copy the data it would need to find the next empty row on the Work Sheet
> > > > "Time record." Could someone please help me.
> > > >
> > > > Thank you,
> > > > Ted

I made a small modification to the function to handle an empty worksheet, but
it worked fine with me in terms of writing the data farther down in the sheet
for subsequent use. (the technique in the GetRealLastCell function is widely
used and is pretty robust). Try this revision and see if it works for you.

Sub copydata()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng1 As Range, rng2 As Range
Set sh1 = Worksheets("Time Sheet")
Set sh2 = Worksheets("Time Record")
Set rng1 = sh1.Range("A11:X26")
Set rng2 = GetRealLastCell(sh2)
Set rng2 = sh2.Cells(rng2.Row + 1, 1)
rng1.Copy
rng2.PasteSpecial xlValues
End Sub

Public Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
If RealLastRow < 1 Then RealLastRow = 1
If RealLastColumn < 1 Then RealLastColumn = 1
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function

--
Regards,
Tom Ogilvy


"Theo Degr" wrote:

> Tom,
>
> Thank you. The Macro Runs with that code change. The only problem is that
> when I run it again it overwrites the data that was previously copied to
> "Time Record." It does not appear that it is searching for an empty Row.
>
> "Tom Ogilvy" wrote:
>
> > Public GetRealLastCell(sh as Worksheet) as range
> >
> > Should be
> >
> > Public Function GetRealLastCell(sh as Worksheet) as range
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Theo Degr" wrote:
> >
> > > Tom,
> > >
> > > Thank you. I am getting a Compile Error when entering the following Line:
> > >
> > > Public GetRealLastCell(sh as Worksheet) as range
> > >
> > > The word "as" is highlighted I am not sure why.
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > the below untested pseudocode should get you going:
> > > >
> > > > Sub copydata()
> > > > Dim sh1 as Worksheet, sh2 as Worksheet
> > > > Set rng1 as range, rng2 as Range
> > > > set sh1 = Worksheets("Time Sheet")
> > > > set sh2 = Worksheets("Tme Record")
> > > > set rng1 = sh1.Range("A11:X26")
> > > > set rng2 = GetRealLastCell(sh2)
> > > > set rng2 = sh2.cells(rng2.row+1,1)
> > > > rng1.copy
> > > > rng2.pasteSpecial xlValues
> > > > End Sub
> > > >
> > > > Public GetRealLastCell(sh as Worksheet) as Range
> > > > Dim RealLastRow As Long
> > > > Dim RealLastColumn As Long
> > > > On Error Resume Next
> > > > RealLastRow = _
> > > > sh.Cells.Find("*", Sh.Range("A1"), , , xlByRows, xlPrevious).Row
> > > > RealLastColumn = _
> > > > sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
> > > > set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
> > > > End Function
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Theo Degr" wrote:
> > > >
> > > > > I have created some Macros using the already posted suggestions but I am
> > > > > having trouble finding one that fits my current need. I am hoping that
> > > > > someone out here can help me.
> > > > >
> > > > > I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
> > > > > Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
> > > > > couple of tasks. First it would need to perform the copy. The copy would need
> > > > > to be the values only of the cells. Then the next time that I would need to
> > > > > copy the data it would need to find the next empty row on the Work Sheet
> > > > > "Time record." Could someone please help me.
> > > > >
> > > > > Thank you,
> > > > > Ted

Don,

Thank you for the code. Your code copies the values in Column "A" but what
would the code be to get the values from "a11:x11?"

Thanks again
Ted

"Don Guillett" wrote:

> You should always post YOUR coding efforts for comments. Sounds like
> homework but
>
> Try this idea from anywhere in the workbook withOUT selections of any kind.
> Change to suit. Sheet5 is your sourcesheet. change to yours., etc.
> Make VERY sure that your source range and copyto range are the SAME size.
> a2:a6=lr+5
>
> Sub copyvaluestodest()
> With Sheets("yyy") 'destination sheet
> lr = .Cells(Rows.Count, "f").End(xlUp).row + 1
> ..Range(.Cells(lr, "f"), .Cells(lr + 5, "f")).Value = _
> Sheets("sheet5").Range("a2:a6").Value
> End With
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Theo Degr" <Theo (E-Mail Removed)> wrote in message
> news:12C23CAA-B04A-496D-9F34-(E-Mail Removed)...
> >I have created some Macros using the already posted suggestions but I am
> > having trouble finding one that fits my current need. I am hoping that
> > someone out here can help me.
> >
> > I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
> > Sheet" to another Work Sheet "Time Record." I need the Macro to per form a
> > couple of tasks. First it would need to perform the copy. The copy would
> > need
> > to be the values only of the cells. Then the next time that I would need
> > to
> > copy the data it would need to find the next empty row on the Work Sheet
> > "Time record." Could someone please help me.
> >
> > Thank you,
> > Ted
>
>

Think about it.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Theo Degr" <(E-Mail Removed)> wrote in message
news:F75133BA-632A-429A-9A28-(E-Mail Removed)...
> Don,
>
> Thank you for the code. Your code copies the values in Column "A" but what
> would the code be to get the values from "a11:x11?"
>
> Thanks again
> Ted
>
> "Don Guillett" wrote:
>
>> You should always post YOUR coding efforts for comments. Sounds like
>> homework but
>>
>> Try this idea from anywhere in the workbook withOUT selections of any
>> kind.
>> Change to suit. Sheet5 is your sourcesheet. change to yours., etc.
>> Make VERY sure that your source range and copyto range are the SAME size.
>> a2:a6=lr+5
>>
>> Sub copyvaluestodest()
>> With Sheets("yyy") 'destination sheet
>> lr = .Cells(Rows.Count, "f").End(xlUp).row + 1
>> ..Range(.Cells(lr, "f"), .Cells(lr + 5, "f")).Value = _
>> Sheets("sheet5").Range("a2:a6").Value
>> End With
>> End Sub
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Theo Degr" <Theo (E-Mail Removed)> wrote in message
>> news:12C23CAA-B04A-496D-9F34-(E-Mail Removed)...
>> >I have created some Macros using the already posted suggestions but I am
>> > having trouble finding one that fits my current need. I am hoping that
>> > someone out here can help me.
>> >
>> > I am trying to copy a Range of Data "a11:x26" from one Work Sheet "Time
>> > Sheet" to another Work Sheet "Time Record." I need the Macro to per
>> > form a
>> > couple of tasks. First it would need to perform the copy. The copy
>> > would
>> > need
>> > to be the values only of the cells. Then the next time that I would
>> > need
>> > to
>> > copy the data it would need to find the next empty row on the Work
>> > Sheet
>> > "Time record." Could someone please help me.
>> >
>> > Thank you,
>> > Ted
>>
>>