Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in Column A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or BB33
in Column A). Once the range has been selected from Sheet1 the code copies it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I dont have to specify the data that
needs to be in Column A. As a result the code will start in row 5 of Sheet1
and scan every row in Column A for any data. When the last row of data in
Column A is found I would like for the macro to select down to that row and
over to Column C, so I am selecting the range A5:C? (unknown row based on
last row of data in Column A). Once the range has been selected from Sheet1
I would like for the code to copy the range and insert the copied cells into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just dont
want to specify what data I want it to look for. I want it to look for any
data in Column A and then perform the same steps it already does. Can you
help me?

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can you
help me?

Hi thanks for the insight. Perhaps I am making a mistake. VBA is asking me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can you
help me?



.

try:

Set rng = sh.Range("A5:C" & lr)



Sal wrote:

Hi thanks for the insight. Perhaps I am making a mistake. VBA is asking me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?

"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can you
help me?



.


--

Dave Peterson

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA is asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is
found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data
that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data
in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based
on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just
don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can
you
help me?



.

This is cool. Thank you for the help. It works well. I probably didnt
communicate the last part clearly. I want to be able to use the macro to
collect data, so I will be using it more than once. Each time I run the
macro Id like to be able to insert the copied cells into Sheet2 so what was
already copied and pasted into Sheet2 moves down a couple rows in the
worksheet to make space for the new data. In other words each time I run the
macro the existing rows in Sheet2 are shifted down and the new data takes its
place. Does that make sense?

"JLGWhiz" wrote:

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA is asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is
found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data
that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data
in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based
on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just
don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can
you
help me?



.



.

Thanks for the tip. I appreciate your help.

"Dave Peterson" wrote:

try:

Set rng = sh.Range("A5:C" & lr)



Sal wrote:

Hi thanks for the insight. Perhaps I am making a mistake. VBA is asking me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?

"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can you
help me?



.


--

Dave Peterson
.

This should do it:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub



"Sal" wrote:

This is cool. Thank you for the help. It works well. I probably didnt
communicate the last part clearly. I want to be able to use the macro to
collect data, so I will be using it more than once. Each time I run the
macro Id like to be able to insert the copied cells into Sheet2 so what was
already copied and pasted into Sheet2 moves down a couple rows in the
worksheet to make space for the new data. In other words each time I run the
macro the existing rows in Sheet2 are shifted down and the new data takes its
place. Does that make sense?

"JLGWhiz" wrote:

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA is asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is
found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data
that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data
in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based
on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just
don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can
you
help me?



.



.

O darn for some reason its inserting the copied cells into the same worksheet
they were copied from instead of inserting them into Sheet2. I am not sure
how to fix it. Any thoughts.

"JLGWhiz" wrote:

This should do it:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub



"Sal" wrote:

This is cool. Thank you for the help. It works well. I probably didnt
communicate the last part clearly. I want to be able to use the macro to
collect data, so I will be using it more than once. Each time I run the
macro Id like to be able to insert the copied cells into Sheet2 so what was
already copied and pasted into Sheet2 moves down a couple rows in the
worksheet to make space for the new data. In other words each time I run the
macro the existing rows in Sheet2 are shifted down and the new data takes its
place. Does that make sense?

"JLGWhiz" wrote:

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA is asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is
found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the data
that
needs to be in Column A. As a result the code will start in row 5 of
Sheet1
and scan every row in Column A for any data. When the last row of data
in
Column A is found I would like for the macro to select down to that row
and
over to Column C, so I am selecting the range A5:C? (unknown row based
on
last row of data in Column A). Once the range has been selected from
Sheet1
I would like for the code to copy the range and insert the copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just
don't
want to specify what data I want it to look for. I want it to look for
any
data in Column A and then perform the same steps it already does. Can
you
help me?



.



.

Change Sheets(2) in this line:

Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown

To the actual sheet name that you want to copy to. i.e. Sheets("Sheet3") or
whatever it is.

Apparently the sheet you are copying from is the second tab from the left,
which would make it Sheets(2), so use the sheet name instead of the index
number.




"Sal" wrote in message
...
O darn for some reason its inserting the copied cells into the same
worksheet
they were copied from instead of inserting them into Sheet2. I am not
sure
how to fix it. Any thoughts.

"JLGWhiz" wrote:

This should do it:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub



"Sal" wrote:

This is cool. Thank you for the help. It works well. I probably didn't
communicate the last part clearly. I want to be able to use the macro
to
collect data, so I will be using it more than once. Each time I run
the
macro I'd like to be able to insert the copied cells into Sheet2 so
what was
already copied and pasted into Sheet2 moves down a couple rows in the
worksheet to make space for the new data. In other words each time I
run the
macro the existing rows in Sheet2 are shifted down and the new data
takes its
place. Does that make sense?

"JLGWhiz" wrote:

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA is
asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row
in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or
BB33 is
found
the macro will select down to that row and over to Column C, so
I am
selecting the range A5:C?(unknown row based on last row of text
AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the
code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the
data
that
needs to be in Column A. As a result the code will start in row
5 of
Sheet1
and scan every row in Column A for any data. When the last row
of data
in
Column A is found I would like for the macro to select down to
that row
and
over to Column C, so I am selecting the range A5:C? (unknown row
based
on
last row of data in Column A). Once the range has been selected
from
Sheet1
I would like for the code to copy the range and insert the
copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I
just
don't
want to specify what data I want it to look for. I want it to
look for
any
data in Column A and then perform the same steps it already
does. Can
you
help me?



.



.

Hi again. I wanted to thank you again for your help; I appreciate it a lot.
Maybe I made a mistake. I posted the code below that I am using but its
still giving me a Run-time error '9'; subscript out of range error message.
Any thoughts?


Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(Sheet2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub



"JLGWhiz" wrote:

Change Sheets(2) in this line:

Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown

To the actual sheet name that you want to copy to. i.e. Sheets("Sheet3") or
whatever it is.

Apparently the sheet you are copying from is the second tab from the left,
which would make it Sheets(2), so use the sheet name instead of the index
number.




"Sal" wrote in message
...
O darn for some reason its inserting the copied cells into the same
worksheet
they were copied from instead of inserting them into Sheet2. I am not
sure
how to fix it. Any thoughts.

"JLGWhiz" wrote:

This should do it:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub



"Sal" wrote:

This is cool. Thank you for the help. It works well. I probably didn't
communicate the last part clearly. I want to be able to use the macro
to
collect data, so I will be using it more than once. Each time I run
the
macro I'd like to be able to insert the copied cells into Sheet2 so
what was
already copied and pasted into Sheet2 moves down a couple rows in the
worksheet to make space for the new data. In other words each time I
run the
macro the existing rows in Sheet2 are shifted down and the new data
takes its
place. Does that make sense?

"JLGWhiz" wrote:

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA is
asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row
in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or
BB33 is
found
the macro will select down to that row and over to Column C, so
I am
selecting the range A5:C?(unknown row based on last row of text
AA22 or
BB33
in Column A). Once the range has been selected from Sheet1 the
code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify the
data
that
needs to be in Column A. As a result the code will start in row
5 of
Sheet1
and scan every row in Column A for any data. When the last row
of data
in
Column A is found I would like for the macro to select down to
that row
and
over to Column C, so I am selecting the range A5:C? (unknown row
based
on
last row of data in Column A). Once the range has been selected
from
Sheet1
I would like for the code to copy the range and insert the
copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function I
just
don't
want to specify what data I want it to look for. I want it to
look for
any
data in Column A and then perform the same steps it already
does. Can
you
help me?



.



.



.

You just needed some quote marks. Try this:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets("Sheet2").Range("A2").Resize(rng.Rows.Count , 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub




"Sal" wrote in message
...
Hi again. I wanted to thank you again for your help; I appreciate it a
lot.
Maybe I made a mistake. I posted the code below that I am using but its
still giving me a Run-time error '9'; subscript out of range error
message.
Any thoughts?


Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(Sheet2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub



"JLGWhiz" wrote:

Change Sheets(2) in this line:

Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown

To the actual sheet name that you want to copy to. i.e. Sheets("Sheet3")
or
whatever it is.

Apparently the sheet you are copying from is the second tab from the
left,
which would make it Sheets(2), so use the sheet name instead of the index
number.




"Sal" wrote in message
...
O darn for some reason its inserting the copied cells into the same
worksheet
they were copied from instead of inserting them into Sheet2. I am not
sure
how to fix it. Any thoughts.

"JLGWhiz" wrote:

This should do it:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert
Shift:=xlDown
Application.CutCopyMode = False
End Sub



"Sal" wrote:

This is cool. Thank you for the help. It works well. I probably
didn't
communicate the last part clearly. I want to be able to use the
macro
to
collect data, so I will be using it more than once. Each time I run
the
macro I'd like to be able to insert the copied cells into Sheet2 so
what was
already copied and pasted into Sheet2 moves down a couple rows in
the
worksheet to make space for the new data. In other words each time
I
run the
macro the existing rows in Sheet2 are shifted down and the new data
takes its
place. Does that make sense?

"JLGWhiz" wrote:

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA
is
asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1,
After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2,
After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every
row
in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or
BB33 is
found
the macro will select down to that row and over to Column C,
so
I am
selecting the range A5:C?(unknown row based on last row of
text
AA22 or
BB33
in Column A). Once the range has been selected from Sheet1
the
code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify
the
data
that
needs to be in Column A. As a result the code will start in
row
5 of
Sheet1
and scan every row in Column A for any data. When the last
row
of data
in
Column A is found I would like for the macro to select down
to
that row
and
over to Column C, so I am selecting the range A5:C? (unknown
row
based
on
last row of data in Column A). Once the range has been
selected
from
Sheet1
I would like for the code to copy the range and insert the
copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function
I
just
don't
want to specify what data I want it to look for. I want it
to
look for
any
data in Column A and then perform the same steps it already
does. Can
you
help me?



.



.



.

Wow, I feel foolish. Thanks for the help. You were right!

"JLGWhiz" wrote:

You just needed some quote marks. Try this:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets("Sheet2").Range("A2").Resize(rng.Rows.Count , 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub




"Sal" wrote in message
...
Hi again. I wanted to thank you again for your help; I appreciate it a
lot.
Maybe I made a mistake. I posted the code below that I am using but its
still giving me a Run-time error '9'; subscript out of range error
message.
Any thoughts?


Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(Sheet2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub



"JLGWhiz" wrote:

Change Sheets(2) in this line:

Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert Shift:=xlDown

To the actual sheet name that you want to copy to. i.e. Sheets("Sheet3")
or
whatever it is.

Apparently the sheet you are copying from is the second tab from the
left,
which would make it Sheets(2), so use the sheet name instead of the index
number.




"Sal" wrote in message
...
O darn for some reason its inserting the copied cells into the same
worksheet
they were copied from instead of inserting them into Sheet2. I am not
sure
how to fix it. Any thoughts.

"JLGWhiz" wrote:

This should do it:

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A5:C" & lr)
rng.Copy
Sheets(2).Range("A2").Resize(rng.Rows.Count, 3).Insert
Shift:=xlDown
Application.CutCopyMode = False
End Sub



"Sal" wrote:

This is cool. Thank you for the help. It works well. I probably
didn't
communicate the last part clearly. I want to be able to use the
macro
to
collect data, so I will be using it more than once. Each time I run
the
macro I'd like to be able to insert the copied cells into Sheet2 so
what was
already copied and pasted into Sheet2 moves down a couple rows in
the
worksheet to make space for the new data. In other words each time
I
run the
macro the existing rows in Sheet2 are shifted down and the new data
takes its
place. Does that make sense?

"JLGWhiz" wrote:

My error, should be:

Set rng = sh.Range("A5:C" & lr)



"Sal" wrote in message
...
Hi thanks for the insight. Perhaps I am making a mistake. VBA
is
asking
me
to debug this line of code.

rng = sh.Range("A5:C" & lr)

I am getting an error message that says

Run-time error '91':
Object variable or With block variable not set

Any thoughts?


"JLGWhiz" wrote:

Give this a try.

Sub copyStuff()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
rng = sh.Range("A5:C" & lr)
rng.Copy Sheets("Sheet2").Range("A2")
End Sub




"Sal" wrote in message
...
Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1,
After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2,
After:=Sws.Range("A"
&
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every
row
in
Column
A
for the data AA22 or BB33. When the last row of data AA22 or
BB33 is
found
the macro will select down to that row and over to Column C,
so
I am
selecting the range A5:C?(unknown row based on last row of
text
AA22 or
BB33
in Column A). Once the range has been selected from Sheet1
the
code
copies
it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I don't have to specify
the
data
that
needs to be in Column A. As a result the code will start in
row
5 of
Sheet1
and scan every row in Column A for any data. When the last
row
of data
in
Column A is found I would like for the macro to select down
to
that row
and
over to Column C, so I am selecting the range A5:C? (unknown
row
based
on
last row of data in Column A). Once the range has been
selected
from
Sheet1
I would like for the code to copy the range and insert the
copied cells
into
Sheet2 starting in Row2.

In other words the code above is performing the same function
I
just
don't
want to specify what data I want it to look for. I want it
to
look for
any
data in Column A and then perform the same steps it already
does. Can
you
help me?



.



.



.



.