how to use vba to organise and select data

[comp23]

i'm doing accounts for my company and I have completed the project through using excel. I've been using sum formula as well as others to get the value to match the company in the right month. However I have just realised that I will be continuously adding more data to the spreadsheet and therefore I will have to constantly change the cell ranges every time I get more data. So realistic I would like to create a loop in excel that will keep changing itself when more values are added my understanding is that vba is the best way to do this. However I'm a complete novice at vba and need some help. Therefore I'm going to simplify the data below and see if anyone can help and then I will try and adapted it myself.

Data
Sheet1
Date/cell a1 Description cell b1 Amount cell c1

25/4/2012 company a 10
17/4/2012 company b 25
26/5/2012 company a 31
13/5/2012 company c 17

I want to manipulate this data into this
Sheet2
a1 b1 c1
April May
company a 10 31
company b 25 0
company c 0 17

Now what I want to do is create a code that looks at raw data on sheet1 then takes all the values for company a the month due to the date of the transactions and place in the correct cell in sheet2. I would like to stop all duplicates if possible. but most importantly I would like to create a loop so that when more data is add no hardcoding is needed. I appreciated I have asked a lot here and that I desperately need to learn VBA myself. But i'm in a slight panic and therefore your help with this would be really really appreciated. I'm complete novice with VBA and really need to learn quick thanks.

[GeneralDisarray]

can you post an example sheet? would be easiest way to demo a solution.

[comp23]

it wont let me paste screen shots on the screen currently

[GeneralDisarray]

i mean, you can upload a complete workbook.

--
Sounds like you are new to excel and will probably be hard to point you in the right direction with comments only
1) When you reply, press "go advanced" (look to the lower right, right next to Post Quick Reply)
2) Scroll down the next page to find and click "Manage Attachments",
3) Find and click "add Files",
4) Find and click "select files" (will then let you browse for a file)
5) After you browse for the file find and click "upload file"
6) Then find and click Done.


you will now have an attachment included with your reply. be sure to clean any attachment of private information.

[GeneralDisarray]

looks like you might be looking for the following solution (hard to tell without actually seeing the book, but sounds like you could do this). Your "making things work when i add more data without hardcoding" can be accomplished with a named formula to represent the data table. For example (if your data table has headers in the first row, you could use this):

WHILE IN SHEET ONE (while sheet1 is the active sheet you are viewing)
1) Press cntrl+F3
2) Press New
3) for Name call it myData1, Source leave as workbook, in refers to field type this:

=$A$1:INDEX($1:$1048576,MAX(COUNTA($A$1:$A$1048576),1),MAX(COUNTA($1:$1),1))

THEN ON THE OTHER SHEET

It really sounds like you could use a pivot table to summarize the data as you indicated you would like to do. If that is the case, you can handle this without VBA. Except that it would probably be a good idea to auto-refresh the pivot table with an event macro. You can use the Named Range myData1 as the pivot table source -- when you add data to the sheet1, the named range will include it in the myData1 "table" and the pivot table (when refreshed) will recalculate the summary....

this is pretty simple to do, probably took more time to type this reply than it would to just implement this idea into a example (if you had put up an attachment).

[comp23]

Hi there thanks for your help so far I have attached a document.

A little explanation again. First of all I want to stop duplicates in the raw data. Then what I want to do is take the data
from sheet 1 and take it sheet in a precise way. I want a VBA code that picks up the company name matches against the date
month and then puts the values into the particular cell in sheet 2. so if company standard life did 4 transactions in april I want to total all transactions at put the value into cell f2. I want this to happen for all months a companys and if there is no transactions then leave the value 0 in the cell. then I want to create a loop so that every time I add more data from
the same companys in sheet 1/rawdata sheet 2 adjusts straight. If dones this in excel already but I want to use vba Once
again thanks and sorry as I know this alot of information and work it's really appreciated.

[GeneralDisarray]

As i thought...

Check out the attachment. I implemented the solution outlined above.

**I had to add a couple of columns to the data you gave (to get month / year),

**Sheet2 is your summary table, my best guess of what you were after. You may have to play with the pivot table to get what you want.

Enjoy

--
EDIT
--

The sheet is .xlsm - Macro Enabled workbook. You will need to allow the macros (to get the pivot table to auto-update after changes to the table).

Otherwise you are OK to run it w/o macros. But you would have to right-click-refresh the pivot table each time you changed the data.

[comp23]

thanks for that its really great but to questions how do I look at the vba code you used as I liked to study it plus two is there any way of doing it without the pivot table and just in cells is that possible? thanks again

[GeneralDisarray]

not sure why you would want to do it without the pivot table....but you could. You would however be re-inventing the wheel as Pivot tables are made to do that exact kind of task!

The real trick here is the named formula which provides an effective dynamic table for you to use in the pivot table data selection box. To see that go to the name manager on the formula tab. NOTE: when you click on the refers to field you will see the range the formula is defining...

To view the code go to the visual basic editor and click on the Sheet2 code. There is very little there, below is what is needed. Recall, the VBA asks the pivot table to refresh.

Please Login or Register  to view this content.

[pdoobie]

I have a similar question now posted here