Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy data range
Hi,
Assuming you have a nice block of data in mainly/but not always A1 to D10. The following would do it. Sub Test() Dim Rng As Range Dim Rng2 As Range Dim lastRow As Long Dim Adrs As String 'Find number of last row used in Column A/1 lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Define range to copy Set Rng = Range("A1:D" & lastRow) 'Find where "Appendix" is on next sheet. With Sheets("NEXTSHEETNAME") Adrs = .Cells.Find(What:="Appendix", After:=.Range("A1"), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Address 'Make Rng2 point to the cell below this Set Rng2 = .Range(Adrs).Offset(1, 0) End With 'Now copy Rng.Copy Rng2 End Sub If there are headers in row 1 of copied range which you don't want to copy, change set Rng = Range("A2:D" & lastRow) Regards Paul "gav meredith" wrote in message ... Hi, I would like to record a macro or run code in which data between A1 and D10 copies and pastes at the end of another sheet. Problem is, this data range between A1 and D10 may get larger due to rows being inserted. How would i have this data copy (it has headings) whilst allowing for the data range to vary? Could i use the headings to set a range?? The data is to paste under a heading "Appendix" on the following sheet?? Any ideas are appreciated!!!! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to copy only range with data | Excel Discussion (Misc queries) | |||
cannot copy data range reference | Excel Discussion (Misc queries) | |||
Copy Range Data Macro | Excel Discussion (Misc queries) | |||
How do I copy data range of the same name to another tab? | Excel Worksheet Functions | |||
Copy a range of data then E-Mail | Excel Programming |