Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie to VBA, Can someone help me?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie to VBA, Can someone help me?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie to VBA, Can someone help me?
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, "TCrow2000" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie to VBA, Can someone help me?
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, 1) clear the amounts sitting in column D on Wksht PL 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: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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie to VBA, Can someone help me?
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, "TCrow2000" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Q | Excel Worksheet Functions | |||
Real Newbie newbie question | New Users to Excel | |||
Newbie | Excel Programming | |||
Newbie needs help in VBA | Excel Programming | |||
Newbie help please | Excel Programming |