Macro - Enter Data In Next Clear Row After Every Run
Jun 27, 2014
I have a macro that
- copies rows from one sheet into another sheet based on a set of criteria
- the very very very first time the macro is run, the first row of data should be put into row 7 (formatting reasons)
- however, every other time it's run it should paste into the next clear row.
But, what is happening is that, each time its run, it puts the data into row 7 => overwriting data.
I've used the offset and counter function but perhaps I need to put it in the header of the code to get it working?
My code pasted below:
[Code] .....
View 4 Replies
ADVERTISEMENT
Apr 15, 2014
I'd like to have a sheet with multiple columns of data (say A thru K for instance.). Id like to reserve column A for ONLY imputing an X. The rest of the columns b-K would have data in the cells. I'd like to have a macro that when it saw an X in column A, would copy all of the data in cells B-K in that row, paste it into the next empty row of a second sheet (for history tracking), then go back to the original sheet and continue looking for additional "X"'s and repeat. Once all of the X's were copied, it would "clear" (Not delete because some of the cells would have formulas in them that would need to remain for future use.) the cells based on the "X" then finally move all of the remaining data up to the empty rows to fill in the empty rows. This last piece would be more for esthetics to have a clean looking sheet.
View 1 Replies
View Related
Jul 5, 2009
I've reformatted the spreadsheet, and now some of the data are in merged cells.
The code lnow ooks like below, (Which I thought would work) but it doen't work ,,, and I'm not sure why
Sub Clear_Risk_Data()
If Range("J5:K5,D12,G11:H11,M11:O11") = Empty Then
MsgBox "No data to Clear."
Else
Range("J5:K5,D12,G11:H11,M11:O11").ClearContents
MsgBox "All Data Has Been Cleared", vbInformation
End If
End Sub
My sheet (An excel 2007 macro enabled version), I've just uploaded here, in case anyone needs to view it, I just don't know why it won't work?
[url]
There are just 4 boxes to clear,
Box 1,, is cell J5 & K5
Box2 is cell D 12
Box 3 are cells G11 & H11
& Box 4 are cells M11, N11 & O11
View 9 Replies
View Related
Jan 5, 2010
I have a spreadsheet that I enter data for cash flow purposes on a daily basis. At the beginning of each month I need to clear out the data containing values only as well as values beginnining with an = for eg 20000+50000+25000+74000 etc, but not formulas and text
I need the macro to clear the values , including data that has been added up as explaimned above from row 9 onwards and from column C
View 9 Replies
View Related
May 16, 2009
I have made a spreadsheet but would love to be able to incorporate multiple 'Clear Data Buttons', (Macros)
that clear cell data.
I have attached several screen shots of my spreadsheet, with the URLs listed below.
(Images 1-6)
http://tinyurl.com/qba57l
http://tinyurl.com/od9upb
http://tinyurl.com/ovaeej
http://tinyurl.com/pctsvj
http://tinyurl.com/oc2qqm
http://tinyurl.com/qdhzge
Spreadsheet (xls & xlsm) in zip format
http://tinyurl.com/qczlap
It is a trading order sheet that I want to use, but also upload to a blog that I have just
started, relating to trading as a free download, hopefully it may benefit some users/visitors etc.
OK, regarding the macros.
I'm not quite sure how it is best to do it.
In total there are 13 'Clear Buttons.'
11 are 'CLEAR DATA' Buttons
12 is a 'Clear ONLY ORDER SHEET DATA' Button.
Finally 13 is a 'Clear All DATA!' Button
I think it's best if I give the cell location of where I would like the macro buttons located and what data they clear.
Clear DATA - Button 1 - (Is in Cell F3) - To Clear Data, Columns D,E,F & Rows 4 to 13)
Clear DATA - Button 2 - (Is in Cell N3) - To Clear Data, Columns G to O & Rows 4 to 13)
Clear DATA - Button 3 - (Is in Cell U3) - To Clear Data, Columns S,T & U & Rows 4 to 13)
Clear DATA - Button 4 - (Is in Cell F15) - To Clear Data, Columns D,E,F & Rows 16 to 25)
Clear DATA - Button 5 - (Is in Cell N15) - To Clear Data, Columns G To O & Rows 16 to 25)
Clear DATA - Button 6 - (Is in Cell U15) - To Clear Data, Columns S,T & U & Rows 16 to 25)................................
View 8 Replies
View Related
Nov 7, 2009
I have a worksheet (“A”) that is populated from a second sheet (“B”). The data on “A” changes as necessary using another Macro. Most of the data on sheet “B” is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet “A” to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes. Here is the Macro to clear the data.
View 3 Replies
View Related
Dec 24, 2009
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
View 7 Replies
View Related
Apr 19, 2013
I have set up a macro to clear data in COl A from row 2 onwards. I need to amend the code to clear the data in the following columns from row 2 as well
Col C to I and Col L to O
Sub ClearData()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
With Range("A" & i)
.ClearContents
End With
Next i
End Sub
View 4 Replies
View Related
Mar 16, 2009
I am trying to have a macro send the data from sheet 2 to sheet 3, record the data then next time shift down a row and enter the new data below and below every time new data is entered.
I have recorded a macro, but I am stuck with the recording the data and then returning to a new row.
View 9 Replies
View Related
Aug 25, 2012
I need a simple macro to enter data for hours worked, but don't like the inputbox.
I need to enter the Emp Nbr in B1.
AM in Time in B3
AM out Time in B4
PM in Time in B7
PM out Time in B8
I then write down the hours worked and
Return to B1
When I enter an new number, I want it to put 0 in B3,B4,B7 and B8
(see attached) Oops can't post. There is probably a better way to calculate the sub-totals, but this works except on an enter of 1200
View 5 Replies
View Related
Apr 26, 2007
I have designed a project sheet that shows Project,tasts,start & end dates, with cells being highlighted via conditional formatting using the between format, What i would like is for the macro that i recorded to enter the data(which is c/f to a new row, At present when i run macro it reruns on the same row,the filldwn method dosn't work either, For design layout i have based it on microsoft project
View 9 Replies
View Related
Feb 15, 2014
On Sheet1 I want a macro that looks for the last used cell in Column D and then put tomorrow's date into column A, 2 cells below where the last used data was found in col D.
I then want to copy the entire contents of sheet2 and paste it into the cell below where we enter tomorrow's date.
View 5 Replies
View Related
Jan 20, 2014
I have two buttons in my excel ("Fund Set up" & "Broker Set up") sheet1. so what i need is when i click the button named as "Fund set up" a input box should appear as "Enter the volume" and the user has to input the volume..For ex, user has entered the volume as 2. After that a line of data should be populated automatically from col A2 to col E2 as below.
Similarly if i click the "Broker set up" button the same action should happen but the activity should be populated as "Broker set up".
S.No Date Activity No.of Req Type of Action
1 21/01/2014 Fund set up 2 Input
2 21/01/2014 Broker set up 4 Input
So whenever i click any of this two buttons a input box should appear to get the volume and once the volume is entered by the user an entry should be populated as above in a new line.
View 3 Replies
View Related
Jun 1, 2014
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[Code].....
View 4 Replies
View Related
Aug 1, 2014
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
View 2 Replies
View Related
Aug 15, 2014
I'm having a problem with a macro clearing a formula in a cell. I have the same type of cell that doesn't have the problem but I can't find the difference between the 2 cells or difference in vb that's making it happen. I have to intentionally cause this to happen but don't see why it's happening. Do I need to attach workbook and describe what's happening? I have been copying and pasting from different sources as well as paying to have it created/started but it was expensive(for me) and I make nothing off of it, just use it at work. I am not proficient in Excel or vb but I'm desperately trying to learn as I go so as not to fork out a few hundred dollars again.
here's atleast the vb for the macros:
VB:
Sub clearclientinfo() '
'
'
'
Sheet5.Select
[Code].....
View 9 Replies
View Related
Dec 4, 2012
i have the following macro when cell value in column "K" changes, it distributes the data accordingly.
however, the issue is, if i change the value in K (which is a date), so another value, it leaves the distribution in tact....i want to apply "clear content" and then make the macro re-apply.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Address, 3) = "$K$" Then
Dim srt As String
[Code].....
View 9 Replies
View Related
Nov 30, 2006
I have data in columns a,b,c and d from row 1 to about 200 right now. Every week another 9 rows are added. I wanted to create a macro that will delete the last 9 entries in each column.
View 9 Replies
View Related
Aug 20, 2008
I'm trying to figure out code to have only the contents of cell 'a2' cleared when the contents of cell 'a1' is changed. The issue is that both cells are derived from lists and I don't want the formats cleared.
View 9 Replies
View Related
Jun 5, 2009
if I had text in column A, I would like to clear all the cells to the right and leave the text in column A.
Also, if I had text in column A and B, I would like to clear the cells to the right.
View 9 Replies
View Related
Sep 30, 2009
I need code for a macro that will find a min value in a particular column and based on that min value clear the contents of other cells for that min value.
For example, I have dates in column E such as:
200907
200906
200902
200809
200803
200710
200707
Therefore, I need to find all records for 200707 and clear the contents in other cells.
I can't hardcode the min date because that can change from month to month when new files are created. As can the number of rows.
I'm using Excel 2007.
View 9 Replies
View Related
Jul 18, 2006
I have a protected worksheet in which certain calculations are performed based on certain inputs.
For example, let's take Column E:
Starting in cell E4, I have cells in that column which are input cells and further down the column are calculated cells which are locked and whose formulas are hidden.
I was looking for a macro which would go down col E and clear contents of all unlocked cells without messing up any of the protected cells.
Also, there are certain cells in which I want to clear contents and some cells that I want the macro to insert a "0" value.
The input cells are non-contigous.
View 7 Replies
View Related
Jul 21, 2007
I need a macro that will clear out all zero's in columns D8:O?
The amount of rows vary, but should never exceed 1000. I'am not wanting any cells to shift. Just the contents deleted.
In other words, if e5 contains the value 0, I need it to not contain anything.
The exception is the total row.see file for example.
View 7 Replies
View Related
Oct 25, 2012
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
View 3 Replies
View Related
Feb 25, 2014
This macro clears all the contents of each sheet to which it refers, however, after putting it to use I find that I need it to clear only columns A-G starting at row 10. I am not sure how to modify it to do so though. Actually it would be even better if it would only clear columns A, B, C, E, F, and G.
View 14 Replies
View Related
Oct 14, 2008
when i password protect my sheet it doesnt let my clear cells macro work on the cells i need it to clear why is this and what way is there around this completes my complex sheet.
View 2 Replies
View Related
Jan 26, 2009
My Workbook with a macro to clear ONLY the highlighted Cells.
The Highlighted Cells can be found on the sheet "Line 5"
View 11 Replies
View Related
Jun 19, 2009
I am trying to create a marco that clears the columns of a named range if the sum of the cells in the range is zero.
View 3 Replies
View Related
Nov 16, 2011
macro code to clear a spread sheet of all of its content? (The extent of the content can vary.)
View 2 Replies
View Related
Dec 8, 2011
I have a workbook that I re-use on a weekly basis. The header information must stay intact (Rows 1 and 2). I recorded a macro that would clear all tabs in the workbook, and that macro is:
Code:
Sheets("Sheet1").Select
Rows("3:1865").Select
Selection.Delete Shift:=xlUp
Range("A3").Select
Sheets("Sheet2").Select
Rows("3:1865").Select
Selection.Delete Shift:=xlUp
Range("A3").Select
Which made me curious if there was a VBA code to say "Clear Worksheet" as opposed to having to explicitly list the cells that need to be cleared?
View 6 Replies
View Related