Automating To Export

Sep 16, 2006

Private Sub Database_Click()
Application. ScreenUpdating = False
masterfile = "S:OfficeMaster File.xls"
Answer = MsgBox("Do You want to export to Final Database?", Buttons:=vbYesNoCancel)
If Answer = vbYes Then
ThisWorkbook.Worksheets(2). Range("q9").Copy
End If
End Sub

for some reason, it doesnt work as it said the masterfile.worksheets is not recognized, but as i go to the immediate thingee, i type in ?masterfile, it give me the exact address, i had been working on this thign for hours, and have no idea what i did wrong.

View 3 Replies


Automating Brackets

Mar 18, 2004

I found a template at it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.

So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.

Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.

View 9 Replies View Related

Automating Graphs

Jun 30, 2006

I'm trying to make alot of graphs(400) and I'm not really sure where to start but here with what I've got so far.

Sub macro2()
Dim x As Long, t As Long, y As Long, z As Long
y = 3
z = 5
x = 4
While x < 1000
With Worksheets("cabernet (2)")
t = Cells(x, 1).Value
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("By:Hz"), PlotBy:=xlRows
ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)"
ActiveWindow.Visible = False
ActiveChart.HasTitle = True
ActiveChart.charttitle.Text = " " & t
Windows("complete Favorite Genes.xls").Activate
y = y + 3
z = z + 3
x = x + 4
End With
End Sub

I don't really know if putting a variable directly into a range works, so far it doesn't.

As you can tell I need a line graph of the range B3:H5, then the next is the those same columns but 3 rows down, so the next graph is at B6:H8. I'd also like to through in a title here, but I may just be playing with fire. The title is located in column A starting at 4 and corrosponds with each graph, so it too goes is increments of 3, i.e., the first title is at 4 the second at 7 the third at 10. Somebody help me out this isn't working very well at all.

View 5 Replies View Related

Automating Xirr

May 7, 2007

I have a very peculiar problem. Each month I have a set of financial products for which I calculate the IRR values using XIRR, as the cash flows are uneven. Imagine this, Product names in ColumnA, the cashflow dates in B, and the cash flows with transaction types in ColumnC and finall D has amounts in negetive and positive. Each month I add new cashflows for each product as rows.

Manually I scroll down to select the range for XIRR (both date and amount)
Offlate this work has gone really high in terms of values, and manually cacluating XIRR takes hell lot of time for all the products (currently 80 odd)

View 9 Replies View Related

Automating Data Processing

Jul 25, 2008

I have a large number of sheets with data in the following general format:

x ..... y ............ z etc

A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value

In seperate sheets, I want the following:

x y1 y2

x z1 z2

And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.

View 11 Replies View Related

Automating The Removal Of Certain Data

May 6, 2009

Just wondering if any one can help me come up with some code to remove some lap time in my document ....

View 14 Replies View Related

Automating Weekly Formula Changes

Dec 11, 2009

The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.

View 5 Replies View Related

Automating File Name Via Inputbox

Jun 11, 2007

I am trying to do is have the code display an input box asking the user to type in the file name, or generate this automatically? to an existing folder in a drive

My master file, imports data from another source (excel file) pastes it in the necessary columns at the moment, however the file is run every Monday so the file name is generic, last week was 10723, this week will become 10724 when the data is run next week,

Can I do what I want to? Via either an inputbox for the file name, or by using something so the file name becomes 107 & Weeknum(today()-3)

View 9 Replies View Related

Automating % Weight Calculation

Mar 16, 2008

I have 1 spreadsheet in which I need to calculate the % weight of some numbers. Columns A to G have data I do not need for the calculation.

Column H has the numbers of whose % weight I need to calculate. The problem is that the no. of rows containing the numbers can change from week to week and I am trying to automate the calculation of the % weights.

So I want try to use column G (as a reference) (like go at the bottom of it so I can know how many rows of numbers there are) and then calculate the % weights.

View 9 Replies View Related

Automating With Internet Explorer

Apr 30, 2008

I am trying to use Excel VBA to conduct a transaction automatically.So far,I managed to get all the required input in.Now,I need to click the "Submit" Button.

I was thinking of using the InternetExlorer.Document.Links(x).Title to search for the link of the "Submit" button and then use the .Navigate method to proceed to the link.

But when I wrote and ran the :


i get the following mesage:

"Run Time Error 98

A property or method cannot include a reference to a private object ,either as an argument or as a return value "

View 9 Replies View Related

Automating Copy And Paste ...

May 16, 2008

I have a spreadsheet which i work on everyday, basically I have a formula which needs to be copied in the second column depending on the range in the first column.

Ex : one worksheet will have A1:A100, the B column should be copied to B1:B100, if the next one has A1:A200 then B column should be copied to B1:B200, how to do this with VBA?

View 9 Replies View Related

Automating And Looping A Web Query

Mar 4, 2009

My spreadsheet is linked to a stock data web page using the little documented .iqy function. I have set cell A1 to the web page ticker symbol, so that whenever this cell value is changed the data in the spreadsheet is automatically updated.

My problem is in several parts:
1. Read TickerSymbol.csv file which is comprised of just ticker symbols,
2. Insert (TickerSymbol.csv) symbol 1 into cell A1,
3. Save the results to a file c:XXXSymbol1.csv,
4. Insert (TickerSymbol.csv) symbol 2,
5. repeat, repeat

View 9 Replies View Related

Automating Fill Down Of Formula

Oct 20, 2006

I have a report which has several tabs of data input which is automated.

There are then several columns of formula which I manually fill down at the minute, is they an easy piece of code to automate this?

The attached example may help, on the tab data source I want to automate the copying down of the formulas in columns D&E.

Can this to be automated by the button on the tab front page?

View 3 Replies View Related

Automating Adding Worksheets

Jan 23, 2007

I have created a workbook consisting of a 'master sheet' (this sheet is a worksheet in itself but also summarizes the totals from the subsequent worksheet) and subsequent work sheets. I wish to be able to add worksheets as needed while having the 'master sheet' still updating as you add sheets.

View 9 Replies View Related

Automating Saving Tabs Used For Billing?

Sep 15, 2012

I've been experimenting with automating saving tabs that we use for billing into a .prn file. It works great on my workstation but when I run it on a coworkers workstation the file gets output to the Windows 7 Documents Library, not the workbook's file path. I want it to Save in the same file path that the workbook is saved in already.

Just to clarify I have many sheet tabs, and these are all saved in the .xlsx file. I was previously clicking one by one and doing save as, other file type, prn... but I've automated that by recording a macro and hacking away at it. specifying the workbooks file path correctly? Here's my code, truncated to just show the name of 2 of the tabs:

' This Macro saves the sheet then exports all tabs as a PRN.


View 2 Replies View Related

Automating Count Of Various Text Values

Mar 11, 2014

I have 300 cells filled with around a dozen different names - i.e. the same names crop up multiple times. What I need is automatically creating a list of all the names that appear, ordered by how many times they appear and with the number of times they appear next to them - i.e. ranking the names by their frequency.

View 3 Replies View Related

Automating Outlook Calendar Invitations

Apr 7, 2014

How to automate outlook 2010 calendar and email integration.

I would like to create a button that sends out calendar invites to a large number of recipients with different specific meeting details (date determined by spreadsheet and other details standard). I'd expect that this would populate a specific shared calendar (not my personal calendar) for managing these appointments. It would be good to avoid duplicate correspondence (e.g. via drop-down list or checkbox).

I've created buttons testing a few codes provided in forums, but have not been able to make these work correctly.

Attached is a simple excerpt of the spreadsheet I'm seeking to automate.

Test example.xlsm‎

View 1 Replies View Related

Data Validation - Automating Process

Feb 12, 2014

I'm currently trying to automate the process of creating Data Validation.


I have Grades and Names List.

" GradesList " Range has values "One, Two, Three, Four "

I'm trying to map Names List to GradesList using INDIRECT().

Debug the function DataValidation() to understand the issue better.

View 7 Replies View Related

Automating Repeated Copy/paste

Nov 23, 2009

I've data in a sheet (case1) which I would like to paste in a new sheet (case2) in the shown format. Is there any easy way of automating this? I tried recording a macro & tried somehow to automate this task but gave up after a few tries?

View 8 Replies View Related

Automating Multiple Cells Using A Stat

Sep 24, 2008

I have a row with 5 numbers, B1:F1.
A1 is my muliplier cell (percentage).

I want all of the above to be variables (no formula in these cells) and controlled by a different cell (G1).

What function would I insert into G1 to be able to control the figures in B1:F1 via A1.

View 9 Replies View Related

Automating Work Task Using Validation?

May 25, 2012

i have a validation list in A5 pointing to F6:F, i need the refmask_no to change according to the validation list in A5:A.

So if i choose from List LEADCASH the refmask_no= '100007995' etc

How can i automate the changing of refmask_no?

Sheet1  ABCDEF4Validation ListCorr _acc_no  Mapping 5LEAD CASH ABNAMROrefmask_no = '100007997'  
Corr_acc_noLead Set6    '100007995'LEAD CASH7    '100020741'LEAD CASH ABNAMRO8    '100020645'LEAD CASH AUDNZNR9 

View 1 Replies View Related

Automating Copy And Pasting Process?

Oct 24, 2012

I was wondering if there was anyway to automate this process. Say take all the files in this folder copy visible cells on a specific tab and paste them into a new worksheet/model we already have built. They shouldn't be consolidated rather pasted below the next.

View 9 Replies View Related

Automating A Macro To Run On A Daily Basis?

Dec 5, 2008

Is there any way of automating a macro to run on a daily basis?

View 9 Replies View Related

Automating Data Import And Calculations

Mar 31, 2009

I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files.

Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.

The text files are here:

View 9 Replies View Related

Code For Automating Template Creation

Aug 8, 2006

I have a census file. All records have a TK number assigned to them. I need to create a new workbook (for each TK number) from a template file and fill in the file with data from all the records belonging to that TK number. I can write this out in English, but I don't know what type of functions/searches/etc. to use to make this work most efficiently.

View 9 Replies View Related

Automating Data Filter Autofilter...

Mar 5, 2007

Automating Data Filter Autofilter. i would like to automate the autofilter (from a form)...the following code works, needs improvement...

myfilter = Range("d2").Value
Selection.AutoFilter Field:=1, Criteria1:=myfilter

View 4 Replies View Related

Account For Two Variables - Automating Percentages Calculations

Apr 25, 2014

What I do at my job is schedule production runs for bakery items. these items consist of many ingredients that go into them (Flour, Sugar, etc.) The products go into the run at certain percentages (20% of the run is flour, 30% is eggs, 15% is Sugar, etc) Many times we will have two or more types of a product, sugar for example. between the different types they can all have a different cost per pound, so what we try to do is use the cheapest stuff first to save money. So for part of this excel sheet what I would like it to do is to automate what percentage I should use the sugar based on cost. the total percentage for the sugar would add up to 100%.

for the second half of this sheet, there is another variable to account for. for this product we also need to hit target specifications so the product will be ok to sell. The example is Protein in this situation. we have a target protein that we need to hit per serving of 24.2 grams. I have the file set up to convert the protein percentage to protein per serving in grams, so that is already done.

my problem with this sheet is I can only seem to do one or the other. I can set it up to use the cheapest product first and add up to 100%, but it won't hit the target protein. Or I can have it set up to hit the target protein, but then I won't hit the 100%, it's be above or below. I need it to account for both variables and then give me the suggested percentages.

So is there a way that I can have it formulate what percentage to use something at to hit a target and equal 100%?

tl;dr I need to account for two variables. hit target protein, and have the total percentage equal 100%

View 1 Replies View Related

Automating Style Settings To Make Skins

Jul 29, 2006

I'm developing a user interface for my spreadsheet that allows users to choose from pre-set skins. The primary mechanism for setting the skins is through a fairly long list of styles applied throughout several sheets. By choosing a skin, a user will start a code that will cross- reference the background color, font characteristics, etc. for that particular skin, and those characteristics will be applied to the pre-set styles. Cool in theory. Problem: The VBA code for modifying the styles doesn't seem to apply consistently. Particularly any code involved in fonts or borders yields an error: "Unable to set the XXXX property of the YYYYY class." This despite it all being modeled on a record macro session output. What setting or element am I missing? I would be most grateful for your expert input. Here's the module that sets the styles based on a table of style names in the Design sheet. I've attempted to keep the myriad of settings clear by naming the ranges by their properties. Each of the lines causing errors are commented out.

Public Sub SetStyles()
'Works from the Style Table in the Design Sheet -- Assigns values based on table contents.
Dim i As Integer
Dim j As Integer
Dim oStyle As Object
Dim sStyle As String
Dim xlVAlign1 As XlVAlign
'Can't delete all existing styles because it deletes all style refs in sheets.
Call UnprotectUs 'Unprotects all sheets so that styles can be modified.
'For Each oStyle In ActiveWorkbook.Styles
' If Not oStyle.Name = "Normal" Then oStyle.Delete
'Add the styles that are named and don't exist.
i = 1
For Each oStyle In Range("StyleName").........................

View 5 Replies View Related

Automating A Macro Range To Hide Rows

Aug 17, 2006

I have a graph in Excel containing the 12 months in column 'A' and data associated with each month in column 'B'. I want to hide rows containing months which have yet to arrive (eg: if we are in August, I want to hide the rows containing September:December). However, I want to change the range of rows that I hide each month (so that when we get to September, I only hide rows containing October:December). My attempts at writing a macro to perform this automatically have failed? This is the script I have written:

fyi - RowStart is the row number associated with January, RowEnd is the row number associated with December and RowNext updates each month and is the row number associated with the next calendar month.

Option Explicit
Dim RowStart As Integer
Dim RowEnd As Integer
Dim RowNext As Integer

Sub Hiderows()
RowStart = sheets("month").Range ("A1")
RowEnd= sheets("month").Range ("A2")
RowNext = sheets("month").Range ("A3")
End Sub

View 2 Replies View Related

Automating Entries In Column Adjacent To Pasted Selection

Jul 17, 2013

The following code inserts the text "ep" into col. F when an entry (formatted text colour green) is made in the corresponding row of col. B:


Private Sub Worksheet_Change(ByVal target As Range)
' Insert "ep" in Type column and set font colour
If target.Column = 2 And target.Font.ColorIndex = 10 Then
With Cells(target.Row, "E")
.Value = "ep"


However, more often than not new entries are made by pasting from another sheet, and usually more than one row. How can the code be modified to count the number of rows in the pasted selection and place "ep" in each corresponding row of col. F (instead of just the top row)?

I've been trying Selection.Rows.Count but keep getting the dreaded error message, "Object doesn't support this property or method".

View 6 Replies View Related

Copyrights 2005-15, All rights reserved