Newbie to VBA, Can someone help me?

[TCrow2000]

In the past we have had this workbook that people have been updating
manually and I want to try and automate it to make it a little more
efficient. I am fair with using functions, but am new to using macros
within VBA. The formatting for both sheets cannot change since there
are others events going on with these sheets.

I have a Worksheet TB that will be used to update Worksheet PL within
the same workbook. I want to create a button that will pull off
information from TB and update PL or add an item if the item does not
already exist on PL. The number of records on TB will vary so I need it
to be versatile. Here is how the two sheets are set up (any blank
columns have information present but nothing pertinent to this
scenario):

TB (info starts on row 6)

A B C D E F G H

152-000 Johnson 100 4000
220-800 Adams 204 1 -25000
314-500 Murray 306 -2150
820-000 Curran 426 35400
431-000 Peters 444 6 14000
980-000 Thomas 222 1 24000


PL (info starts on row 11)

A B C D-G H I
311-000 Carson 0 306
314-500 Murray -3000 306
820-000 Curran 40000 426
646-100 Lori 83000 444 26
total 120000
(formula)

PL is to only contain accounts from TB where the value in Wksht TB Col.
C is greater than 299.

Here is what I am envisioning what the button should do:
1) clear the amounts sitting in column D on Wksht PL
2) Find the new accounts on Wksht TB, open a new row at the bottom of
the list on Wksht PL and copy the info into its appropriate columns on
Wksht TB
3) Then Copy-Paste Special-Value the numbers on Wksht TB in Col. C, D
& H into Wksht PL in Col. H, I & D, respectively.

TIA,
TCrow

 

[Don Guillett]

why not use data>filter>autofilter>copy/paste
record a macro if done often

 

[Guest]

Hi,
The first problem I see is that your P&L does not cotain all of the
accounts, 431-000 Peters and 980-000 Thomas, so you might make sure your P&L
contains all the accounts. Clearing your P&L (complete P&L) is not to hard,
just erase the existing balances. Then you will have to match accounts and
put the new balances in place. There are no headers, so it is hard to see
what you are recording. This looks more like a STB, but since the accounts
are not matched series, maybe sales by Employee?

Thanks,

 

[lcifers]

I'm sure someone would be more than happy to help. You might make that
process smoother by asking for help with specific tasks rather than
asking for folks to study your application and write code for you.

If you're new to VBA, you might experiment with recording macros to do
what you want and modifying them as needed. As you find specific tasks
that stump you, search help and this newsgroup for answers. If you
can't find them, post here and there will be plenty of help for those
kinds of questions.

Also, I highly recommend getting a good Excel VBA book. Power
Programming is my favorite. But the 21 Days and Step By Step books are
pretty good too. Try this to start with:

While using the macro recorder,

Select a sheet named Wksht PL. The code recorded is:
Sheets("Wksht PL").Select

Highlight col D and hit the delete key. The code recorded is:
Columns("D").Select
Selection.ClearContents

Item 3) can be figured out using the macro recorder also. I dont
understand what you want to do in Item 2)

Cheers.

- Luther

 

[Guest]

Hi Again,
Step one go to your Chart of Accounts and make sure your P&L has ever
account, if this is a Subsidiary of some general account, then use the
subsidiary. I would even copy out accounts that are no longer used on the
possibility thay may be used by mistake. This is something you will have to
update each month, so that it is always current.

You will need to capture each "piece" of data to want to carry over from the
TB to the new P&L. Find the account on the P&L and write the new data over.
Frankly you might be better off sending the file to someone, instead of
trying to do it yourself.

Thanks,