Copy a Range of Data to another Work Sheet
 

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

Copy a Range of Data to another Work Sheet
 

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

Copy a Range of Data to another Work Sheet
 

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

Copy a Range of Data to another Work Sheet
 

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

"Theo Degr" <Theo wrote in message
...
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

Copy a Range of Data to another Work Sheet
 

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

Copy a Range of Data to another Work Sheet
 

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

Copy a Range of Data to another Work Sheet
 

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

Copy a Range of Data to another Work Sheet
 

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

Copy a Range of Data to another Work Sheet
 

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

"Theo Degr" <Theo wrote in message
...
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

Copy a Range of Data to another Work Sheet
 

Think about it.

--
Don Guillett
SalesAid Software

"Theo Degr" wrote in message
...
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

"Theo Degr" <Theo wrote in message
...
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

Copy a Range of Data to another Work Sheet
 

Don,

I know you want me to figure this out on my own and I appreciate that
because how else can I learn.

Some Questions

lr what exactly is this doing?

..cells(Rows.Count, "f") this indicates that the copying should be done in
column F

When I change .cells to .Range I get an error.

I am certain that the fix is simple I am just to new at this I guess. I
would like to figure it out will my questions help me in soliving the
problem. If not could you provide me with another clue.

Thanks
Ted



"Don Guillett" wrote:


Think about it.

--
Don Guillett
SalesAid Software

"Theo Degr" wrote in message
...
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

"Theo Degr" <Theo wrote in message
...
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

Copy a Range of Data to another Work Sheet
 

Don,

Patients and Trial and Error pay off. Thank you for making me figure it out
on my own. I just need to recopy the code for each column and it seems to
work.

Thanks Again
Ted

"Theo Degr" wrote:

Don,

I know you want me to figure this out on my own and I appreciate that
because how else can I learn.

Some Questions

lr what exactly is this doing?

.cells(Rows.Count, "f") this indicates that the copying should be done in
column F

When I change .cells to .Range I get an error.

I am certain that the fix is simple I am just to new at this I guess. I
would like to figure it out will my questions help me in soliving the
problem. If not could you provide me with another clue.

Thanks
Ted



"Don Guillett" wrote:


Think about it.

--
Don Guillett
SalesAid Software

"Theo Degr" wrote in message
...
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

"Theo Degr" <Theo wrote in message
...
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

Copy a Range of Data to another Work Sheet
 

I don't think you need to do that. Post your final code and/or send me a
workbook.

--
Don Guillett
SalesAid Software

"Theo Degr" wrote in message
...
Don,

Patients and Trial and Error pay off. Thank you for making me figure it
out
on my own. I just need to recopy the code for each column and it seems to
work.

Thanks Again
Ted

"Theo Degr" wrote:

Don,

I know you want me to figure this out on my own and I appreciate that
because how else can I learn.

Some Questions

lr what exactly is this doing?

.cells(Rows.Count, "f") this indicates that the copying should be done in
column F

When I change .cells to .Range I get an error.

I am certain that the fix is simple I am just to new at this I guess. I
would like to figure it out will my questions help me in soliving the
problem. If not could you provide me with another clue.

Thanks
Ted



"Don Guillett" wrote:


Think about it.

--
Don Guillett
SalesAid Software

"Theo Degr" wrote in message
...
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

"Theo Degr" <Theo wrote in message
...
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

Copy a Range of Data to another Work Sheet
 

Tom,

Thank you for the modification as the code worked. The issue that I am
having now is your Macro copies the information from "Time Sheet," Range
"A11:X26" to "Time Record," Range "A11:x26," but when I run the Macro again
with new information on the "Time Sheet" it overwrites the old information on
the "Time Record" Sheet. Essentially when I run the Macro over and over again
the first copy should reside on "Time Record" Range "A4:X19," then the next
time it is run the second copy should reside on "TIme Record" Range
"A20:x35," and so on.

I like your suggestion the best because it most closely mirrors what I want
to do. It takes a range of data from one sheet 'Time Sheet" and copies
(Values Only) it to another sheet "Time Record." When subsequent runs of the
Macro are made I would like the previously copied information on "Time
Record" to remain and have the new information added to it.

Thanks again for all of your help.

Ted

"Tom Ogilvy" wrote:

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