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

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

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

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

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

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

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

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

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

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