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!!!!

copy data range
 

Ill give it a shot!! Does this allow for a variance of
data between A1 and D10 if it should expand to D20 for
example?? The data is kept between 2 headings...'options'
and 'inclusions'. COuld i use the headings to set the
range?? Depending on the information a user inputs, the
range may increase so the last heading is actually on row
30??

Thank you so much!!!!
-----Original Message-----
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!!!!




.

copy data range
 

Sorry paul i should have made it clear that the data is on 2 worksheets. It should copy from a sheet named quote2 to a sheet named Contract. Can you amend the code to reflect this?

Cheers!!

----- Paulw2k wrote: ----

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 Rang
Dim Rng2 As Rang
Dim lastRow As Lon
Dim Adrs As Strin

'Find number of last row used in Column A/
lastRow = Cells(Rows.Count, 1).End(xlUp).Ro
'Define range to cop
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).Addres
'Make Rng2 point to the cell below thi
Set Rng2 = .Range(Adrs).Offset(1, 0
End Wit

'Now cop
Rng.Copy Rng


End Su

If there are headers in row 1 of copied range which you don't want to copy
chang

set Rng = Range("A2:D" & lastRow



Regard

Pau




"gav meredith" wrote in messag
..
Hi
I would like to record a macro or run code in which data between A1 an
D1
copies and pastes at the end of another sheet. Problem is, this data rang
between A1 and D10 may get larger due to rows being inserted. How would
have this data copy (it has headings) whilst allowing for the data rang
t
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!!!