Stuck and need some help on Update button

[xc405]

I have a flowchart sheet for my Petroleum Engineering project and it's set up so the user can type the data into the flowchart box and then add/update the new well permit to the database sheet. The input ranges on flowchart do not change but the database does, each table in the database sheet begins every 13th cells. i.e
1. New Permit
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14. New permit
etc...

I am having touble trying to update one permit then automaticaly move to next permit and so on. If you need my sheet then let me know how to send it to you and i will. THANKS!

Sub UpdateData_click()
   ActiveWorkbook.Sheets("Database").Range("A799:I799").Value = ActiveWorkbook.Sheets("Flowchart").Range("G25:O25").Value
   ActiveWorkbook.Sheets("Database").Range("A802:I802").Value = ActiveWorkbook.Sheets("Flowchart").Range("G28:O28").Value
   ActiveWorkbook.Sheets("Database").Range("A803:I803").Value = ActiveWorkbook.Sheets("Flowchart").Range("G29:O29").Value
   ActiveWorkbook.Sheets("Database").Range("A804:I804").Value = ActiveWorkbook.Sheets("Flowchart").Range("G30:O30").Value
   ActiveWorkbook.Sheets("Database").Range("A805:I805").Value = ActiveWorkbook.Sheets("Flowchart").Range("G31:O31").Value
   ActiveWorkbook.Sheets("Database").Range("A806:I806").Value = ActiveWorkbook.Sheets("Flowchart").Range("G32:O32").Value
   ActiveWorkbook.Sheets("Database").Range("A808:I808").Value = ActiveWorkbook.Sheets("Flowchart").Range("G34:O34").Value
   ActiveWorkbook.Sheets("Database").Range("A810:I810").Value = ActiveWorkbook.Sheets("Flowchart").Range("G35:O35").Value
   ActiveWorkbook.Sheets("Database").Range("A811:I811").Value = ActiveWorkbook.Sheets("Flowchart").Range("G36:O36").Value
[COLOR="Lime"]'First New Permit Data Added
'Clear input data on flowchart but save data that was updated on database
'Find out how to keep the flowchart input ranges the same, but move down to next table on database[/COLOR]
End Sub

 

[kpark91]

Hi, xc.

1) I think what you need is like a count variable, which counts up after you add a row.
or
2) a variable that obtains the last row of your table everytime.

but I'll just show you the second method due to its ease of implementation
2)

Dim LR&
LR = Sheets("Database").Range("A" & rows.Count).End(xlUp).Row
ActiveWorkbook.Sheets("Database").Range("A" & count &":I" & count).Value = ActiveWorkbook.......

 

[BrianB]

I cannot see exactly what you are trying to do. I suspect that you have not set up your database correctly. Excel uses the term "Database" which is technically incorrect - the correct term is "Table". A database is a *collection* of tables and other objects to manipulate them. If you open a MS Access database and see what is available you will see what I mean.

In Excel the "database" should be a simple table on a worksheet of its own consisting of headings in row 1 and data underneath. You will note that it depends on repetition of common data in the columns. Having set that up it is easy to analyse the data and extract whatever you want using the very powerful functions of Excel. If you look under the "Data" menu in Excel you will see most of them there. Your table can consist of 1 record or millions of records but the methods or any code can be exactly the same.

It is a 3 stage process to extract data to a separate report. This was my main job for many years and I found the best method is to use a Pivot Table as the interim step. So we have DATA TABLE -> PIVOT TABLE -> REPORT. A typical time saving is cutting a 3 hour job down to 30 minutes. There is no need to format the raw data table or the pivot table.

Here is some sample data extracted from a much bigger database using "Album" as filter (I have used the code tag to keep it aligned in the message):-

FileName                     Artist       Album                Genre      Title                     Duration  Date Modified    Size
Don't Miss Your Baby.wma     Count Basie  Legendary Hits Vol2  Big Band   Don't Miss Your Baby      3:15      26/12/2009 17:21 2,317 KB
The Blues I Like To Hear.wma Count Basie  Legendary Hits Vol2  Big Band   The Blues I Like To Hear  3:15      26/12/2009 17:21 2,321 KB
Moten Swing.mp3              Count Basie  Legendary Hits Vol2  Quickstep  Moten Swing               2:25      26/12/2009 17:21 3,400 KB
Shortly George.wma           Count Basie  Legendary Hits Vol2  Big Band   Shortly George            2:51      26/12/2009 17:21 2,032 KB
Louisiana.mp3                Count Basie  Legendary Hits Vol2  Jive       Louisiana                 2:29      26/12/2009 17:21 3,513 KB

 

[xc405]

I'm going to give both of these a shot; thanks for the feed back and I wish it was easier to explain, how do I attach my excel sheet so you two can see exactly what I am trying to do?

 

[xc405]

If I use the pivot table idea and filter by well names, how do I automatically add a new well name to my Pivot table using a VBA button named UpdatePivot_Click(). Right now I have 20 wells in the pivot table but I want them to be able to type in new well data on the flowchart sheet and click the button and have the pivot table now display 21 wells and so on.

 

[BrianB]

You have my code to *add data to the data table* - then refresh the pivot table.

 

[xc405]

I don't know why but the pivot table format is not looking so good. I can add Colums and update data just like I wanted to but the format is awful. It's going to be difficult to explain what the problems are so I'm just going to google pivot table. Thanks for the help to you both; I used the first option on another sheet and both work perfectly, pivot table is better for the amount of data I have, I just want the pivot table to look like a normal table sorted by site name and have ot quit summing up coloums

 

[xc405]

I was wondering if you could help me with the Count variable, when i used the code it cleared all of my data. do i code it like this

Dim LR&
LR = Sheets("Database").Range("A" & rows.Count).End(xlUp).Row
ActiveWorkbook.Sheets("Database").Range("A799" & count &":I" & count).Value = ActiveWorkbook.Sheets("Flowchart").Range("G25:O25").Value

Do i do this for all rows in my code?

 

[xc405]

Still having problems, i though it worked but it is deleting the existing database. My fault for not being to clear but let me try and explain it better, im sure its an easy fix.

On the sheet "FlowChart" the range of cells is G25:P33; which contains data that the user can type in, i want the user to click the update button and it will copy the FlowChart.Range(G25:P33) to the sheet Database.Range(A800:J808). Then the next time the user clicks update it will copy FlowChart.Range(G25:P33) to Database.Range(A810:J818) and so on. Is this more clear?

 

[taurean]

You can use something like:
Untested and there could be a typo so verify it in VBE and use it:

Sheets("FlowChart").Range(G25:P33).Copy
Activesheet.Paste Destination:= Sheets("Database").Range("A" & Rows.Count).End(xlUp)(2)
Application.CutCopyMode = False