I am trying to create a workbook that calcualtes the sum of selected cells from another workbook. I have quite a few formulas, so i would like to use the fill command to help. I cannot get excel to pick up the pattern so I can use the fill formula. The pattern is as follows.
Sum(n5:n11)
Sum(n12:n18)
Sum(n19:n25)
Sum(n26:n32)
etc.
I have a cell in one worksheet xxx in workbook XXX column F selected
I want to run a macro that will open (if not already open) Worksheet yyy in workbook YYY (in C:/Test/ directory)
And copy values from columns B and C of workbook XXX worksheet xxx (the row determined by the cell selected in the F column) to columns D and G respectivley of workbook YYY worksheet yyy
I'm hoping the data will be pasted to a new row at the bottom of all existing rows on worksheet yyy (xlup?)
If multiple cells in column F worksheet xxx are selected, it would be nice if the data was sent over to yyy in the same fashion, ordered top to bottom all at once...I don't want to have to select each row individually and run the macro
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
I'm currently on Manual Calculation mode. I have formulas in cells A1 to A10 which has not been refreshed. Can I select only range A3 to A6 and recalculate only those cells?
View 5 Replies View RelatedHow do I divide a range (number of rows will vary) into groups for calculation purposes?
I have a spreadsheet with too many dependencies to calculate properly (over 400,000). After I calculate I can delete the empty rows, and then there will only be a few hundred rows left and it will calculate just fine.
I think all I need is a loop that will break the range into groups and calculate the groups separately, but I'm very bad at loops.
This is what I need to break into workable parts.
Sub FilterTrue()
ActiveSheet.Calculate
Range(Range("AC16"), Range("AC16").End(xlDown)).Select
Selection.AutoFilter Field:=1, Criteria1:="TRUE"
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Selection.AutoFilter
End Sub
i have tried making a simple macro that just copies all of the available data from a workbook and pastes it in another. I have looked at information available on the internet and combining data from multiple sources I have reached the following code that does not do what I want:
Code:
Option Explicit
Sub GatherData()
Dim wbCSV As Workbook
Dim wsMstr As Worksheet
Dim FileToOpen As String
Dim fPath As String
Dim fCSV As String
[code]....
I have tried a search on the forums but I could not find anything that I could apply with my limited understanding.
I have two workbooks say 'MASTER' & 'REVISED' in D:Office. The REVISED workbook contains certain rows colored in Yellow which contain the rectified data. How to copy those rows (rectified data rows of workbook REVISED) to the workbook MASTER in the same row number.
View 7 Replies View RelatedI have a workbook with about 25 sheets. All the sheets are named. I'd like either a Macro or some VBA code whcih, when the user clicks a button will Unhide selected sheets, say "Equip Labor", "Equipment", Proj. Summary" and "Implementation Guide" and then copy these sheets into a new workbook created on the fly (Book1.xls) and then in the original workbook re-hide all the selected sheets. This way the user can save the new workbook as whatever name they want.
Is this possible? The closest post I could find was this:
http://www.mrexcel.com/forum/showthr...opy+Worksheets
I tried to modify this, but I'm not that good yet with VBA.
I have a code that will allow me to copy three worksheets from an open workbook to a new workbook. That works great, but I only need only a selected range from each of these worksheets to be copied (Range A1:AV60). Here is the code I use to select and copy the worksheets, how do I add a range within this code for each worksheet.
Sheets(Array("Req Page 1", "Req Ext 1", "Req Ext 2")).Copy
I am trying to create a method to select the values of contiguous range selections on excel worksheets.
Users will be making new worksheets in new workbooks out of the data from the old sheets.
Currently users make a selection is and copy it to a new worksheet.
Is it possible to retrieve from the 'clipboard', the values from the associated cells?
And then to parse them into columns in a new worksheet?
Earlier I tried this bit of ( it didnt work )
Sub AddNew()
' for passing highlighted text into the form so that I can pass it
'to a new workbook.
Dim PassData1 As String
Selection.Value = PassData1
Set NewBook = Workbooks.Add
With NewBook
.Title = "xxx"
. SaveAs Filename:="xxx.xls"
End With
Range("A1").Value = PassData1
End Sub
I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.
Some questions:
1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select".
2) Is there a way to select the workbook from were to copy the column without needing it to actually open?
3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?
As the buyer edits orders, I have a macro which pulls out all the parts with changes and puts them on a sheet named "Changes" in the attached "Sample" workbook. Since the buyer will do dozens of these edits a day, I'm trying to create a simple way for them to create a list of parts they may want to try and buy from another source.
I would like to add a macro that would open a message box telling the buyer to select the parts they want to copy. They could select whatever rows they want to copy to the "Backorder List", and select "OK" or hit "Enter", and the macro would open the workbook named "Backorder List.xls" on their desktop, (mine is located under "C:Documents and Settingsjerry.hutchesonDesktop"), and copy the selected rows to the workbook, then save changes and close the "List" workbook. It should only paste the values, to preserve whatever formatting is in the book being copied TO, and it should copy below any data already in the workbook.
To make things even more difficult, the macro would also need to copy the Vendor # and PO # from the top of the "Sample" workbook, (cells B2 and B3 respectively), and enter that data beside the other data just pasted beside the parts data in the backorder list. (I have entered sample data in the backorder list to show what I mean.)
I currently have a macro that goes into a specified folder and opens all the files within that folder. This works great for me, but if i have to change the location of the folder i need go into the macro and change the destination code.
I was hoping to let other people use this, but not necessarily people who would be able to go in and manually change the folder location. What i was wondering is if there is a function out there which would pop up a browse box which would allow the user to specify the folder location in which the files are kept? This would mean any user could use the tool to open the files without having to have the spreadsheet setup up on their machine.
I have a workbook I add report data to, in the form of imported sheets. I know how to do this with text files, but I'm lost on existing excel files. I want to add Sheet1 from one excel file to another workbook. I got this far, but I don't know how to tell the computer to import the data from Sheet1 in the old file into the new version of Sheet1 in the workbook. So I currently get a blank new sheet.
Sub OpenExcelFile()
Dim FileToOpen As String
Dim wks_exp_src As Worksheet
Dim i As Long
Dim strSourcePath As String
Dim strSourceName As String
Dim strSourceExt As String
Dim strNewShName As String
Dim strNameFinal As String
Dim FirstPos As Long
Dim bolShCreated As Boolean
ChDir ThisWorkbook.Path & "" ' "C:"
I have a workbook with 30 some sheets. I would like to have a macro that if i select multiple sheets, will paste the values and formats of those sheets into a new workbook. I would like this to keep the names of the worksheets when transfered to the new workbook.
Thus far, I have been using this code, which does nearly everything i want, but instead of pasting the values, it gives me #VALUE! for nearly all the cells. Most of the cells are using Vlookup and/or Indirect functions to reference other sheets- not sure if this is relevant. The few cells that do paste accurately are either text or simply reference another cell on a different worksheet without a function.
Here's my code.
Sub PasteShtVal()
Dim w As Worksheet
ActiveWindow.SelectedSheets.Copy
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I'd like to import column A,E, H, and I in different 11 workbooks in MYdocument folder to one Master workbook.
I'd like to import 'Name' and ' ID' in cell E3 and C3 in each different 11 workbooks to cell A1 and cell B1 in Master workbook.
Starting row for Column A,E,H and I are 'row 6' because row1 thru 5 are title, name, and comments.
In the Sheet 1 in Master workbook will be the Main sheet for command buttons, so I want to import after Sheet1 in Master workbook.
I want to import each worksheet , not stacked type. I got 11 workbooks in my document right now, but numbers are always changing. I also import in row 2 in Master workbook because I need to add title or button on row 1 in Master workbook.
How can I import only selected columns from row 6 into row 2 in master workbook?
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
I had a macro on my previous laptop that worked, but didn't have the macro backed up so it was lost.
I need a vba macro that will copy all active/selected worksheets into a new workbook. Included requirements:
* Maintain tab names
* Only bring in contents/data with all formats (including logo image, but NOT formulas or hidden rows/columns)
* Keep the file name the same except adding "- FINAL" to the end
I'm opening 2 workbooks and I'm on Manual Calculation option. I wanted to calculate only workbook A. The standard button only give me the option of calculating a worksheet or all open workbooks (F9).
View 4 Replies View RelatedI have a worksheet with some info that is filled in from other employees. I have Customer Name, Date of Oldest Invoice, Avg. Days Beyond terms in column N42:N44, with the data in column M42:44. I want to copy and paste that into another workbook, I need to transpose the data and then paste it into the next blank row. IF POSSIBLE, I would like to have some of the column headings the same as the above fields, and I would like to paste the data into the correct column heading.
Currently I have the code listed below. It copies the data, opens the spreadsheet, (this is where it fails) find the next blank row and transpose and paste the data. It does transpose and paste the data but it does not look for the next blank row, instead it just transposes and pastes the data in the last cell that was active when the work book was closed.
Code:
Private Sub CommandButton1_Click()' Paste Macro
' Macro recorded 5/14/14
'
'
Range("O42:O47").Select
Selection.Copy
Workbooks.Open Filename:="L:Financial Services! FRA !! Preston !3rd Party Collections Accounts 2014.xls"
[Code] ..........
How do i calculate only the active workbook in VBA?
simple question, but i'm really slow today.
I have few data that I need to calculate average:
A B C
9 3 6
5 2 4
3 6 8
I need to calculate the average of column A/B/C --> Average (A2:C2) and Average (A3:C3) and Average (A4:C4). The end result should be pasted to another workbook from A1:A3
I would like to use VBA since I have written some VBA to other parts of the report....
I am building an excel workbook to calculate a number of different results and I am having trouble with one particular formula.
I need to enter in a formula that will return a value for the following:
ex. C21 = 10
C22 = 10
C23 = 10
C24 = IF CUMULATIVE SUM OF C21:C23 IS GREATER THAN 15 THEN START COUNTING AT 15 BUT INCLUDE THE FIRST 15
SO USING THE EXAMPLE ....IF C21:C23 = 30 THEN C24 SHOULD EQUAL 30. BUT IF C21:C23 WERE TO EQUAL 14 THEN C24 SHOULD EQUAL 0
I have build an add-in To Calculate Specific Sheet when something change in the workbook. For this I am unselecting the "automatic calculation" option and check "Manual". Now with every change in a cell i would to call AUTO_CALCULATION_PI that is calculating only the sheet that the user have been selected.
The problem when i change a cell Workbook_SheetChange is not stiggered and nothign happen. I dont understand why? The add-in is doing the following. Create a Menu "PI Options", this open a user form where you can select the worksheet to calculate, the choices is store in hidden worksheet called "AUTO-CALCULATION-PI". When a cell is change the Macro AUTO_CALCULATION is called, and calculated the sheets that the user have selected.
I have a workbook with about 34 worksheets (one for every day plus a few extras). I have a summary sheet which collates all the data from each other worksheet. I have required a "MAX IF" type array formula on this summary sheet which slows everything down.
Is it possible to have the whole workbook in automatic calculation, but set the 'Summary' sheet to manual calculation when I press F9 or click on a button?
I have a macro which autofilters a range of cells.
I can count the number of visible rows by using the following code
Code:
lcountActive = Range("BC34:BC" & x).SpecialCells(xlCellTypeVisible).Count
What I would like to do is both calculate the Average value of the visible cells and the Sum as well. I have tried to adapt the code I have but my knowledge is just not good enough to do this.
Can you ctrl-F for only highlighted cells? I want to change all cells with '#VALUE!' in them, however, I only want to do this for a few columns.
View 4 Replies View RelatedI'm trying to format a new spreadsheet and was wondering if there was a way that I could stop the search function (normal Ctrl + F) looking through all cells, and only looking through column A? It has to be permanent, so I couldn't only highlight the ones I need when searching, and I can't use VBA due to security risks.Is this even possible?
View 7 Replies View RelatedCan you help with how to import into vba just the data in the currently selected cells?
What I want to do is to manually select a range of rows (in a single column) and upon running the macro put the data from each cell into the uppermost cell with each seperated with a comma.
I can manage the combining of the data but don't now the selection is read into VBA.