Pasting Cells To Another Sheet If Another Cell = "yes"
Aug 14, 2008
I have started to actually write a few macros for myself and am doing ok ish. this one has stumped me. I would like all open sheets to be considered. if any cell on row 12 on any sheet is changed to "yes", i would like to have from the sheet it was changed on, the following cells copied (some update at intervals, so i would like them copied so they show what is in the cell at that time and then not change with the update)
the sheet name pasted into A144 of sheet 5
the cell from row 6 that is on the same column as the "yes" pasted into B144 of sheet 5
the cell from row 9 that is on the same column as the "yes" pasted into C144 of sheet 5
the cell from row 10 that is on the same column as the "yes" pasted into D144 of sheet 5
if another cell on any sheet is then changed to "yes", i want it to do the same as above, but paste it in row 145, then 146, etc so i end up with a log of those cells when a cell on row 12 = yes
View 12 Replies
ADVERTISEMENT
Nov 10, 2006
I'm trying to write very simple code that copies totals from one worksheet and pastes to a different worksheet. Normally, it is a one cell to one cell copy/paste. For instance, I want to copy the total found in cell "C4" in Worksheet "BalanceSheet" and paste it into cell "C14" in Worksheet "SpreadFinancials." I'm able to do this fine, and the code below is what I've been using.
Occasionally though I'm needing to sum up 2 cells in Worksheet "BalanceSheet" and only copy/paste the summed total into one cell in Worksheet "SpreadFinancials". For instance, I want to sum the totals found in cell C8 and C9 in Worksheet "BalanceSheet" and then paste that summed total into cell "C8" in Worksheet "SpreadFinancials". I'm looking for simple code that does this. Here's an example of what I've been doing so far for one cell to one cell copy/pastes:
Sub PopulateFinancials()
Sheets("BalanceSheet").Range("C4").Copy Sheets("SpreadFinancials").Range("C14")
Sheets("BalanceSheet").Range("D4").Copy Sheets("SpreadFinancials").Range("E14")
Sheets("BalanceSheet").Range("E4").Copy Sheets("SpreadFinancials").Range("G14")
End Sub
View 4 Replies
View Related
Sep 1, 2009
Yes, I am a newbie to VBA. I've tried for hours now to come up with code to make this work, but no go.
Here's the situation:
There are a list of cells all in column A (row ? - ???) that have a string that needs to be searched to locate a number, and insert that particular number into a cell in another sheet, which is in another workbook.
These are actual examples:
*001 RJ 17 17 1,436 316 28 17 0 0 0 0 0 0
*042 RJ 39 39 215 215 0 39 0 0 0 0 0 0
*03A RJ 8 8 282 202 2 8 0 0 0 0 0 0
The numbers needed from these examples would be: 1,436
215
282
End results:Trip 001 = 1,436
Trip 042 = 215
Trip 03A = 282
FYI...The cell data always starts with *, and is followed by three characters we refer to as a Trip, then a space, and then RJ.
The column with the data is always "A", but the starting row may not always be the same. The rows continue down one by one, but there may not always be the same number of rows (each row used will ALWAYS start with an asterisk, though.)
The number that needs to be extracted is always between the 4th and 5th spaces - CHR$(32)'s.
The numbers, once found, then need to go in another sheet into one of the following cells - C28, C29, C30 or C31. Which cell the number goes into depends on which one of the cells, C28-C31, matches the three character Trip text shown after the *. There are currently 16 different sheets that need to be searched to find where the matching Trip number is. The sheet names do not change.
and yes, the people always enter everything shown in the example into one cell.
View 9 Replies
View Related
Feb 2, 2010
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.
View 3 Replies
View Related
Jun 29, 2014
I am looking at trying to develop a workbook for my job that would make things alot more efficient.
Firstly, I download data from a web based app into excel. I now want to take certain full rows and paste them into another sheet depending if they have certain text in a cell. So i want to search the entire coloumn for a value of "OR" and if this is present copy the entire row into a new sheet ?
View 14 Replies
View Related
Feb 11, 2010
I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.
One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.
I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.
Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.
A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.
By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.
View 5 Replies
View Related
Jun 20, 2014
I have a macro that copies all my sheets onto a new sheet called combine. It is pasting onto the combine sheet starting on cell A2 instead of cell A1. Following is my macro.
Sub Combine()
Dim J As Integer
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
On Error Resume Next
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy Destination:=Sheets("Combined").Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub
View 4 Replies
View Related
Mar 3, 2014
I am trying to paste data into cells that are offset from specific data in my excel sheet.
The code I've got so far is:
[Code] ....
What I am aiming for is to search my Columns G and H that contain the values 1 & 1. Then from the cells that contain those variables, move 2 row up and 7 columns to the left and then paste on that active cell.
I am completely new to VBA so im sure there plenty wrong with this VBA script but so far ive managed to paste the data but it just pastes across the whole row instead of just the cells ive copied.. now i just get errors on the script.
View 5 Replies
View Related
Apr 21, 2014
I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
View 1 Replies
View Related
Jun 19, 2012
I have several sheets with one master sheet. I have a trigger on each sheets that manipulates the data on that particular sheet. Here is the code I'm using:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim cell As Range
Set r = Intersect(Target, Range("A:A"))
[Code] .....
Now the point where i put new code is where i want the information from that particular row, column a thru column m to be special pasted linked to the first blank row from the top down to be pasted starting over from Column B. so A1 would be pasted on B1. I ran the recorder and got this as the code ActiveSheet.
Paste Link:=True so i do i incorporate what i want.
View 2 Replies
View Related
Jun 28, 2006
I have multiple worksheets and I am looking to take specific information from those worksheets and paste them into a new one, thereby compiling the information.
The "C" column is populated with certain cells that say "Not Acceptable". This varies from sheet to sheet, but all in the C column. I'm looking for a code that will run through all the sheets, find the cells that say "Not Acceptable", copy that row of information it is in, and paste it onto a specific sheet that we'll call "Summary"
View 4 Replies
View Related
Feb 9, 2010
For simplicity sake if I wanted to paste the letter A in to a cell that already had the letter B in it to make the cell read AB how could I achieve this?
View 10 Replies
View Related
Feb 1, 2009
I am using 'Dim' to define a string for SheetName, a variable.
I have tried with no success various ways to try and paste data to a sheet with this variable.
I know the variable is correct as I am using it to make a new sheet then rename it and also move it.
View 9 Replies
View Related
Jul 27, 2006
I need a macro which goes thru the columns A to K, picks up the value in the filled cells and paste them all in 1 column on another sheet, like sheet2 A1 onwards. Attached is an example of the file.
View 8 Replies
View Related
May 12, 2014
I am using the following code to paste an excel generated chart into ppt. How do I adapt it such that it only pastes the charts from sheet2 and not the entire workbook?
[Code] .....
View 11 Replies
View Related
Aug 14, 2012
I have the following code (which works fine when the sheet isn't hidden)
Code:
FinalRow2 = TtlRev.Cells(Rows.Count, 1).End(xlUp).Row
TtlRev.Range("AM6:AO6").Copy
TtlRev.Range(Cells(6, 39), Cells(FinalRow2, 41)).Select
ActiveSheet.Paste
However, I need to hide the sheet, but it doesn't work because you can't use .select on a hidden sheet I believe. Any way of doing this to a hidden sheet?
View 4 Replies
View Related
Jan 10, 2014
I am trying to create a macro that will take inputs entered into a range of cells in 1 worksheet and then paste it into another range on another sheet. The target range is selected based on a condition and once the values have been pasted then the target range should be locked.
I have been trying the following code but keep getting the Run-time error 1004 telling me that the PasteSpecial method of range class has failed. Can some one explain what I may be doing incorrectly?
Code:
Dim i As String
i = Range("Month").Value
Select Case i
Case "January"
Sheets("INPUT SHEET").Select
Range("Input_In").Select
Selection.Copy
[Code] .........
View 1 Replies
View Related
Jul 3, 2008
if anyone has some vba code that can help with keeping latest duplicates when pasting to another worksheet.
Basically, I have a worksheet with YTD data and another worksheet with data for the current month. I need to paste the data from the Month to the YTD sheet. However, if the data (a reference number in column A) already exists, the row from the Month data should be pasted over the duplicate in the YTD sheet, and if it doesn't exists, it should paste it as a new row.
At the moment I'm just copying the whole lot to the YTD sheet and then try to remove duplicates, however, once it's pasted, I don't know which ones to remove.....
View 9 Replies
View Related
Jul 22, 2006
I just need a code that will copy items in a row from column A:F...
View 5 Replies
View Related
Nov 9, 2013
I have values printed between from c5:c14 ( not always all 10 will be filled up. sometimes can be 1 also). Wanted some code or method where any value in this gets pasted in the second sheet from A1 onwards. Also whatever number of times I paste it keeps on adding one below the other in the A column in the second sheet . Important : If there are blank cells between one value and other in the working sheet, then it should eliminate the blank space and paste it in sequence in the second sheet. i.e if there are values in c5 , c8 , c14. Then when pasting in the second sheet it will be in A1,A2,A3.
View 3 Replies
View Related
Jul 16, 2014
I need to figure out a way to search for a string in column D and then paste the entire row that string is in to a new sheet. I'd like for this to continue until I paste all of the rows of that string into the next sheet. This is what I got so far, and when I run it, it says an error occurs and then it says either press enter or paste. After I hit enter, it pastes the first row to the next sheet, but that's it. Also should I be doing this with a button?
Code:
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
[Code]....
View 3 Replies
View Related
Aug 5, 2014
I'm trying to copy a range of column headers from one worksheet and paste them in another using a macro
The source range starts at Q1 and runs along row 1 for a variable number of columns each time.The destination cells start at B1 and will run along row 1 for the same number of columns.
I've got as far as counting the number of source columns
PHP Code:
Dim LastDate As Long
LastDate = Sheets("Edited data").cells(1, Columns.Count).End(xlToLeft).Column
how to paste to a changing number of columns
PHP Code:
Sheets("Edited data").Range("Q1", cells(1, LastDate)).copy Destination:=Sheets("Variety Total").Range("B1", ????)
View 2 Replies
View Related
Dec 18, 2008
doesnt work when recording a macro because the group name changes each time I group the pictures (visios) and the picture names change depending on what is on the sheet at the time.
Is there a way to group every picture on a sheet1 (excluding macro buttons), then copy them to sheet 2?
View 14 Replies
View Related
Jan 1, 2006
I am creating a data tracking workbook. Information is entered into cells on
different sheets, then the tallies are pulled to one easier to read sheet.
In creating the tally sheet, I am setting the cell formulas. The cells
referred to are constant, only the sheet referred to changes. For example,
the cell might read
='FP 1'!$I$25
and the next one should read
='FP 2'!$I$25
....and so on. I would like to be able to paste the formula and have the
referred to sheet change so that I do not have to go through and make
hundreds of little changes.
View 10 Replies
View Related
Sep 27, 2013
Code:
Sub Copy_and_Layout()
Dim ws As Worksheet
ThisWorkbook.Activate
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
If Left(ws.Name, 5) = "Sheet" Then
Range("B2:G" & ws.UsedRange.Rows.count).Copy
Sheets("Rev New").Range("B1").Insert xlDown
End If
Next ws
End Sub
So far I have this as a code but it seems to only paste the last sheet at the top ....
View 3 Replies
View Related
Jan 16, 2007
I'm writing code which will copy multiple sheets of information in a workbook (from a report) and trying to paste it onto one sheet in another document where I can further manipulate the data. Here's my code, I've made some mistakes, and I've just been banging my head against the screen try to fix it. I'm erroring at ActiveCell. Offset(1, 0).Range("A1").Select. I'm trying to paste it all in one sheet at the next blank cell in the sheet and then close the document after it's all been pasted.
Sub OpenDataSheet()
'Opens CSS Report
Workbooks.Open Filename:="C:Documents and SettingseichornjDesktopTest and Destroy est and destroy CSS.xls"
'Delete first two sheets of CSS report
Application.DisplayAlerts = False
Worksheets( Array(1, 2)).Delete
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Select
'Delete unneeded rows of CSS report
View 7 Replies
View Related
Nov 13, 2008
I have a spradsheet that I've formatted using merged cells. The spreadsheet has several columns. Into these columns I must enter raw data extracted from our SAP data base. The extraction worksheet has no merged cells. So bottom line, I have a column of say, 30 non-merged cells and I want to paste that column into my formatted spreadsheet that has 30 merged cells of thre cells a piece (90 cells total).
So far I have been unable to do this. If I've attached it correctly, the sample attached
MERGE EXAMPLE.xls
View 7 Replies
View Related
Jul 24, 2013
Usually if I filter and perform some kind of operation on the cells displayed by the filter it is only these cells affected. For example, if I run a filter that shows 10 rows and I Paste a value into say, Column A, of all 10 Rows then remove the filter and look through all my rows, only the 10 cells displayed during the filter contain whatever I Pasted in.
However, sometimes when I do this many rows that were not displayed as part of the filter are affected. For example, I Paste something into my 10 filtered Rows and after removing my filter I find that whatever was Pasted into the 10 cells is now in 200 cells.
How can I be sure that any changes I make to a set of filtered cells are ONLY applied to those cells?
View 4 Replies
View Related
Mar 31, 2014
I have a sheet made into a form with some check boxes included. If I select all the relevant columns, create a new sheet, and paste into cell A1 - all of the information carries over just fine. You can still select cells and check the boxes in the new sheet.
However, when I record this action as a macro, using the macro produces a picture of the selected columns instead of actually copy/pasting the information. I can't click on the cells or check boxes. I can only move the picture around within the new sheet.
View 7 Replies
View Related
May 1, 2009
I have code that selects and copies data from one sheet onto another sheet. the sheet that is getting data copied too has borders defined. When i run the code i get a debug error. Here is the issue.... when i remove all the borders it works fine. I have tried everything i can think of to solve the problem but have had no luck. anybody have an idea what is causing this. i attached the workbook file so you can see what is going on.
View 2 Replies
View Related
