Loop?

[superspiker]

The code below works once. I'd like it to run anytime the value changes. I assume this requires the use of a loop.

I have no clue how loops work. Anybody feel like helping me out?

Thanks.

Please Login or Register  to view this content.

[protonLeah]

After the addition of the line indicated, the code will copy the last used cell of Col D on the first sheet to cell B3 on the other sheet. Was that your intent?

Private Sub Worksheet_Change(ByVal Target As Range)
    
Application.ScreenUpdating = False
    Range("D48").End(xlDown).Copy
    Sheets("Financial Summary").Activate
    ActiveSheet.Cells(3, 2).Select
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Checking Transaction History").Activate
    Application.CutCopyMode = False     "<-------- added to cancel out of the copy/paste operation.  "
Application.ScreenUpdating = True

End Sub 

[davesexcel]

where is your target range?
This one works if A1 changes

Please Login or Register  to view this content.

[superspiker]

Yes that is exactly my intent. It is working, however it only copy's the last used cell from Colum D to B3 on the other sheet one time.

I am looking for it to copy and paste as appropriate every time the last used cell is added/updated/changed.

Adding your suggested line of code seems to have no effect: (still only works once)

Please Login or Register  to view this content.

[davesexcel]

Yes that is exactly my intent. It is working, however it only copy's the last used cell from Colum D to B3 on the other sheet one time.

I am looking for it to copy and paste as appropriate every time the last used cell is added/updated/changed.

Adding your suggested line of code seems to have no effect: (still only works once)

Please Login or Register  to view this content.

You didn't answer the question

What range do you want the code to activate??

[superspiker]

I'd like the macro to execute whenever the last used cell in colum D is added/updated/changed.

Does that help answer the question?

[GrishaP]

Superspiker,
Worksheet_Change is supposed to work everytime there is a change in worksheet.

I frankly do not see any problem with the code.

I am assuming though that as a result of the copy and paste operations done there are no changes hapening on your source cells. Because if this happens then you will go into a loop and as per my understanding excel comes out of this loop automatically after some 250 times.

I would check the following:
1. See if there are any static variables else where in the code. if for e.g. you have a static integer which is hitting the max end of its range
2. See if there are any changes happening in the source sheet as a result your copy and paste operations.

Not sure if i have solved your problem.

[davesexcel]

here you go



Range(("D48"), Selection.End(xlDown)).Copy


Sorry abot that

[davesexcel]

whenever there is a change in column D the code executes

Please Login or Register  to view this content.

[superspiker]

Thanks for all the quick responses.

Well Shoot. It's still not workin.

Any more ideas?

Please Login or Register  to view this content.

[davesexcel]

go to this site to find out where the worksheet module is

http://www.contextures.com/xlvba01.html#Workbook

[superspiker]

I was thinking about taking a different approach.

Can someone post code that would make the last used cell of Colum D on the active sheet equal to B3 on the other sheet?

Thanks.

[davesexcel]

This should work

Please Login or Register  to view this content.

change sheet names as required

[superspiker]

Thank you davesexcel!

This is exactly what I was looking for. It works perfectly.

Please Login or Register  to view this content.