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

Macro To Clear Row Data Then Shift Remaining Data Up To Empty Rows?

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

Clear Data Macro

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

Macro To Clear Data

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

Mutiple Clear Data Macro

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

Clear Data Not Format With Macro

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

Macro To Clear Some Data Within A Cell But Leave Other Data

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

Macro To Clear Data In Specific Columns

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

Macro To Record And Enter Data In A New Row

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

Macro To Enter Data Without Using Input Box

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

Macro Record The Enter The Data

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

Macro To Enter Date And Paste Data

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

Macro To Enter Data By Clicking A Button?

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

Enter 4 Columns Of Data In Row Then Shift After Hitting Enter?

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

Clear List Macro For Data Validation - Multiple Lines In Table

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

Clear Macro NOT Clear Formula?

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

Add Clear Content To Macro?

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

Macro To Clear Cells

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

Macro To Clear Contents.

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

Clear Cells To Right IF Macro

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

Macro For Min Value Then Clear Contents

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

Clear Contents Macro

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

Macro To Clear Zero's In Columns

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

Clear Data Depending On User Input And Move Cells Forward Without Deleting Data Table

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

Keep Macro From Clear Entire Sheet

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

Cell Protection & Clear Macro

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

Macro Button To Clear Certain Cells

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

Macro To Clear Colums In Range

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

Macro To Clear All Contents Of Spreadsheet?

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

Macro To Clear Contents Of Workbook

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







Copyrights 2005-15 www.BigResource.com, All rights reserved