Copy Row To Another Workbook If . . .
 

Hello. I am a new user, and I'm thankful for this fantastic resource!
I wonder if anyone could offer some help? I'm trying to learn how t
copy an entire row to a different workbook if that row has a certai
value.

Specifically, I have a checkbook register, and I've added a column i
the register that, if I type something in that cell ("t" for ta
deductible item), then it will copy that entire row (date, chec
number, pay to, amount, etc., etc.) to another workbook. Once th
information is copied to this separate workbook, I'll then have a cop
of all tax-deductible items for the year.

It has to be a separate workbook, because I'll have several budget
(each month). So therefore, I need to copy the information from al
the budgets (as I create and make new each month) into the on
workbook. Can someone help? Thanks in advance

--
Rekani
-----------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...fo&userid=2958
View this thread: http://www.excelforum.com/showthread.php?threadid=49285

Copy Row To Another Workbook If . . .
 

Ron de Bruin's site:
http://www.rondebruin.nl/copy5.htm

should get you started.

--
Regards,
Tom Ogilvy


"Rekanix" wrote in
message ...

Hello. I am a new user, and I'm thankful for this fantastic resource!
I wonder if anyone could offer some help? I'm trying to learn how to
copy an entire row to a different workbook if that row has a certain
value.

Specifically, I have a checkbook register, and I've added a column in
the register that, if I type something in that cell ("t" for tax
deductible item), then it will copy that entire row (date, check
number, pay to, amount, etc., etc.) to another workbook. Once the
information is copied to this separate workbook, I'll then have a copy
of all tax-deductible items for the year.

It has to be a separate workbook, because I'll have several budgets
(each month). So therefore, I need to copy the information from all
the budgets (as I create and make new each month) into the one
workbook. Can someone help? Thanks in advance!


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile:
http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

Thanks! I'm going there now!


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

Here is some code. It looks for t in Column F. It copies the found rows to a
new workbook...

Sub CopyRows()
Dim wks As Worksheet
Dim wbkNew As Workbook
Dim rngFound As Range
Dim rngFirst As Range
Dim rngFoundAll As Range
Dim rngToSearch As Range

Set wks = Sheets("Sheet1") 'Change This
Set rngToSearch = wks.Columns("F") 'Change This
Set rngFound = rngToSearch.Find(What:="t", LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "Sorry Nothing to Move"
Else
Set rngFoundAll = rngFound.EntireRow
Set rngFirst = rngFound
Do
Set rngFoundAll = Union(rngFoundAll, rngFound.EntireRow)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
Set wbkNew = Workbooks.Add
rngFoundAll.Copy wbkNew.Sheets(1).Range("A2")
End If
End Sub

--
HTH...

Jim Thomlinson


"Rekanix" wrote:


Hello. I am a new user, and I'm thankful for this fantastic resource!
I wonder if anyone could offer some help? I'm trying to learn how to
copy an entire row to a different workbook if that row has a certain
value.

Specifically, I have a checkbook register, and I've added a column in
the register that, if I type something in that cell ("t" for tax
deductible item), then it will copy that entire row (date, check
number, pay to, amount, etc., etc.) to another workbook. Once the
information is copied to this separate workbook, I'll then have a copy
of all tax-deductible items for the year.

It has to be a separate workbook, because I'll have several budgets
(each month). So therefore, I need to copy the information from all
the budgets (as I create and make new each month) into the one
workbook. Can someone help? Thanks in advance!


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

Hey, thanks, Jim

You obviously know exactly how to do this! Only, I'm kinda dumb. What
do I do with this code (in other words, where do I paste the code--which
cell?)?

Thanks! And, what does dim mean? Sorry so new and dumb!


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

This code can be place in a new module or it could go right inside of a
sheet. Open the Visual Basic Editor (VBE). Ensure that the Project explorer
is visible(View). Select the project (spreadsheet) that you want to run the
code in. Right click and Select Insert Module. In the project you will now
have a new Folder called modules where you will have a Module called Module1.
Open that module by double clicking. Paste the code that I posted. Go back to
the spreadsheet and select tools macros macros. Run the macro called
CopyRows...

Dim stands for Dimension. A better way to think of it though is declared in
memory. These are variables where info is stored in order to run the code. I
have declared Workbooks, Worksheets and Ranges which are Excel Objects. You
can also declare integers, strings, doubles (decimals), ...
--
HTH...

Jim Thomlinson


"Rekanix" wrote:


Hey, thanks, Jim

You obviously know exactly how to do this! Only, I'm kinda dumb. What
do I do with this code (in other words, where do I paste the code--which
cell?)?

Thanks! And, what does dim mean? Sorry so new and dumb!


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

Maybe you can use EasyFilter to do this
No code then <g
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rekanix" wrote in message
...

Hey, thanks, Jim

You obviously know exactly how to do this! Only, I'm kinda dumb. What
do I do with this code (in other words, where do I paste the code--which
cell?)?

Thanks! And, what does dim mean? Sorry so new and dumb!


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

Hey, Jim

I did what you said (Cool! I'm into a whole new dimension of Excel
here that I didn't know even existed!). I ran the macro and got the
following error: Run time error '9', Subscript out of range. I
clicked "Debug", and it highlights and points out this line in the
code: Set wks = Sheets("Sheet4") 'Change This

I changed the "Sheet 1" to "Sheet 4", because that's what it is in my
budget. I also changed the column from F to E, and I changed the range
from A2 to B3:L150 (I'm assuming this covers all of my table). Is this
correct?

Thanks for your help!
Robert


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

I assume that you are using the Sheet4 which is what you see in the VBE
project explorer. ... Sheet4(MyTabName) Change that lise of code to read

Set wks = Sheet4 'This should fix it.

Changing the column was correct. Leave the last line at A2. This is where
the rows will be pasted A2 through A???
--
HTH...

Jim Thomlinson


"Rekanix" wrote:


Hey, Jim

I did what you said (Cool! I'm into a whole new dimension of Excel
here that I didn't know even existed!). I ran the macro and got the
following error: Run time error '9', Subscript out of range. I
clicked "Debug", and it highlights and points out this line in the
code: Set wks = Sheets("Sheet4") 'Change This

I changed the "Sheet 1" to "Sheet 4", because that's what it is in my
budget. I also changed the column from F to E, and I changed the range
from A2 to B3:L150 (I'm assuming this covers all of my table). Is this
correct?

Thanks for your help!
Robert


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

Hey, Ron

Ok, I'm looking at this Easy Filter thing. Can you tell me exactly how
to set it up? I'll download and see if I can learn more about it.
Thanks.

Robert


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

Ok! I'm getting excited now! Jim and Ron, both of your solutions ar
WORKING! I still have a question for each of you--how can I get it t
paste the rows into the pre-formatted table that I want it to go i
(Deductions.xls)? What's happening is it's putting it into a ne
workbook, and the row and column heights and widths are not the same.
I'm sure there's a way I can tell it to paste into the Deduction
workbook?

Thanks--you guys are AWESOME!!!

--
Rekani
-----------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...fo&userid=2958
View this thread: http://www.excelforum.com/showthread.php?threadid=49285

Copy Row To Another Workbook If . . .
 

I think I just found the answer to my question. Ron, all I have to d
is filter the data without copying to a new workbook. Then, I jus
select and copy the cells that I want, and then paste them into th
workbook I want. It's an additional step, but it'll work. I'll kee
researching, and if you or Jim know of a way that I can copy i
directly into the Deductions workbook, then that'll save that extr
step. Thank you both so very much for your help! You guys are great!

Rober

--
Rekani
-----------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...fo&userid=2958
View this thread: http://www.excelforum.com/showthread.php?threadid=49285

Copy Row To Another Workbook If . . .
 

Change where it says tab name and in the 3rd last line change the path to the
workbook...

Sub CopyRows()
Dim wks As Worksheet
Dim wbkPasteTo As Workbook
Dim rngPasteTo As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngFoundAll As Range
Dim rngToSearch As Range

Set wks = Sheet1 'Change This
Set rngToSearch = wks.Columns("F") 'Change This
Set rngFound = rngToSearch.Find(What:="t", LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "Sorry Nothing to Move"
Else
Set rngFoundAll = rngFound.EntireRow
Set rngFirst = rngFound
Do
Set rngFoundAll = Union(rngFoundAll, rngFound.EntireRow)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
On Error GoTo OpenBook
Set wbkPasteTo = Workbooks("Deductions.xls")
On Error GoTo 0
'Change the Tab Name
Set rngPasteTo = wbkPasteTo.Sheets("TabName").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
rngFoundAll.Copy rngPasteTo
End If
Exit Sub
OpenBook:
Workbooks.Open ("C:\Deductions.xls") 'Change This
Resume
End Sub

--
HTH...

Jim Thomlinson


"Rekanix" wrote:


Ok! I'm getting excited now! Jim and Ron, both of your solutions are
WORKING! I still have a question for each of you--how can I get it to
paste the rows into the pre-formatted table that I want it to go in
(Deductions.xls)? What's happening is it's putting it into a new
workbook, and the row and column heights and widths are not the same.
I'm sure there's a way I can tell it to paste into the Deductions
workbook?

Thanks--you guys are AWESOME!!!!


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857

Copy Row To Another Workbook If . . .
 

There is a manual


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rekanix" wrote in message
...

Hey, Ron

Ok, I'm looking at this Easy Filter thing. Can you tell me exactly how
to set it up? I'll download and see if I can learn more about it.
Thanks.

Robert


--
Rekanix
------------------------------------------------------------------------
Rekanix's Profile: http://www.excelforum.com/member.php...o&userid=29586
View this thread: http://www.excelforum.com/showthread...hreadid=492857