ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   copy and paste from one sheet to another with VBA (https://www.excelbanter.com/excel-programming/364063-copy-paste-one-sheet-another-vba.html)

SITCFanTN
copy and paste from one sheet to another with VBA
 
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

Paul Mathews
copy and paste from one sheet to another with VBA
 
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

SITCFanTN
copy and paste from one sheet to another with VBA
 
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

Paul Mathews
copy and paste from one sheet to another with VBA
 
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

Paul Mathews
copy and paste from one sheet to another with VBA
 
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

SITCFanTN
copy and paste from one sheet to another with VBA
 
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

Tom Ogilvy
copy and paste from one sheet to another with VBA
 
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

SITCFanTN
copy and paste from one sheet to another with VBA
 
Hi Tom,

Maybe it is where I have this stored and those properties. I've never
worked with multi sheet workbooks before. Should this be in the workbook open
code or attached to a specific sheet? I"m thinking it must be something
like that to cause this not to run.

Thanks again.

Joyce

"Tom Ogilvy" wrote:

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

brucelim80[_14_]
copy and paste from one sheet to another with VBA
 

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

Tom Ogilvy
copy and paste from one sheet to another with VBA
 
you should go to the VBE and do Insert=Module and place it in that module.

Then if you want to run it, you go back to Excel and do

Tools=Macro=Macros, select CopyData and click the run button

If you are expecting to flash and blink all over the screen, then you will
be disappointed. It might shudder for a second and then is probably done.
Look at the Tapes sheet.

The code should not be in the Thisworkbook module or a sheet module.

--
Regards,
Tom Ogilvy



"SITCFanTN" wrote in message
...
Hi Tom,

Maybe it is where I have this stored and those properties. I've never
worked with multi sheet workbooks before. Should this be in the workbook
open
code or attached to a specific sheet? I"m thinking it must be something
like that to cause this not to run.

Thanks again.

Joyce

"Tom Ogilvy" wrote:

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

Tom Ogilvy
copy and paste from one sheet to another with VBA
 
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


All times are GMT +1. The time now is 03:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com