stop macro repeating

[Gordon Scott]

I Have found this macro that work very well for me but the only problem is that every time i run the macro it repeats the data it has already transfered over. What i need it to do is when i enter either pending or paid in the main page and run the macro the respective sheets reflect this, so in theory the pending sheet and the paid sheet added together should equal the main sheet whenever i change anything and refresh it. can anyone amend the macro i have attached to do this.

Cheers:beerchug:

Gordon Scott

[mdmackillop]

Hi Gordon,
The only simple way is to insert a Key column to record the previous transfer. Something like

If strStatus <> "" And Cells(lngRowSource, 1).Offset(, 10) <> "t" Then
Cells(lngRowSource, 1).Offset(, 10) = "t"


You could consider also making the font of Pending or Paid bold, change the cell colour or other such indicator which could be tested.

[Gordon Scott]

Thanks for that but could you tell me where to insert it as i am really new to macros and dont understand much of the code.

Gordon

[mdmackillop]

Sub ProcessRows()

Dim lngRowSource As Long
Dim lngRowTarget As Long
Dim strStatus As String

For lngRowSource = 1 To 25
'sheetnames are in column 11 or K
strStatus = ActiveSheet.Cells(lngRowSource, 1).Value
If strStatus <> "" And Cells(lngRowSource, 1).Offset(, 10) <> "t" Then
Cells(lngRowSource, 1).Offset(, 10) = "t"
lngRowTarget = TargetRow(Sheets(strStatus))
ActiveSheet.Range(Cells(lngRowSource, 1), Cells(lngRowSource, 15)).Copy _
Sheets(strStatus).Cells(lngRowTarget, 1)
End If
Next

End Sub

[mdmackillop]

To change the position of the "t" column, change the value 10 as required.

[Gordon Scott]

That is working much better now but i still need it to do something else. If i change the word pending to paid on the main sheet it should remove it out of the pending sheet and put it into the paid sheet. How can i run it without the letter "t" appearing. I have attached the updated macro.

Cheers

Gordon

[Charlize]

A modified version with a unique idno. The idea is to look for the unique idno and based on that you can move (not copy) to the other sheet. When using a button to change from pending to paid you can store the number of the id in column L. Based on that you can look for the row with the id in it and move it.

Charlize

[Bob Phillips]

.

[Aussiebear]

MD, is this something very similar to that which was coded for my Cleared and Hold?

This section to set the trigger on sheet ("Main")


Private sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count >1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 1 Then

'Copy data from Main to appropriate sheet

If Target = "Pending" Then DoPending Target

If Target = "Paid" Then DoPaid Target
End IF
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

This next section to copy a row to sheet "Paid" if the word Paid is found in column A of the row


Sub DoPaid (Target As Range)
Dim lRow As Long

lRow = Sheets("Paid").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With ActiveSheet
Range(.Cells(Target.Row, 1), .Cells(Target.Row, 8)).Copy
With Sheets ("Paid")
.Range( .Cells(lRow,1(, .Cells(lRow, 8)) .PasteSpecial xlValues
.Range(.Cells(lRow -1, 1), .Cells(lRow - 1, 8)).Copy
.Range(.Cells(lRow, 1), .Cells(lRow, 22)) .PasteSpecial xlFormats
End With
Application.CutCopyMode = False
End With
End Sub


Next section to copy row to Sheet "Pending" if the word Pending is found in column A of the row


Sub DoPending( Target As Range)
Dim lRow As Long

lRow = Sheets ("Pending") .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With ActiveSheet
.Range (.Cells(Target.Row, 1) .Cells(Target.Row, 8)).Copy
With Sheets ("Pending")
.Range(.Cells(lRow, 1) .Cells(lRow, 8)).PasteSpecial xlValues
.Range(.Cells(lRow-1,1), .Cells(lRow -1, 8)).Copy
.Range(.Cells(lRow,1), .Cells(lRow, 8)).PasteSpecial xlFormats
End With
Application.CutCopyMode = False
End With
End Sub


And for those rows in sheet Pending which are changed to Paid.


Private Sub WorkSheet_Change(ByVal Target As Range)

If Target.Cells.Count >1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 1 Then
'Copy data from sheet "Pending" to sheet "Paid"
If Target = "Paid" Then DoPaid2 Target
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Copy data from sheet "Pending" to sheet "Paid"


Sub DoPaid2(Target As Range)
Dim lRow As Long
DimcRow As Long

lRow = Sheets ("Paid").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With ActiveSheet
.Range(.Cells(Target.Row, 1), .Cells(Target,Row, 8)).Copy
With Sheets("Paid")
.Range(.Cells(lRow,1), .Cells(lRow, 8)).PasteSpecial xlValues
.Range(.Cells(lRow-1, 1), .Cells(lRow-1, 8)).Copy
.Range(.Cells(lRow, 1), .Cells(lRow, 8)).PasteSpecial xlFormats
End With
Application.CutCopyMode = False
cRow = Target.Row
.Range("A" & cRow & ":H" & cRow & ",K" & cRow).ClearContents
End With
End Sub


Of course it will mean that the value in column A will need to entered after columns B to H. or we could change the trigger value to that which is in column k....

What do you think?

[Charlize]

macro to copy from pending to paid (main) en remove entry in pending sheet. you can hit the macro as long as you are in the row that you want to change to paid. when trying to do with a paid transaction you'll get a msgbox.

Charlize

[Charlize]

This one uses a doubleclick event. If you doubleclick on column 1 and row > 1 the macro will check the status. If it isn't pending you'll get notified. Doubleclicking in another column will tell you to click in column A.

Charlize

[Gordon Scott]

Still not working. What i am looking for exactly is if i put either pending or paid in column A on the main page it will copy that row to the respective sheet, which is what it is doing at the moment, but If i change the name on the main sheet from say pending to paid or vice versa it must transfer that row from one sheet to another or if i delete what i have inserted in column A the data will dissapear from the respective sheets. Can this transfer be done without any additional text appearing aswell.:help

Gordon

[mdmackillop]

Hi Gordon,
The simple way to do as you ask is to filter Main and copy the filtered data to the Paid and Pending sheets, overwriting all the previous information. Whether this is apopropriate depends upon what, if anything is being added/modified on these sheets. If they are only for listing the Pending/Paid items, then I'd go this way. Can you advise?

[Charlize]

Still not working. What i am looking for exactly is if i put either pending or paid in column A on the main page it will copy that row to the respective sheet, which is what it is doing at the moment, but If i change the name on the main sheet from say pending to paid or vice versa it must transfer that row from one sheet to another or if i delete what i have inserted in column A the data will dissapear from the respective sheets. Can this transfer be done without any additional text appearing aswell.:help

Gordon
- Things that are paid for ... can they become pending againg ???
- when doubleclicking you haven't got to type a letter. value is changed from pending to paid. If you want you can even insert pending. Check if value in A column exist - if not put pending in it and do the copying. When you want it the other way around. Check for paid - change it to pending - copy to pending - go to paid sheet - filter on idno and delete rows that are visible.
- you can always hide the columns with t and the idno

Charlize

[Gordon Scott]

I am only using the words pending and paid as an example so either sheet name could change from one to another and also revert back to main only if left blank again. Is this possible and if so could someone amend the macro to do this as i have no idea how to alter macros. Your help would be much appreciated.

Cheers

Gordon

[Bob Phillips]

I am only using the words pending and paid as an example so either sheet name could change from one to another and also revert back to main only if left blank again. Is this possible and if so could someone amend the macro to do this as i have no idea how to alter macros. Your help would be much appreciated.

Cheers

Gordon

I did in my response

[Aussiebear]

Sorry if I mislead you...... ( I'll just go back to my corner of the round room)

[Charlize]

Last try. When nothing in column A put pending in it and copy. If pending copy to paid and remove from pending. If paid and double clicking remove from paid and move back to pending. Look at the code you can get for free. Who knows you learn something. To see it hit alt+F11 and look in the sheetcode, the module code, the form code if they are present.

Charlize

[Aussiebear]

Okay, can I put forward another simple suggestion. As I understand it data is entered on the Main sheet and then if Paid is entered in Column A, that row of data is copied to sheet "Paid". If Pending is entered then that data row is copied to sheet "Pending".

On sheet "Pending", if the value "Paid" is entered then that row is copied to sheet "Paid".

Am I correct?

If so then all you need is someone to tidy up this example to force the cleared row to delete, and to create the indexing function.

If this is also correct then I'll leave that to the experts.

Ted

[Charlize]

Hello Ted,

I think Gordon wants to manipulate everything through the main sheet. He needs a worksheet change or so when typing. I've build it with double click events to change value from nothing to pending to paid to pending. With every double click the other two sheets are adapted to reflect the main sheet (pending and paid together give main). When you change the words in the 'select case' code and the 'process rows' code you can put anything you like in those cells.

Charlize

ps. Ted I've tried you code. I would :
- use Ucase to be sure no matter how the input is (paid, Paid, PaiD), something would happen.
- maybe even use trim (you never know with those inputters).
- copy and not move the row.

[Aussiebear]

Hmmm... now I'm really confused.

[Aussiebear]

Okay, I set validation on column A in sheet "Main" to Blank, Paid, & Pending.

Data rows on Main only get copied to either sheets "Pending" or "Paid" but data on sheet "Pending" gets copied and cleared if changed to Paid.

[Charlize]

Nice, when you change the value from sheet pending to paid the entry moves to paid (ok) but what with the main sheet ? It will display pending and isn't altered to paid.

Charlize

[Aussiebear]

Hmmm..... okay just requires a bit more thought here.

[mdmackillop]

Methodology as per my post 13.

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then PP
End Sub

Sub PP()
Dim Arr, p, LRw As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
Arr = Array("Paid", "Pending")
For Each p In Arr
Sheets(p).Range("A2:H500").ClearContents
Columns("A:A").AutoFilter Field:=1, Criteria1:=p
LRw = Cells(Rows.Count, 1).End(xlUp).Row
If LRw > 1 Then
Range(Cells(2, 1), Cells(LRw, 1)).Resize(, 8).Copy Sheets(p).Cells(2, 1)
End If
Next
Columns("A:A").AutoFilter
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[Gordon Scott]

Thanks MD, that works great. Sorry i took so long to reply but i was in Tenerife for a week. My next question to you is now i have the macro i need how can i insert it into a worksheet i have at work? I tried to attach it here to show you what it is but it wont allow me somehow but i could email it to you personally if that is ok with you.

Thanks again

Gordon

[Bob Phillips]

mail it to yourself at work as text in the email, and cut and paste it.