Posts by Brandtrock
-
-
-
Re: Scenarios
Welcome to OzGrid!
This type of thing can be done in Excel quite nicely. I am attaching a file that Robert Hind created and I have found to be useful for illustrating where to start with something like this.
Hope this helps,
-
Re: J-walk To Swim Atlantic
Do you think he'll change the name of his site to J-Swim and Associates?
-
Re: Last Used Cell
Quote from globalStarI have coded this and it's work?
[VBA]
Sub LastCellAfterUsedInColumn()
Dim Lastrow As Range
Set Lastrow = Range("A3").End(xlDown)
Application.Goto Reference:=Lastrow, Scroll:=True
End Sub
[/vba]You will see the problem with this method if you do this:
Enter some text or numbers in A1:A5
Skip A6
Enter some text or numbers in A7:A10Run your code.
Where are you?
Place the cursor back in A1.
Now run Roy's code on the same sheet.Now where are you?
Roy's method will work correctly as he is going to the last row, first column using the Cells(Rows.Count,1) code. This will go to the bottom of 2003 and 2007 worksheets, even though they have different numbers of rows.
Quote from RoyUKWhy not
[VBA]Sub LastCellAfterUsedInColumn() Cells(Rows.Count,1).End(xlUp).Select End Sub
[/vba]The FIND method listed elsewhere in this thread is also a better solution than the code you posted.
Hope this helps,
-
Re: Congratulations Will Riley On Passing The 8,000 Mark
Given your location, I would guess that this milestone translates loosely to about 4100 euros. In any case, your posts have been valuable to the members here at Oz. In addition to your knowledge of Excel, your expertise with SQL and relational databases is very impressive. Keep up the good work mate.
Oh, and :drink: :drink: :drink:
Later buddy,
-
-
Re: Square Brackets - Vba Notation
You're welcome.
-
Re: Square Brackets - Vba Notation
You may want to read through this post for some additional insight.
Regards,
-
Re: Addin Registry
Regarding changing the extension:
John Walkenback's Power Utility Pak uses .pup extensions on his files. The main add-in then loads these when a given utility is being used.
As usual, Dennis has great input for these type of things.
Regards,
-
Re: Pivot Table That Get Data Dynamically
You're welcome. Always glad to try to help.
Regards,
-
Re: Pivot Table That Get Data Dynamically
Here you go.
I inserted three dynamic named ranges:
ContractsPivotSource
SuppliersPivotSource
DeptPivotSourcethese will expand and contract based on the data entered in the three data sheets (Contracts, Suppliers, Dept).
I made three separate pivot tables, one for each set of data. Admittedly, the layout is horrid. You can change the layout to whatever you desire. Be warned however that deleting the pivot table and recreating it will REQUIRE changing the refresh coding - this is documented by comment in the code as well.
The Contracts and the Suppliers userforms had close buttons so the code to refresh the appropriate pivot table is in the click event of those buttons. The third userform was not as clear as to where to put the refresh code. I will leave that up to you. It will be the same idea as the code for the other two. Just make sure to change the sheet name and the pivot table name.
Regards,
-
Re: Pivot Table That Get Data Dynamically
Tara,
Thanks for attaching the file. Do you want one pivot table encompassing all of the data (hard), one pivot table for each sheet's data (easier), or one table for just the Contracts sheet (easiest)?
Do you want the refresh to happen when the data entry forms are closed using the click button? (I'm guessing you do)
It is quite late here now, but I will be happy to work on this in the morning for you.
Regards,
-
Re: Pivot Table That Get Data Dynamically
Yes, it's possible.
The example I posted earlier has all the pieces we need except the userform.
Do you have a file started? If so, could you attach it (scroll down to manage attachments in the reply window. If not, do you have a layout in mind for the columns and the userform?
Regards,
-
Re: Pivot Table That Get Data Dynamically
Tara01:
I understood what you wanted. My example simply illustrates the creation of the dynamic named range and the programmatic refreshing of the pivot table created from that data.
I assumed that you are using a userform to enter data, that the data is being stored on a spreadsheet, and that that group of data is then used as your pivot table's pivot source. When you add additional data to the group of data you want to see an updated pivot table.
1. Are you creating the userform through VBA?
2. Do you want the data on the same sheet as the pivot table?
3. When do you want the pivot table to "update"?
[INDENT]After each new line of data?[/INDENT]
[INDENT]After all new data is entered?[/INDENT]Regards,
-
Re: Pivot Table That Get Data Dynamically
Here's a file to play with.
The file refreshes the pivot table when the pivot sheet is activated. Add data to the pivot source on the Data sheet and then go back to the Pivot table to watch it refresh itself.
You can code the refresh routine to a userform button instead of in the sheet activate event like it is in this example.
Regards,
-
Re: Pivot Table That Get Data Dynamically
If you get really stuck, post back with a specific question, or better yet, attach a copy of your workbook with any sensitive data removed.
Regards,
-
Re: Pivot Table That Get Data Dynamically
You can do this with some VBA code.
First off, dynamically name the range that you will use as your pivot source.
Next, refresh your pivot table when the data is added.Check out the links in the text above to help you with this.
Regards,
-
Re: Userform Date Calendar
There are a couple of good examples on the site. Try a search on the Hey! That's Cool forum.
-
Re: Importing More Than 65000 Records Into One Worksheet
Quote from nat_m...How can I import data which is either more than 65000 records long or it will sum up to be more than 65000 when imported? I need all my data to be on one worksheet, and I don't care if these data will be imported let's say in columns A B C, then once it reaches the 65K, it will be imported to the neighbouring columns D E F, etc.?
...So, I hope that someone can help me out, because I really would hate to use Access.
I'm not trying to step on toes here, but if your data already resides elsewhere, have you considered what you are going to do with it once it is in Excel?
In other words, are all 65,000+ records necessary? If so, is it necessary to place them on the worksheet? Could they be read each time the worksheet is used?
I'm not proposing any other solution per se, but frequently when the volume of data is this large, only a subset is being worked with. SQL may be an option that would work with your data as is and retrieve only what you really need.
Just stirring the pot,