I have been trying for two weeks to look for excel function that can copy specific rolls of a worksheet to a specific rolls of another worksheet. I do not know the best way to explain the problem here. I have attached an excel, sheet 1 is the original data while sheet two is a sample of how i want the new sheet to look like after applying an excel function.
View 10 RepliesI need to get a function that can copy some rolls in a worksheet 1 to worksheet 2 by sorting worksheet 1 according to column A of that sheet. I have attached a sample of what i intent doing for bether understanding.
View 2 Replies View RelatedI clean over 150,000 records a month, over 98% of the challenges have been resolved through this forum.
Here is the challenge
We have Columns like this
29669
January 20, 2014
January 21, 2014
January 28, 2014
January 5, 2014
34823
February 5, 2014
22015
January 3, 2014
I need a formula that can apply to all the column A and whenever the value on any cell is empty, it would copy the value of the cell above it.
the result need to look like this.
29669
January 20, 2014
29669
January 21, 2014
29669
January 28, 2014
29669
January 5, 2014
34823
February 5, 2014
22015
January 3, 2014
I'm trying to create a formula that takes from "Manual" Sheet the number from colume G2 and multiply it by the percentage in sheet "AllocationRule".
My formula currently is =Manual!$G$2*AllocationRule!$B5.
What needs to happen is that the total number in "Manual" needs to be distributed evenly in 4 rows by the percentages allocated in "AllocationRule".
Right now I can't copy my formula over to the sheet because the "AllocationRule" should stop at B5 and not go further and the G2 from "Manual" should not change for the percentage allocation but should change to the next row for the next month.
And then after I've done the calculation I want the LOB in "AllocationRule" to be displayed in the LOB in "H1913_H1914" but I'm not sure what formula to use.
I remember years ago that I made a couple formulas that on sheet one would transfer to another sheet when I enter in sheet one.
View 5 Replies View RelatedI would like a macro that will go to a fixed sheet, copy the format, go back to the previous sheet and paste the format. My problems arise going back to the previously activated sheet rather than just a fixed sheet.
View 2 Replies View RelatedI would appreciate if someone can help me figure out a macro that will copy range (A3:T112) from "Step 1" sheet to next available row in "Step 2" sheet.
View 8 Replies View RelatedHow do I change this macro to refer to the current sheet name instead of "FullScreen (2)"?
View 4 Replies View RelatedThe senior members have made it less intimidating to start writing code. Infact I picked up a DUMMIES book and a Macro book and it's been a lot of fun. Anyway here is my code that I have created thus far.
HTML Code:
Sub ReportExtract()
Sheets("Sheet1").Range("B2").Copy Destination:=Sheets("Sheet2").Range("C4")
Sheets("Sheet1").Range("A2").Copy Destination:=Sheets("Sheet2").Range("C6") '
[Code]....
Essentially what I am trying to do is copy these cells from sheet 1 to sheet 2 and then save it as another file using the name of C6&"Text String *Report" This would essentially take the first record A2 - CL2 and then delete that record and move onto the next row 3 and loop through and create another file with the same name C6(Sheet2)&"Report Name" Sheet 2 is the template where the data from the rows will drop into. I have those called out above in the code. It works just fine. I'm stuck though with how to copy the file and move threw all the records and saving them as separate files. Just to make sure I am clear it's 1. Copy data from sheet1 to sheet2 (Template). 2. Then save that file with the file name as cell C6&"some text" concatenated. Loop through sheet1 grab the next record and rinse lather repeat. Just a few weeks ago I was struggled to create macros and now I can copy. Pretty weak I know but I really enjoy this, in fact I sold all my books and just have VBA material so I stay focused.
I'm opening an excel workbook and link it to a workbook variable type, then I'm copying a sheet to a new book and trying to link it to a new workbook variable type
At the moment I can do this with the piece of code at the buttom, but I believe that this is not the best way to do it.
Why?
Because, this procedure is causing me some troubles at the end.
What happens in the last line of the code is that, besides I don't have any error, at the end of the procedure the workbook stills open in the memory and it should be deleted from the memory with the Set ... = nothing....
The problem is step happens multiple times, and the default sheet name increments (Sheet1, Sheet5, etc) each time.
Current code looks something like this:
Sheets.Add
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Fancy New Sheet"
Is there some way to
Sheets.Add Name = "Fancy New Sheet"
Or define a variable that grabs whatever the name of the new sheet is, then renames it to something specific?
On Sheet1, Row 2 I have my columns named January-December, which correspond to the names of the other 12 sheets in my workbook. I want to test and sum the same range of cells in each sheet by simply coping and pasting the formula. I am using INDIRECT and SUMPRODUCT, but is there a better way? It seems to have caused the sheet to run slowly. Perhaps it's just the amount of data. Here is an example of the formulas I am using:
=SUMPRODUCT((INDIRECT("'"&B2&"'!$B$3:$B$200")=$A$2)*(INDIRECT("'"&B2&"'!$C$3:$C$200")="Yes")*(INDIRE CT("'"&B2&"'!$E$3:$E$200")))
This gives me the intended result. B2 in this case is January. C2 is February and so forth.
[URL]
All I need to get it working is a formula to look up a specific cell in every sheet, if the cell returns a specific value it will return the names of all the sheets. Heres what I have so far:
=IF(COUNTIF('Pupil A'!D5, "ICT"),'Pupil A'!C1, FALSE)
'Pupil A' could be all the sheets in the workbook and hopefully that will return the names of all those sheets. I made a new question as this seems to be more specific, I will of course update my old question when I get it working.
In this file sheet1 is not printing. But other sheets r ok. It show print preview. Also it take print command and printer also show that printing. But actually not printing. The printer not even taking paper for printing.
View 3 Replies View RelatedI have below code, which copy the sheet from different workbook and paste it in current book. How to ensure that below code find Sheet name "Create Positions" and copy that sheet data only and not other sheet?
[Code] .....
how I can use a hyperlink to access a particular sheet of another workbook? for eg, I have 2 workbooks, A and B.' A ' has a set of 15 hyperlinks. When I click the first link in 'A' it should open sheet 1 in 'B' and when i click link 2 it should open 'sheet 2' in 'B'. How can I do that?
View 6 Replies View RelatedI have a large document with a lot of names listed. I want to make up new sheets for each person listed. Is there a formula that I can enter that will move the entire row based on the person's name? See attached.
View 3 Replies View RelatedI have a sheet called "Richard" and i wish to assign a button so that when i click it, it goes straight to it.
I know how to make a button and assign the macro but i just need the code. So would be very grateful if somebody could help me with this or point me in the right direction.
I have a sheet which is a basically my template, it has a bunch of formulas etc on it
I need to create about 200 copies of this exact sheet and its formatting etc, is there anyway you can bulk copy a worksheet or does this need to be done individually?
how can i disable "Cut" in a specific sheet for users?
View 3 Replies View RelatedHow to create a hyperlink to a specific sheet in a different workbook.
Both workbooks are located on the same drive if that makes any difference.
Switched to Excel after using OpenOffice and I'm stuck on knowing what an old a 'find and replace' formula would be in Excel. It would remove a specific character (or word) ONLY if it was the last characters in a cell.
The old find and replace for open office:
Find: (.*)/$
Replace: $1
It's not that important now to delete a word, mainly the last slash '/' ONLY if it's the last character e.g. this data has 2 rows with a '/' as the last character
website.com/page
website.com/page/
website.com/page/page/
Running my old find and replace formula would remove the last slashes, but leave the others
website.com/page
website.com/page
website.com/page/page
Need simple replacement to the find and replace but a formula is also right.
I'm trying to add some code so that it only runs on the specific sheet or a range of specific sheets - but this list might change in the future) as a worksheet event... When I have been doing this in the past, I have been copying the code to each of the individual sheets but this now seems to be incorrect.
This time, though, I have created the code on one sheet (with a Private Sub Worksheet_Calculate() event) which launches a macro in a separate module - my logic being to have as little code as possible in each of the sheets for copying, pasting, editing purposes, and centralise the main code in the module. However, I've noticed that this code is actually being run on all the sheets - even those I do not want it to run on. I thought, while developing this code, that it would only run on the one sheet it had been added to (e.g. right click the worksheet name, view code).
I have my template workbook with sheet tab as following:
Summary/Report/Contact 1/Contact 2/Contact 3/Contact 4/Contact 5/Note/
And I have one macro to run and only want to run in the specific sheet as below:
Summary/Report/Contact 1/Contact 2/Contact 3/Contact 4/Contact 5/Note/
The sheet name for contact 1 to 5 is varies means it can be change to any name based on different job. How should I write my macro in order to do that with one macro?
we have a Workbook... in this workbook we want run a VBA or Macro which macro copy a specific sheets (Sheet Name "Abstract") this sheet but when we copy this sheet then in sheet name show file name where this sheet come..
for eg.
if Abstract sheet copy ABC.xls file then sheet name show in my workbook ABC
We have 205 file in the folder (folder store in E:yr 13-14) is it possible that in few files abstract sheet not found..
I have a code that is inside a specific sheet. How can i make it run when i open the workbook?
I known that i should make a workbook open in ThisWorkbook but my other code doesnt have a name.
It starts as a Private Sub Worksheet_Calculate.
I'm making a Task list with assignments for each of my team members. Columns A & B describes the ID number of the task, Column C describes the Task itself, Columns D to J are hidden, Column K describes the author of the task.
Column L is the one that have the name (or names) of the person who is in charge of doing that task. What I need to do is to create a macro that searches the name of that person in Column L and once it finds it it will create another sheet with the name of that person. And add to that sheet the entire row of his task..
I was wondering how to input data on one sheet, then have specifics from that first sheet get sorted and put on the second and third sheet according to it's value. I have attached an example with my comments in red.
View 3 Replies View RelatedI found the following code to delete duplicates. I want the code to work on close and only on a specific sheet....
View 3 Replies View RelatedI have got an Excel program that retrieves colunms of data from a Database using MS Query on the first sheet of the workbook. I then have some code that abstracts the data from from the first sheet into each seperate sheet depending on the number value in the Family Groups column. This is all done using a loop.
This all worked fine until I tried to insert another sheet which contains a couple of columns of static values which I dont wish to be affected by the VBA loop. when I now run the code it overwrites the contents of this sheet.
May question is how can I put some code into my loop to prevent it from updating this sheet in the work book?