I have created a form in Excel for my wife to be able to enter data. Once the data has been entered, a print is required, but, the form is large, it contains lots of formatting, a lot of "space" and a lot of color. I world like to (on another sheet) collect all the relevant text, without any formatting, space or color to produce a concise print.
View 2 RepliesI am trying to get information from two sheets. I have added the lines myarray1 and mystring1. I also added myarray1 in the For Statement. I cannot get it to run correctly. If you take these two lines out, and the myarray1 out of the FOR statement it works for one sheet.
Sub test()
On Error GoTo notfound
myarray1 = Array("A", "D", "G", "H", "I", "End")
myarray = Array("A", "Q", "R", "S", "T", "U", "V", "X", "Y", "Z", "AB", "AC", "END")
For i = 0 To WorksheetFunction.Match("END", myarray1, myarray, 0) - 2
x = WorksheetFunction.Match(ActiveCell.Value, Sheets("phones", "computers").Range("a:a"), 0)
mystring1 = mystring & Sheets("phones").Cells(1, myarray1(i)).Value & ": " & Sheets("phones").Cells(x, myarray1(i)).Value & Chr(10)
mystring = mystring & Sheets("computers").Cells(1, myarray(i)).Value & ": " & Sheets("computers").Cells(x, myarray(i)).Value & Chr(10)
Next i..........................
I have a workbook that has close to a hundred sheets in it (see attached). I need to know how to get all of the information onto one sheet in separate columns like this: EMPLOYEE NO| HIRE DATE| NAME| VAC AMOUNT|WEEKS|VAC DATES| # OF HOURS|AMOUNT|PPE
If you review the attachment you will see how the sheet is read from left to right, so I would need the have the information in that order.
search sheets by the name of cell , like name would be 17012-23-12-45 i need search by the first 5 letters 17012 , because sheet name 17012 , it would be for a lot of sheets so i need to do it automaticaly , and then that specific sheet has been found i need to copy table from there and some pictures in there
View 4 Replies View RelatedI am trying to tie two worksheets together. If text found in one cell in sheet1, make the same cell on sheet2 different color.*
There is a catch... no formula can occupy the cell in sheet2.*
My question is, is there a way of have a formula in completely different cell that will eventually fill the cell on sheet2 with proper information?
To explain a little better, I am trying to tie the sheets together, same cells and everything so when information gets put inside the cell on sheet1 the same cell on sheet2 will change color or display different information, and vice versa. That is the reason no formulas can occupy those cells.
I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT
View 2 Replies View RelatedI am writing a macro that organizes data, but also needs to be able to conditionally copy data from other sheets in a workbook.
I have a table with values...column A has the identifiers (strings of letters) and column N has comments related to the data in each row. So, for example, row 3 column A has the value "AAPL," and row 3 column N has the related value "sells electronic goods."
Now, I want to be able to copy column N based on the value of column A into other sheets using my macros. So, say I have another sheet in the workbook and row 6 column A has the value "AAPL," I want row 6 column N to have the related value from the previous sheet. Is it possible to do this?
I have an excel with several sheets with task for different departments. All of them are assigned to a person responsible for that task. I applied auto filter which gives me the overview for a person Per department, now i would like to gather all departments into one so we have an overview for each person what his tasks are.
View 1 Replies View RelatedI have a wb containing several sheets that the field supervisors use to fill out for ordering material. Each sheet contains different types of material. The problem is that, if they only need one particular item from each type (sheet), they have to print 7 or 8 sheets just for those 7 or 8 items. I want to create a sheet that will list everything they are ordering in one compact, neat area, so they only have to print one sheet.
The set up of the sheets goes like this. There's a column containing the name of all the items. Next to each item is a place they can specify how many of that item they want. They leave it blank if they don't want any. That's it. Really quite simple.
So now, I need this new sheet to find all the cells that a quantity was entered on the other sheets, and list that and the description of the item wanted, along with what type of material it is. I have figured out one way of doing it, but it would take a crap load of hidden IF formulas, and I know there has to be a simpler way. I'm open to all suggestions - whether it's vb codes or formulas, it doesn't matter to me.
Can someone help me i have a few columns of information i need to move over to a another sheet, but the problem is the information i need to move is the end result of a formula and when i move it the answers are gone how do i fix this,,,,, and both sheets are on the same work book as well
is there a vlookup formula to use,,,im not experienced in Vlookup up formulas
I will be adding a weekly report to each sheet, which will have varying rows within it, but always the same column headers.
I want a master sheet that will automatically pull all the rows of data through from each sheet, create a new row if needed but merge rows with the same data name and combine the figures.
I have excel from windows 8 and I am trying to copy the information on one cell on a sheet to multiple sheets. I tried the click on one tab hold shift and click on the last tab and type the information or press F2 or paste the information. Well, nothing works.
View 5 Replies View RelatedIs it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?
View 3 Replies View RelatedI am new to using Macros and have not had great progress building a macros to make my life easier.
What I am trying to do
-In my main database tab 'FW Telecom' Use column A ( a set of numbers) to create a new tab based on that number, with a 'FW' infront IE ( column A shows 11, i want the tab to read FW 11)
-I want to create one for every number in the column
-Then I would like to copy my 'Template' tab to each new tab.
-From there I would like to fill in the information in the new tabs from the main database tab 'FW telecom'
-Since they will all be copies of the original template, i figure i can just get it to pull from the first column A and then fill in the info.
Not too much in theory going on here, just take my database and move the pertinent information to a user friendly look which I set up in 'template'
I have a blank sheet and need to total values in cells to the right of certain information from a range in 90 sheets in other workbooks contained within the same folder.
for example the range is O13:AX500
the first text to search would be "P1", that could be found several times within the range in each workbook with the sheet named "plant"
The value to total would be found in the 2nd cell to its right, ie "P1" in cell O13 then value "£290" in Q13 ::: "P1" in cell X365 then value "£100" in Z365 and so on throughout all 90 workbooks.
The total of the referenced values would then be placed in say B1....
I have around 500 reference codes to total throughout the 90 workbooks so maybe having a list of the text reference down column A may help the process.
Is it possible and can anyone please help with the code to look at a piece of text in new sheet with cell A1 to A500 containing "P1 to P500" and then total all values in the cell 2 to the right in every workbook and place the total in new sheet cell B1 to B500
I would like to generate a dynamic list of employees on sheets based on information on Sheet A. Here is my workbook structure:
-Sheet A contains all employees information (Name, division, salary hire date, etc)
-Sheet B is named Div A
-Sheet C is named Div B
-Etc...
Here is what I want to happen:
-Sheets B should pull the employees name and salary from sheet A only for employees of division A
-Sheet C should pull the employees name and salary from sheet A only for employees of division B
-Etc..
Sheet B, C , etc. should update the info and resort every time sheet A is updated.
So, I'm setting up an accounting book. There's a master sheet that needs to pull from the other sheets that are made. The problem is, with each new sheet that is made, I have to update the formulas on the master. I would like that if I made a new sheet, the master would automatically pull from it.
Example:
Master pulls renter fee info from May 3rd, May 7th
I create sheet (May 15th)
Master pulls renter info from May 3rd, May 7th, May 15th
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code:
Option Explicit
'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
[Code] ......
Imagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.
So far I have only managed to list the files in the folder using code I found on your site!
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 been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
[URL] ....
I want to sort the Inventory Checklist sheet based on Column D but it gives me nothing but references errors.
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.
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 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‎
on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
I 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???