I've scanned the posts and I can't seem to find what I'm looking for, many of
the posts talks about numerous workbooks, I'm just trying to query Sheet 1
and make mini reports on the subsequent sheets.

For instance if I want to copy and paste all rows with "Red" in Column A and
"Blue" in column B from sheet 1 titled "Books" to sheet 2 titled "Tapes", is
there simple code that will accompish that with an existing macro? I would
run the macro from sheet 1 each day.

Thanks

One way to do this in code (assuming I understood your post correctly) would
be something along the lines of:

Sub CopyRedBlue()
'Copies entire rows from Books sheet to Tapes sheet only where cell in
column A contains the value "Red" and cell in column B contains the value
"Blue"
Application.ScreenUpdating = False
Sheets("Books").Select
Range("A1").Select
Sheets("Tapes").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then
ActiveCell.EntireRow.Copy
Sheets("Tapes").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Books").Select
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

Another possibility might be to just apply an Auto Filter on your "Books"
sheet and then just filter column A to see only "Red" items and column B to
see only "B" items. No code required on that approach.

"SITCFanTN" wrote:

I've scanned the posts and I can't seem to find what I'm looking for, many of
the posts talks about numerous workbooks, I'm just trying to query Sheet 1
and make mini reports on the subsequent sheets.

For instance if I want to copy and paste all rows with "Red" in Column A and
"Blue" in column B from sheet 1 titled "Books" to sheet 2 titled "Tapes", is
there simple code that will accompish that with an existing macro? I would
run the macro from sheet 1 each day.

Thanks

Hi Paul,

I see what you are saying with your code and since I'm a beginner, I
appreciate that. I've got an error in this part of the code, do I need to do
anything special as far as spacing or anything? Thanks so much!

If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then


"Paul Mathews" wrote:

One way to do this in code (assuming I understood your post correctly) would
be something along the lines of:

Sub CopyRedBlue()
'Copies entire rows from Books sheet to Tapes sheet only where cell in
column A contains the value "Red" and cell in column B contains the value
"Blue"
Application.ScreenUpdating = False
Sheets("Books").Select
Range("A1").Select
Sheets("Tapes").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then
ActiveCell.EntireRow.Copy
Sheets("Tapes").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Books").Select
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

Another possibility might be to just apply an Auto Filter on your "Books"
sheet and then just filter column A to see only "Red" items and column B to
see only "B" items. No code required on that approach.

"SITCFanTN" wrote:

I've scanned the posts and I can't seem to find what I'm looking for, many of
the posts talks about numerous workbooks, I'm just trying to query Sheet 1
and make mini reports on the subsequent sheets.

For instance if I want to copy and paste all rows with "Red" in Column A and
"Blue" in column B from sheet 1 titled "Books" to sheet 2 titled "Tapes", is
there simple code that will accompish that with an existing macro? I would
run the macro from sheet 1 each day.

Thanks

Hmmm. What was the error message? I used the UCase function to ensure that
if the value in a cell was "red" or "Red" or "ReD", they'd all evaluate to
"RED" when upper-cased. Try removing the VBA.UCase from each item and see
what happens.

"SITCFanTN" wrote:

Hi Paul,

I see what you are saying with your code and since I'm a beginner, I
appreciate that. I've got an error in this part of the code, do I need to do
anything special as far as spacing or anything? Thanks so much!

If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then


"Paul Mathews" wrote:

One way to do this in code (assuming I understood your post correctly) would
be something along the lines of:

Sub CopyRedBlue()
'Copies entire rows from Books sheet to Tapes sheet only where cell in
column A contains the value "Red" and cell in column B contains the value
"Blue"
Application.ScreenUpdating = False
Sheets("Books").Select
Range("A1").Select
Sheets("Tapes").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then
ActiveCell.EntireRow.Copy
Sheets("Tapes").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Books").Select
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

Another possibility might be to just apply an Auto Filter on your "Books"
sheet and then just filter column A to see only "Red" items and column B to
see only "B" items. No code required on that approach.

"SITCFanTN" wrote:

I've scanned the posts and I can't seem to find what I'm looking for, many of
the posts talks about numerous workbooks, I'm just trying to query Sheet 1
and make mini reports on the subsequent sheets.

For instance if I want to copy and paste all rows with "Red" in Column A and
"Blue" in column B from sheet 1 titled "Books" to sheet 2 titled "Tapes", is
there simple code that will accompish that with an existing macro? I would
run the macro from sheet 1 each day.

Thanks

Also, please switch the selection of Books and Tapes sheets (I had them
reversed):

...Sheets("Tapes").Select
Range("A1").Select
Sheets("Books").Select
Range("A1").Select....


"SITCFanTN" wrote:

Hi Paul,

I see what you are saying with your code and since I'm a beginner, I
appreciate that. I've got an error in this part of the code, do I need to do
anything special as far as spacing or anything? Thanks so much!

If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then


"Paul Mathews" wrote:

One way to do this in code (assuming I understood your post correctly) would
be something along the lines of:

Sub CopyRedBlue()
'Copies entire rows from Books sheet to Tapes sheet only where cell in
column A contains the value "Red" and cell in column B contains the value
"Blue"
Application.ScreenUpdating = False
Sheets("Books").Select
Range("A1").Select
Sheets("Tapes").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then
ActiveCell.EntireRow.Copy
Sheets("Tapes").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Books").Select
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

Another possibility might be to just apply an Auto Filter on your "Books"
sheet and then just filter column A to see only "Red" items and column B to
see only "B" items. No code required on that approach.

"SITCFanTN" wrote:

I've scanned the posts and I can't seem to find what I'm looking for, many of
the posts talks about numerous workbooks, I'm just trying to query Sheet 1
and make mini reports on the subsequent sheets.

For instance if I want to copy and paste all rows with "Red" in Column A and
"Blue" in column B from sheet 1 titled "Books" to sheet 2 titled "Tapes", is
there simple code that will accompish that with an existing macro? I would
run the macro from sheet 1 each day.

Thanks

Man, I just can't understand this, there is no error in my code now but all I
do when I run the macro is jump to the page I'm suppose to be pasting to.
I've never worked with multi sheet booklets before, do I have to do something
other than click the macro I assigned to this code? I so appreciate your
help Paul.

"Paul Mathews" wrote:

Also, please switch the selection of Books and Tapes sheets (I had them
reversed):

...Sheets("Tapes").Select
Range("A1").Select
Sheets("Books").Select
Range("A1").Select....


"SITCFanTN" wrote:

Hi Paul,

I see what you are saying with your code and since I'm a beginner, I
appreciate that. I've got an error in this part of the code, do I need to do
anything special as far as spacing or anything? Thanks so much!

If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then


"Paul Mathews" wrote:

One way to do this in code (assuming I understood your post correctly) would
be something along the lines of:

Sub CopyRedBlue()
'Copies entire rows from Books sheet to Tapes sheet only where cell in
column A contains the value "Red" and cell in column B contains the value
"Blue"
Application.ScreenUpdating = False
Sheets("Books").Select
Range("A1").Select
Sheets("Tapes").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then
ActiveCell.EntireRow.Copy
Sheets("Tapes").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Books").Select
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

Another possibility might be to just apply an Auto Filter on your "Books"
sheet and then just filter column A to see only "Red" items and column B to
see only "B" items. No code required on that approach.

"SITCFanTN" wrote:

I've scanned the posts and I can't seem to find what I'm looking for, many of
the posts talks about numerous workbooks, I'm just trying to query Sheet 1
and make mini reports on the subsequent sheets.

For instance if I want to copy and paste all rows with "Red" in Column A and
"Blue" in column B from sheet 1 titled "Books" to sheet 2 titled "Tapes", is
there simple code that will accompish that with an existing macro? I would
run the macro from sheet 1 each day.

Thanks

Sub CopyData()
Dim rng As Range, cell As Range
Dim i As Long, sh As Worksheet
With Worksheets("Books")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1
Set sh = Worksheets("Tapes")
For Each cell In rng
If UCase(Trim(cell.Value)) = "RED" And _
UCase(Trim(cell.Offset( _
0, 1).Value)) = "BLUE" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If
Next
End Sub

worked fine for me. If it doesn't work for you, then there could be
problems with your data or I have misunderstood the requirement.

--
Regards,
Tom Ogilvy




"SITCFanTN" wrote in message
...
Man, I just can't understand this, there is no error in my code now but
all I
do when I run the macro is jump to the page I'm suppose to be pasting to.
I've never worked with multi sheet booklets before, do I have to do
something
other than click the macro I assigned to this code? I so appreciate your
help Paul.

"Paul Mathews" wrote:

Also, please switch the selection of Books and Tapes sheets (I had them
reversed):

...Sheets("Tapes").Select
Range("A1").Select
Sheets("Books").Select
Range("A1").Select....


"SITCFanTN" wrote:

Hi Paul,

I see what you are saying with your code and since I'm a beginner, I
appreciate that. I've got an error in this part of the code, do I
need to do
anything special as far as spacing or anything? Thanks so much!

If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then


"Paul Mathews" wrote:

One way to do this in code (assuming I understood your post
correctly) would
be something along the lines of:

Sub CopyRedBlue()
'Copies entire rows from Books sheet to Tapes sheet only where cell
in
column A contains the value "Red" and cell in column B contains the
value
"Blue"
Application.ScreenUpdating = False
Sheets("Books").Select
Range("A1").Select
Sheets("Tapes").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
If VBA.UCase(ActiveCell.Value) = "RED" And
VBA.UCase(ActiveCell.Offset(0, 1).Value) = "BLUE" Then
ActiveCell.EntireRow.Copy
Sheets("Tapes").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Books").Select
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

Another possibility might be to just apply an Auto Filter on your
"Books"
sheet and then just filter column A to see only "Red" items and
column B to
see only "B" items. No code required on that approach.

"SITCFanTN" wrote:

I've scanned the posts and I can't seem to find what I'm looking
for, many of
the posts talks about numerous workbooks, I'm just trying to query
Sheet 1
and make mini reports on the subsequent sheets.

For instance if I want to copy and paste all rows with "Red" in
Column A and
"Blue" in column B from sheet 1 titled "Books" to sheet 2 titled
"Tapes", is
there simple code that will accompish that with an existing macro?
I would
run the macro from sheet 1 each day.

Thanks

hi,

Can anyone help me with this problem?

I have three workbook place in different directories


Workbook A consist of original data
Workbook B consist of old data
Workbook C shall be use to run a macro with inbuilt button.


How to i make workbook C (with marcro) to copy the data from Workbook
to workbook B without opening both A and B?

Can anyone provide me a sample of the code?

Thank yo

--
brucelim8
-----------------------------------------------------------------------
brucelim80's Profile: http://www.excelforum.com/member.php...fo&userid=3224
View this thread: http://www.excelforum.com/showthread.php?threadid=55123

Probably no easy way to do that unless you want to treat both of the closed
workbooks as databases and use ADO or some such.

How hard it is it to use the code to open the workboooks, copy the data,
save the changed workbook, close the workbooks.

--
Regards,
Tom Ogilvy


"brucelim80" wrote
in message ...

hi,

Can anyone help me with this problem?

I have three workbook place in different directories


Workbook A consist of original data
Workbook B consist of old data
Workbook C shall be use to run a macro with inbuilt button.


How to i make workbook C (with marcro) to copy the data from Workbook A
to workbook B without opening both A and B?

Can anyone provide me a sample of the code?

Thank you


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=551238