i have a button and code so that when i click the button it prints, saves and creates a new worksheet ie job 2 ready for data entry,
somewhere in that process i would like it to copy the contents of certain cells from sheet job 1 to sheet job list,
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I would like to be able to copy a group of cells from sheet 1 to sheet 2 or 3 based on a value in another cell on sheet 1 eg
sheet 1
If Cell A1 = 24, A2=London Road (this make up an address), A5= omit
If A5 = omit I would like the address copied to sheet 2
If A5 = Complete I would like it copied to sheet 3
I have 1600 address to go through.
I am trying to do a loop to go through all the sheets in a workbook (11 ina all) that will copy all the cells and paste special value. below is the code i am trying to use and it is giving an error.
For Each sheet In ActiveWorkbook.Sheets
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next sheet
I have 5 Sheets titles Quote 1, Quote 2, Quote 3, Quote 4, and Quote 5, and One Sheet called DataQuote. There s a qty column on all 5 Quotesheets at D7:D62. So that if i was to put a numerical value in that column From any of those sheets I need to Put on the DataQuote the Following:
Say i Have a quanity 5 on quote 3 on cell D9.
On the Data sheet I need to Show in A10 The Name of the Sheet, then stuff from Quote 3 A9:F9 in C10:H10.
alternatively if i went back and put something on Quote 1 Put those values in the above range moving down stuff from quote three, automatically updating itself.
I browsed through the other posts concerning copying and couldn't find (or maybe I didn't just understand) how to do what I want. I looking for a macro for a comand button that when pressed copies certain cells in the active row to another sheet. I recorded a macro for what I wanted to do ( to make the explenation easier) for one row but even the recorded macro refuses to work.
I'll put the recorded macro here if it helps someone, but as I said even that doen't work
Private Sub Kopiering_Click()
Range("E3").Select
Selection.Copy
Sheets("TOTAL").Select
Range("K3").Select
ActiveSheet.Paste
Sheets("BRL Newbuilding").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:
Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer
[Code].....
I have a workbook that tracks pollutant emissions. The workbook could end up having up to 180 worksheets, one for each pollutant and the emission total. I have VBA to create a new sheet for each pollutant (attached) using a hidden template sheet.
The summary sheet in position 1 needs to list each pollutant worksheet on a new row, and the same 4 cells from each worksheet (id, name, value, value). I have been typing out the cell references in the summary sheet, and it gets boring [=Sheet!Cell id], [=Sheet!Cell name], etc.
Is there a way for me to copy those cells to the summary sheet when the new sheet is created?
I have a worksheet "parent child" with product data, cells F4 and BK4, pull pertinent data from cells T2 and M2 respectively on a different sheet "products".
A5:A196, D5:D196, F5:F196 is dependent on cell F4 and BK5:BK196 is dependent on BK4.
Once we get to row 197, the cycle starts over again. F197 and BK197 needs to equal products!T3 and products!M3. Then rows 198 through 389 will be dependent on row 197.
I basically need this to repeat perpetually for about 1000 different products on the products sheet, thus the ability to create approximately 193,000 rows.
I am not sure what it will take to do this, i am fine if I have to drag and copy all rows, which I have tried to create and failed at, I end up with products! T196, instead of T4.
I have a list of about 600 addresses that need to be reformatted with a space following X character and need to be in all caps. This would be the more tedious way because I would still need to search the other file to make sure the text itself is correct. I saw on a different forum that this line would work in adding a space
(=REPLACE(A1,5,1,MID(A1,5,1)&" ")) but I need to add two spaces and adjust the location of the space so that it is following the first string of text.
The other approach could be to set up a formula or macro that searches the other file with a section of the original text and replaces that string.
I have a workbook with so many sheets as the working days of a month. I also have a Master sheet.
The sheets are named as the dates. Example: 020113 , 020113, 030113......etc
In the Master sheet, first column has ALL working dates of the month(i don't care about Holidays).
First row of this sheet are headers.
My goal is from the sheet of the certain date of the month automatic copied certain cells values(same in eatch sheet) to certain cells in Master Sheet.
i have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...
View 9 Replies View RelatedI have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
I am trying to make the cells Iam am copying paste hidden cells with all formats - seems to work fine other than the security part of them, Iam makeing a sheet for work and just trying to make it were it will not get destroyed by other users-
View 12 Replies View RelatedI have merged 336 individual spreadsheets into one book, now I want to merge the data in all the sheets into 1 individual sheet. All the sheets have the same size and range, I need to copy a constant range(row,column) from all the different sheets into one.
What VBA functions to use???
Need to autohide cells in sheet 2 and 3 based on sheet 1 cells that are hidden. (Example if i hide 150 switchgear on SOV tab it will hide 150 switchgear in Customer and Foreman sheets as well).
View 1 Replies View RelatedI have a sheet (sheet1) of around 900 rows with data in columns A-K. Column B contains a unique value.
Sheet2 contains data which needs to be highlighted in sheet1 and each row in identified by a unique value in Column B.
sheet1.PNG
sheet2.png
In sheet 1, I only want to highlight the cell which exists in sheet 2 for the matching reference number in column B. For example, in sheet2, the value 'PM328491CN' exists and the value 'CN' is present in cell c2. I want the cell that contains this value in sheet 1 to be highlighted (cell C6).
So, find the matching reference, the highlight the cell in the row which is present in both sheets.
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View RelatedI am trying to get two cells to be used to adjust other cells on the same sheet repeated times.
In a inventory sheet I have 5 columns as such , A1 previous balance, B1 Qty received, C1 qty on hand, D1 last cost, E1 current avg cost What I want, is to be able to enter my weekly received items in the B1 Qty received and the new cost in D1 Last cost cells and have them calculate my current average E1 and update my qty on hand C1 total. This in itself is not the biggest challenge. I was asking if there is a way that after the E1 current average cost and C1 qty on hand are updated by that formula, that the next time I enter a new B1 qty received and new cost in D1 Last cost cell they will update again basically without changing the earlier calculations achieved .
Example: Today I have item X with a A1 previous balance of 10 , with a D1 last cost of $1, and E1 current avg cost $1
I want to receive B1 10 more today at $.50 D1 last cost, which ideally would end up showing
A1 previous 10 (or 20 if adding) , C1 Qty on hand 20, D1 Last cost .5, E1 current avg $.75
Next week I want to receive B1 10 more at $.25 D1 last cost , which then would update showing
A1 previous balance 10 (or 30 if adding), Qty on hand 30, Last cost $.25 and E1 current avg $.58
Tried to write a Macro to Range Cells in a sheet, setting the range values from a another cell. I have encounted a Run-Time error as select method of Range class failed. Below is the Macro.
Sub Macro1()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Worksheets("Pre").Activate
Set r1 = Range("J4")
Set r2 = Range("K4")
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select
Worksheets("Data").Select
Range(r1, r2).Select
Selection.Copy
Sheets("1").Select
Range("B5").Select
ActiveSheet.Paste
End Sub
Have the Ranges been set incorrectly
We have a simple holidays workbook, with seperate sheet tabs for each member of staff in the department to note down there holidays in a calendar.
The 1st row (from B1 and onwards) contains the day numbers of the month, ie. 1 - 31
The 1st column (A2 downwards) shows the 12 months of the year.
The holidays are then marked in the relevant cell matching the dates required. The codes we use are F (full day), A (morning), and P (afternoon). A countif adds up all the occurences of the various codes and totals the ammount of used/remaining days.
Hopefully that drew a picture.
The problem is, each sheet only shows the holidays marked down for that person. There is no way of checking for a "clash" (ie. 1 member of staff off on the same day as another) without flicking to the other sheets and checking yourself.
how can I get a cell shading to automatically show up on the sheets, if there is a holiday noted down (ie a value, A, P or F) in one of the other sheets?
I have a worksheet with columns as follows:
col A Name
col B Category
Col C Subcategory
In column A every row has a name, but in col b and c only the first row of a category or sub are popluated, for example:
Name CAT Sub
a 1 1
b
c 2 2
d 3
e
f 3 4
g
h
As I work with many of these spreadsheets, they are all different in respect to number of products(name) and number of CAT and SUBCAT.
I have a list of names in column a, and in column b either a "0" or a "1"
as part of a longer piece of code I need to be able to copy the names that have a "1" next to them over to column c
I need help with copying some data from one sheet to the other, but I am really bad at explaining this. Can I send the excel sheet to someone and then explain it? I think that will be easier, cuz Ive tried explaining it on another forum and nobody understood what i was saying without being able to see the sheet.
View 9 Replies View RelatedI have a lottery pool in sheet 1 in which i have the names of the people and their number picks. In sheet 2 i have the lottery results.
All i want is to be able to highlite(adding a backround color in the cell) to the matching numbers in sheet 1.
I have got the task of doing the results for a local athletics mid-distance event once again and im fed up of doing it the present way. Basically i have a sheet which i input the race registrations which has the following collums: Number, Name,School,
and i have another sheet for results which have: Position,Number,Time. I then have to merge the two in a magical way and i get the results. However what i want to do is have the first sheet the same but the results sheet would be different reading: Postion,Number,Name,School, Time
But when i input the number, it will check with the other sheet and automatically fill the rest of the boxes (bar position and time) hopefully. Using my knowlegde i gathered at college(fairly average knowledge of vb) i still cant get it to work the way i planned. Is it actually possible to do, or should i go to my pet hate access?
I got a range of data on sheet2, size changes everyday (dynamic) And in sheet1. I got a range of data and the size changes everyday as well. I need to copy the range in sheet2 to sheet1. The position would be at the cell after the last data in sheet1. e.g.
sheet1 got 105 data
I need to paste data of sheet2 start of row106 in sheet1
I'm using Excel 2003, Windows XP, and Microsoft Outlook. I'm trying to copy some cells, which include merged cells, from Excel into a new e-mail using Outlook (the output of some calculations), but every time I do this, Outlook seems to unmerge the cells.
When I paste into Word, the cells remain merged; I can then copy/paste from Word to Outlook with no unmerging occurring. what's going on and/or how to resolve this issue so I can copy things into Outlook straight from Excel?
I am copying a range of rows (which include some hidden rows). When I paste them I see all the rows. Is there a way to retain the hidden attribute?
View 2 Replies View Related