I have got the following code to fill series and someone please help me that how to use this macro I can enter thses code in sheet1 on enter event and the result iwill be displayed in sheet2 and if any duplication in series then it is copied in sheet3....
View 9 RepliesHaven't done any VBA in a long time so I'm very rusty. I need to copy cells A2 - G2 from sheets 2 - 30 in a workbook to create rows Sheet 1, which will start out blank. This is the final part of longer process. Sheet 1 is used as a summary page.
View 2 Replies View RelatedI have 4 sheets name A, B, C and D.
The D worksheet is a summary sheet that pulls in data from A, B and C.
e.g.
=A!A4
=A!C4
There are approx 50 of these references to Cell A. Is there an easy way to copy the same formulas but reference Sheet B and C without having to retype them all?
I have a spreadsheet (see attached) in which there are many series and i want to be able to delete most of the series except the first 4.
(The 0%, 5% 10% air voids and the 95% comp line)
iv got this code, which is attacted to a command button, which works fine to delete all the series but makes the graph a blank white box. id prefer it so it delete just the series not the graph "picture?" ...
I have about 50 lincharts that I wont to update with VBA but they have different number of series. Some have two series, some three and others four. Is there a way to count how many series a chart has? Otherwise could I get tips on how to approach this problem.
View 2 Replies View RelatedI have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I need to do VBA coding. Got 12 Sheets for 12 month of Sales. Every Sheets are in same Header Format.
For Column R (Status), there's Filter Data "TRUE" and "FALSE". I have to move/copy "TRUE" item into Sheet Aging 2014.
I manage to transfer using only one Sheets using Advanced Filter VBA, failed with other Sheet.
I attached the file : Sales 2014.xlsx‎
I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?
With cht
.ChartType = xlColumnClustered
' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
I need a macro… when I run the macro.. I want to create work sheet based in the shift time and copy entire row related to that shift time of all the cell to that particular sheet… there may be multiple rows related to same shift time but remaining data may be different..
View 9 Replies View RelatedIs this possible using code: Copy Cell A1 and Paste in first empty Cell down Column D. This would be connected to a command button. Both Cell A1 and Column D are on the same spreadsheet.
View 3 Replies View RelatedI have an excel document with multiple excel sheets(sheet1, sheet2...etc), now every sheet contains a cell "total".
Now I want to copy the row containing "total" from all the sheets into another sheet called "report".
I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets
Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................
I want to automate the transferring of specific pieces of data from one spreadsheet into another spreadsheet. There are many project spreadsheets that all feed into a resource plan spreadsheet showing every employee, what projects each of them are on, and how many hours they are spending on each project for the quarter.
Now what I would like to do is to automate this process of feeding the data from the many project spreadsheets into the resource plan spreadsheet. With transferring the specific data from the one to the other. Basically I need it so that excel looks at the project spreadsheet copies the name of the employee and the hours they have worked each week for the quarter and paste It into the big resource plan. What I am finding tricky is knowing how to make it automatically paste into the right area as in copy an employee and paste the details in the same employees cells in the other spreadsheet.
I am trying to create a new sheet from an existing sheet. I only need to copy the sheets and not the code from the original sheet. But, when I copy the first sheet the code module also gets copied to this new sheet.
View 5 Replies View RelatedI am using the following code to copy data from multiple workbooks in a folder to one workbook.
Code:
Sub copy_files()
Dim wkb1, wkb2, wkb3 As Workbook
Dim i, lastrow As Integer
[Code]....
What changes are required in the above code to paste the data in different sheets on one workbook.
I have a macro that copies 3 sheets to a new workbook but I do not want to copy worksheet properties. Attached is my Macro that I use but when the user accessed via the webpage It will have sheet properties(macro) attached. my Desire is to not have macros on my new work book.
Sub WEBPAGE()
Dim wbkNew As Workbook
Dim wbkCurrent As Workbook
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application. ScreenUpdating = False
Set wbkCurrent = ThisWorkbook....................
I have a workbook with 10 sheets.
Each sheets has data starting row 14 and column B with row 13 being column Headers.
I want to copy data from each sheet, having column headers suppose A, F & G in to a single sheet. The position of column headers I want to copy are different in each sheets
Means macro will go to sheet1 first, he will copy data from column headers A, F & G and paste in new sheet suppose main.
then macro will go to sheet2, he will copy all the data from column headerA, F & G and paste it in main sheet after the last used row and so on.
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I have a large list of invoices from different suppliers in a workbook. What I would like to do is to produce statements using this list so that I don't have to type the information into each statement.
I have the following fields:
Customer Name:
Customer Address 1:
Customer Address 2:
Customer Address 3:
Customer Address 4:
Invoice Number:
Invoice Amount:
I have a table lists producted items and quantities per weekdays as follows:
Weekday
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Produced item
Yellow
Yellow
Yellow
Red
Green
Blue
Yellow
Amount
1200
300
790
275
942
612
421
What I need is: If I specify the weekday; to start from; and number of days, then I should get the total productivity by item..
For example:
Tue (i.e. starting weekday)
3 (i.e. number of days)
Results:
1090 - Yellow
275 - Red
I am trying to run the following code on multile sheets in a workbook. so far this code is working fine for one worksheet. Can someone help me modify this so it runs on multiple sheets. There can be more then 1 worksheet in a workbook depending upon data ...
View 9 Replies View Relatedi have a macro code but i don't know how to apply it to all sheets in the same workbook
my code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D5:D100"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now() [code]....
I havae the following macro which i recorded in Excel. I want this Macro to run after another macro that groups data and creats tabs. The following macro will then add a column and run an array formula. I think this can be done in a loop but i'm not sure how to do it. This is working but takes a long time and times out by the time it reaches the last tab.
Application.Run "TotalHrs"
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("I4").Select
ActiveCell.FormulaR1C1 = "Invoiced Amount"
Range("I5").Select
Columns("I:I").EntireColumn.AutoFit
Selection.FormulaArray = _
"=INDEX(VLookup!R2C2:R242C4,MATCH(RC[-3]&RC[-2],R5C6:R2500C6&R5C7:R2500C7,0),3)*R[3]C[-1]"
Selection.Copy
Range("I6:I1000").Select...................
How do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.
View 6 Replies View RelatedWe are trying to work out a pricing model based on how many units we will produce. In production the price per unit will be really high when you produce a low quanity, and the price per unit will be lower with the more units you produce (but never reach zero). What we are trying to accomplish is to create a formula to build a pricing structure that accounts for this curve without having to price every possible scenario.
For example, if we have the following data where for 1 unit we charge $3 per unit to produce, for 5 units we charge $2 per units to produce, etc...:
1 units @ $3 per unit
5 units @ $2 per unit
10 units @ $1 per unit
What I need to figure out is how much it would cost per unit if we were to produce 3 units. I'm guessing it would be somewhere around the $2.40 mark give or take. Or say 8 units, would be something like $1.50 per unit.
Is there a formula that can calculate this? I've tried thinking of it as a moving average, or prorating the pricing.
I have been using a macro for the last while and it has done the job untill recently. It is too slow and I need to speed it up. What is does is looks for a product code in column 3 of the "shelf" sheet and then goes to the "sheet 1" and if it finds it copys accross the information in each cell choosen. There are about 4000 products but the most a particular product will appear is twice. Also the information in columns "e - j" gets copied everytime to "sheet 1" the code is below and i have included an excel file.
Sub One_prices()
'this section does the actual updating of the PLOF/SLOF for 1 prices
Sheets("Sheet1").Select
Dim cf As Range, s1 As Worksheet, s2 As Worksheet, p As Long
Dim firstfound As String
Set s1 = Worksheets("Sheet1")
Set s2 = Worksheets("Shelf_stock_Labels")
'Application. ScreenUpdating = False
p = 2
q = 1
Do While Not IsEmpty(s2.Cells(p, 4))
Set cf = s1.Columns.Find(s2.Cells(p, 4), SearchOrder:=xlByRows)
If Not cf Is Nothing Then
firstfound = cf.Address
Do
I've created one file: Mappe1_results.xlsm
This file ask in another file "Mappe1_ground.xlsx" for "B" and "N". Now one new sheet is created with the name "month-2014" and shows me which Category (B) is how often referred in one month. After that one chart is created for a better representation. This can be done for every month (which month is selected by the user over the button "Auswertung" in Sheet "Tabelle1").
Now I have a few problems / requirements:
1. If one user is using (maybe) Jan as his selected month, in the sheet "Auswertung" should be one duplicated chart of the Jan Chart., with no other (previous) series. Because Jan has no previous conditions (prev. year).
1.1 If one user is using another month (maybe Feb), in the sheet "Auswertung" should be one chart with both series of Jan and Feb and so one (for the other months). So that in "Auswertung" the chart is one comparison over the months. Only Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct/Nov/Dec have one prev. month.
For example: If one user write "Apr" into the inputbox and "Mar" is in the sheet existing, so in the chart of "Auswertung" should be April and March shown.
Some functions are set in my macros, but the problem is that the results are not equal between the sheet "Auswertung" and maybe "Jan-2014" or "Feb-2014". Because in "Auswertung" we need called all Categories (they can be found in "Referenz" - A).
All what I want is in "Auswertung" one chart with all present categories of "Referenz" - A and the series of the created sheets by the user.
2. The next problem is, every created sheet has one legend "Anzahl im ..." - this legend of every series should be shown in "Auswertung", too. So that we know which color is for which month, u know.
Sheet1: 10 columns (can have any number of rows, let's say 5,000 rows)
Sheet2: 10 columns (can also have random number of rows, let's say 10,000)
Both sheets have the same data but the column headers may be slightly different due to the source being different systems
Here's what I need: I need a macro to copy the data (without the headers) from Sheet1 underneath the the last row of Sheet2. For example beginning on the 10,001 row of Sheet 2. OR, if it's easier to copy the data in both sheets to a new sheet. The new sheet will already have the headers so begin pasting on the second row
I need a Macro to copy a set range from the Active Sheet and Paste Values in the next available row in a named sheet.
View 5 Replies View RelatedI have a table of many rows and columns of information, some of which is hyperlinked to seperate sheets in the same workbook, some are not hyperlinked at all. What I want to do is select a column and when a hyperlink is found going down the column, copy the sheet that it is linked to into a new book, then find the next hyperlink and copy that sheet into the same book as the first one. So if I have 25 hyperlinks in the column, I finish up with a new book containing 25 sheets in the order they were found. Hope someone can help me with this, it will save me hours of work.
View 9 Replies View Related