I have a file and i want to make an exact copy with only values and the layout.
I can do copy paste each sheet in the new file one by one. with first the values and then the layout.
But the file has lot of sheets so it would be great to find a faster way then do it one by one.
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek()
If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
[Code]....
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
I have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.
Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.
I previously posted a problem related to copying data from one sheet and paste it in another workbook when "Delivered" is chosen from a drop down menu. Everything works fine with my dummy files, but a problem comes up when I try to incorporate this code into the original file.
The issue is that once the code copies the info from the range A3:D3 it has to paste it into different cells in the other workbook.
Please see the attached file to see where I need the data from the range A3:D3 to be pasted.
I am writing a macro that will take data from a series of objects and then filter 3 columns according to their attributes.
EX.
Object 1 Example1 20140306 10
This combination of values is unique so is used to grab the value in the third column after the filter has ran.
My issue is that in the second column the actual values are 50201403069999 and they change per value although they represent the same date "20140306" so manually I can type in "20140306" and the filter will show the results however it seems the macro requests the exact which I can't provide as this the superfluous numbers change.
Is there a way to make it the auto filter search the value??
My second issue is how to extract the value sought once found. Since this cell will be different each time, what is the best way of selecting would it be C3 select then down?
Here is my current code:
Code:
Sub IMPORTPRICES()
Dim i As Integer
For Each Ativo In Sheet4.Range("A2:A6")
[Code].....
What command do I enter so that a value in one cell is copied EXACTLY to another cell? For example, I have today's date in cell A4 and I want the exact date to be automatically copied to cell E5.
I can't seem to figure out the correct function to do this. I looked for help but there are just ways to transfer formulas over to other cells.
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
When I click a command button an InputBox pops up asking me to enter a set of initials. When I enter the initials and click OK all of the rows with the initials in column 'H' are transfered to another worksheet. However if I was searching for say the initials 'AP' and I only entered 'A' and clicked OK, it will still transfer all the corresponding data with an 'A' in column 'H'.
In other words I want the data that is entered into the inputbox to be exact or nothing gets transfered.
I'm trying to create a macro that will take a value found on Sheet 1 in Column A and search Sheet 2 Column D. If the value matches exactly it will highlight the row on Sheet 2 in Red and if a match is found but it has some extra characters in the list it will highlight the row in Yellow. I have included a sample sheet to show you exactly how the outcome should look. It will need to run on a loop until there are no more values found in Column A on sheet 1.
View 7 Replies View RelatedI went through all the forums but confused a little. In the work sheet I want Excel to find an exact word such as letter "a" in all over the work sheet (not a column name) and paste it to the destination work sheet. I am attaching an example file, can some help me to solve the problem.
View 9 Replies View RelatedBelow is my current code. The strFind1 searches for a name within the database and then I need strFind2 to do a exact for a Subproject search and a partial search for everything containing the Subproject selected and other Subprojects. Currently, when the database entry in the worksheet includes Subproject 1 the search function works but when I have an entry that contains Subproject 1/Subproject 4 it does not find the entry. How can I expand the strFind2 to equal what is selected in the Combobox2 and find entries that have what is selected plus more text. I have set the line where I think everything is going wrong to a bold format.
[Code] .....
I need an autofilter which filters rows based on a list of values (+50).
The problem is that I am looking for rows INCLUDING values from the list, not for exact match.
I am not able to make excel filter values including the values from the list... I am able just to filter values matching exactly values from the list.
[Code] .....
I have "systems" that have multiple "units". I what to sum values for units that have EXACT MATCH in the system column. Example data is below.
I am trying things like =SUMIF(B:B,"EXACT MATCH",C:C)
How do I express exact match? For example the first 3 in the list I would want to return a value of 9. Note: Lists can have up to 3000 rows and > 1000 system ID's.
Unit ID
SYSTEM ID
Qty
MRK0012179
MRK0012179
3
[Code] ......
I am very new withe macro I recieve every day a CSV file from our supplier withe a list of the products that got updated withe new price, stock count, product ID etc.
I have my own worksheet with the product ID that we use, and I want to finde the exact match to my product ID in that CSV file and delete all other that don't match.
But i need them get deletede by rows thos product ID that dosen't match to my workbook.
I tried with this, so it could set an X in front of my match then i could filter and copy it to my workbook but it dosen't work:
Because the same product ID is sometime in 100 rows and the X come only in front of one of them.
=IF(ISNA(MATCH(Q2:Q1000;G$2:G$1000;0));"";"X")
so with some macro. I need to have every row deleted that don't match to my list of product ID.
Sheet linked from external file, new data coming daily. How to copy Values of cells from B4 till B-empty to C column? The attached file has a properly displayed data.
View 3 Replies View RelatedI have the following line of code:
Code:
Range("D" & LastRow).Value = Area.Text + Level.Text
Area and Level are both comboboxes on a userform, I want them to both go to the same cell but with a space between them. Right now it looks like "AreaLevel" but I want it to look like "Area Level".
I assume that I can do this by making each value in the combobox have a space after it but I would like to know the proper way of doing it...
I need to make an identical copy with the name I choose from the input box with (wk2).
For example:
If I enter Tommy in the input box, it would make 2 worksheets:
Dim NewSheetName As String
Sheets("NewPerson").Copy After:=Worksheets(Worksheets.Count)
NewSheetName = InputBox("What would you like to name this sheet?")
On Error GoTo Err_Trap
ActiveWindow.ActiveSheet.Name = NewSheetName
Err_Trap:
If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("NewPerson (2)").Delete
Application.DisplayAlerts = True
MsgBox ("A new sheet was not created." & Chr(10) & _
"Please choose a different name."), vbInformation, "Sheet Creation Error"
Exit Sub
End If
I am simply trying to make a copy of a userform in my project.
Unless I am missing the obvious, how are you supposed to perform this operation ?
I've two collumns, after some index the values of cells are zero
how i can draw a chart without selecting those zero cells?
I've developed a spreadsheet that shows what errors people made in their spreadsheets. I compare cell A1 in spreadsheet 1 (original) against cell A1 in spreadsheet 2 (final), in spreadsheet 3. Spreadsheet 3 shows a "-" if the cell they wrote was OK, and shows the new, correct value if they made an error, as follows:
=IF('Final'!A5='Original'!A5, "-",'Final'!A5)
The problem is, this only works if I take the original spreadsheets, which contain formulae in each cell, and paste them into a new spreadsheet using paste values.
Is it possible for the IF function to search the formula in a cell, and not the value?
I have on my Sheet named "Data" in Cell K4
=CONCATENATE(L16,O25,L18,O25,K20,S25,N22)
The values of that cell become a formula.
I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.
The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.
Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?
I'm trying to make two identical worksheets in a workbook. I would like the second sheet reflect all the changes made in the first one. I thought I would simply use = but it doesn't work correctly if I add or delete rows. If I add a row between row 1 and 2 in the first sheet the formula in row 2 in the second worksheet changes from =sheet1!A2 to =sheet1!A3. The result is the same if I use absolute references which suprised me. I want the formula in row 2 to stay =sheet1!A2 whatever happens on the first sheet.
I don't care about formatting, just data are important.
The reason I want the same data on two worksheets is that the second sheet will contain some more data that should not be visible to everyone. I want to protect the second worksheet and require a password to unhide it.
I need to write a macro that will create a new sheet called "Export Format" and it's A Column needs to be exactly the same as the A Column in another sheet called "Master". I'v been trying to look it up since I have never used VB before, but my boss needs this done soon so i thought I'd ask. This is just a small part of what i need to do but I think it will give me the start I need.
View 2 Replies View RelatedI would like to place a command in a macro to have a workbook replicate itself WITHOUT replicating its macros. Is that possible? Is there a command? I am using Excel 2003.
View 9 Replies View RelatedI have the text value "Ball, Red" somewhere in column A. I don't know which row it's in because it will never be in the same row twice. I need a macro to find this value, then, copy that whole row and "insert copied cells" right below that row and change the value in the newly created cell from "Ball, Red" to "Red, Ball".
I tried macro recorder to do this and had no luck...
Cells.Find(what:="Ball, Red", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A21").Select
Selection.Insert Shift:=xlDown
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Red, Ball"
Range("A22").Select
That's is all there is to it on this one.
See attached file with a smaller sample of the 1667 row table of data I am working with. What I am looking to do is make a list on another sheet of each every line with a county and have the corresponding Township and range with it. So if I have a cell and I select Audubon county, I would like a list to populate that has the 12 lines of Audubon county in column A, and the Township in column B and the Range in column C.
Excel Rate Example.xlsx
I do data entry for a webstore and one cell is used to map out the exact product category/subcategory path. I currently have to copy the appropriate path from a long list on one page and then paste it into the cell. I have to do this a hundred times a day. It would be nice if each cell within that column can have these values stored in them so I can just click on the cell and open up a drag down box and select the needed value. Is this possible to do in Excel?
View 2 Replies View RelatedHow do I make a formula based on two incremental values
e.g I drag this down
=ROWS(A$1:$A1)*B$4
where B4 is say 0.2
I will get
0.2
0.4
0.6 etc
But what if I want to start from 5 so it will be like this
5.2
5.4
5.6 etc
I need to make an IF statement that when it's true it need to retrieve the values from a different column. With what function is it possible to just retrieves values?
I need to return the value from pivot table. Forgot to mention this.