More Conditional Row Copying Using Macros
Jun 13, 2006
You assisted me in writing a macro to move rows from one worksheet to another based on column criteria. I need a slight improvement to the code, ie it should only delete the rows after moving them if the rows are those that matched the criteria and not those that simply had blank cells for criteria after the rows moved are deleted.
Private Sub CommandButton1_Click()
Dim c As Range
Dim count As Integer
count = 0
If MsgBox("Are you sure you want to move rows to the archive ?", vbOKCancel, _
"Archive") = vbCancel Then Exit Sub
On Error Goto catcher
For Each c In Range("J1:J" & [sheet1].[a65536].End(xlUp).Row)
If c = "yes" Or c = "good" Then
count = count + 1......................
View 2 Replies
ADVERTISEMENT
Aug 8, 2014
I need a code that will copy a list in the same order from different sheets and will paste special and transpose it on the summary sheet.
View 1 Replies
View Related
Mar 12, 2012
I've managed to be able to create a macro to make a copy of a worksheet within excel.
However, when that copy is create all the macros are lost. If it is not possible to copy all the macros that are within the "Original Hours" sheet, I at least need a macro that will take people back to the original hours sheet (we have disabled sheet tabs, so need the macros to navigate through the worksheet).
Code used is below
Private Sub Hours_Click()
Sheets("Original hours").Copy After:=Sheets("Original hours")
ActiveSheet.Name = "Update hours"
ActiveSheet.Unprotect "PASSWORD"
With ActiveSheet.UsedRange
.Value = .Value
Sheets("Update hours").Select
End With
End Sub
View 7 Replies
View Related
Sep 13, 2006
I Have a sheet called "Main" in Which I use a Doubleclick Private function. When the user Double clicks on a row, I want excel to take note of the row number that was clicked, then move to a different sheet and copy the row in the new sheet (Called "Data") with the same row number as the Target row that was Double Clicked.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 4 Then
Sheets("Data").Select
Sheets("Data").Rows(Target).Copy
Sheets(" Chart Data").Select
Sheets("Chart Data").Rows("2:2").Paste
End If
End Sub
I know The (Target).Copy line is incorrect but I dont know what the correction is.
View 2 Replies
View Related
Jul 4, 2008
I have only recently started playing around with macros, and am slowly getting into them. I do however still struggle a bit to ‘read’ them. Having played with formulas for years I can generally translate a string of formula text into English, like ‘if this cell value is greater than that cell value, then do this, if it is not then if it is equal to that cell value, do that, if not return 0’. Babbling like a child basically.
With visual basic I have managed to record some handy macros and then tweak them a little manually but I am still struggling to follow it going through it step by step reading it like a formula. So I hope you won’t mind me asking some very stupid questions. I mean well; I’m just a bit slow.
At present I am trying to do two different things on two different sheets, and I was wondering if perhaps one of you could nudge me in the right direction.
1:
I am trying to insert a blank row above every row that has a certain word in column B.
So basically ‘find value “Example” in column B, and when you find it, insert an entirely blank row directly above it’.
2:
Is a bit more challenging. I want to change the colour of a cell if the value in the cell corresponds with the value of another cell in the same column.
For example, I have a long list of surnames in column A. When I add ‘McNeil’ at the bottom, I would like to be able to run a macro that checks if the name McNeil appears anywhere else in column A, and if it does, that it changes the colour of the cell.
Preferably both of the cells that say McNeil, but one would do very nicely indeed.
If that is possible, I wonder if it is possible to do the same with the first name in column B, but only if there was a match for the surname in column A on the same row. So, if McNeil does not appear in column A, don’t bother, but if it does, does the corresponding first name appear in column B?
If both of that is possible, the next step would obviously be if McNeil appears in column A (say twice, once in A123 and once in A678), do cells B123 and B678 match as well?
View 14 Replies
View Related
Oct 11, 2013
I am totally new to Macros. I need a Macro which should format a column based on the value of another column.
Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?
View 9 Replies
View Related
Apr 7, 2009
Each row in my file describes a country and a year. I have data for some years; I would need a way to copy these observations to the following years until there is a new value (conditional to being same country). On the other hand, if there was no value for that country, I would need a ".".
View 4 Replies
View Related
Nov 24, 2011
I am having trouble copying conditional formatting down my spreadsheet. When i copy it the number dont change. the formula i have in the conditional formatting is =O3
View 9 Replies
View Related
Aug 26, 2009
Cell (A1) is formatted differently from the other cells of the same column. I added to it a conditional formatting (based on a formula) which I want to copy [alone] to the rest of the cells, without getting the other different formatting of cell (A1) copyed as well.
View 9 Replies
View Related
Nov 14, 2012
I have data like this:
James English
Bea English
Bea Math
Pat English
Sarah Math
Sarah Geography
Sarah Art
What I want is
James English
Bea English Math
Pat English
Sarah Math Geography Art
I have ~350 rows of subjects. Is there an efficient way of doing this? At the moment I'm cutting and pasting.
View 6 Replies
View Related
Feb 1, 2013
I have a set of values on Sheet1. These 'original' values will never change.
I then have a set of values on Sheet2. The 'updated' values get updated every month.
On Sheet3 I want to show the updated values again, pretty much a copy of Sheet2, but where a value is higher than that on Sheet1 or lower than a value on Sheet1 I want to use conditional formatting to show this.
Is there a simple way to use conditional formatting in such a way? I have a long list of values so don't want to individually create a rule for each cell.
View 6 Replies
View Related
Sep 1, 2009
I have one excel 2007 workbook with three sheets in it.
Sheet 1 is a master sheet with 1858 rows and 45 columns of data.
Sheet 2 has the qualifier data, that is, one column with 255 unique 6-digit integers each in its own cell.
Sheet 3 is a result or output sheet that is currently empty.
Here is what needs to be done.
Compare all of the 255 unique 6-digit integers from Sheet 2 with the integers in column number 15 (or Column O) of Sheet 1.
If there is a match then copy that entire row from Sheet 1 into Sheet 3.
View 9 Replies
View Related
Jun 20, 2006
i want to copy data from one excel workbook to other (or ever between diff sheet wud do ) on basis of search on a complete column by picking data one by one from other column & on finding the exact match copy 4 columns form one workbook(or sheet) to other. this has to be done 4 a bulk of data (1000+) so manually is next to impossible.
View 2 Replies
View Related
Jul 27, 2012
I have :
A1 = "Banana"
A2:10 = blank cell
B1 = xxxxxxx (always 7 digits) (number)
B2:B10 = xxxxxxx
How to fill the range of A2:A10 with "Banana" only if the value in Column B is having a value (or not blank)?
The second one is, how to delete entire row if the value in column B is text?
View 9 Replies
View Related
Jul 9, 2013
I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
View 9 Replies
View Related
Mar 19, 2013
How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
View 6 Replies
View Related
May 2, 2012
I have a workbook containing two worksheets of staff training records.
The first work sheet contains a list of names and the dates they completed various training courses. I have used some simple date based formulas and conditional formatting to colour-code their name depending on whether their earliest retraining due date has passed, is coming up in the next few weeks, or is a long way off. The data is set out alphabetically, one person per row of data.
The second sheet contains the same list of names, but each person's data is split across two consecutive rows. The cells in column A which contain the staff names are merged in pairs so that the name heads both rows of data.
I want the colour coding of the merged name cells in sheet 2 to automatically copy the colour coding applied to the single name cell in sheet 1, but don't know how.
I'm using excel 2010.
View 3 Replies
View Related
Nov 5, 2013
I'm a 2007 user. I am trying to conditional format a range of F1:J10, where F1 will be conditionally formatted relationally to A1. G1 is relational to B1. H2 relational to C2, etc., all the way down to J10 relational to E10. My conditional format formula in cell F1 is >A1*2 (will format F1 as orange). I want to be able to copy the conditional formatting over so the formula in J10 for example would be: >E10*2 (will format it orange).
I omit the "$" in the conditional format formula in an attempt to prevent it from being absolute. But every time I copy the conditional formatting over to the other cells, the formula within those cells remains identical to the formula in the original cell.
View 6 Replies
View Related
Jun 6, 2012
Excel 2007.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager
Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager
Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
View 2 Replies
View Related
Nov 30, 2008
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
Acct #
Date
Time
City Called from
State Called From
Called #
City Called
State Called................
View 17 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Jul 28, 2008
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
View 9 Replies
View Related
Jul 23, 2005
What is the commands or script for deleting a macro automatically using
another workbook macro.
View 9 Replies
View Related
Jul 14, 2008
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies
View Related
May 15, 2014
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
View 2 Replies
View Related
Dec 12, 2007
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 Related
Apr 27, 2014
i have created a table. when i m editing new row then formulas is copying bu value is not copying ?
View 3 Replies
View Related
Dec 14, 2009
I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
View 11 Replies
View Related
Apr 13, 2007
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
View 2 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related