Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I copy a range of data to another sheet? MrNobody Excel Worksheet Functions 2 February 19th 09 03:38 AM
Copy data from 1 work sheet to another automatically Shazza Excel Discussion (Misc queries) 7 November 13th 07 06:21 PM
copy data from a specific range to another sheet new_to_vba[_3_] Excel Programming 4 January 6th 06 08:04 AM
Use macro to check a range of cells within a row and copy wanted data to new sheet busspeed[_3_] Excel Programming 1 September 29th 04 03:59 PM
Use macro to check a range of cells within a row and copy wanted data to new sheet busspeed[_2_] Excel Programming 0 September 29th 04 03:15 PM


All times are GMT +1. The time now is 08:22 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"