find data not used, find last row and paste(stuck on last part! pls help)

Hi all, I am so grateful for everyone's help here on this board as it took me a far way!

I am working on building summary database which utalizes 2 different data spreadsheets and currently am stuck on last part. I am attaching what I have put together so far, and added detailed notes what I am trying to accomplish inside..

to give quick overview, I have 3 excel tabs:
-total_Summary (where I summarize profits anything for (this month+previous month) and anything that has (nothing for this month but has for previous months)
-this_month_profit
-prior_months_profits

"prior_months_profits" contains information for past 2 years.. every month we sweep monthly data to "prior_months_profits".. to track profits, I combine current month profits with any previous month profits.. any remaining items in prior month profits which has no current month profits I still need to reflect..this is the part where I got stuck.. as I need to find the data, seperate it and then paste it in my summary tab on the last row after where I combined current month with any data that matches to previous month.

I know this might be little confusing, so I made it little more user friendly in the attached file.

Please if anyone could help with some feedback or suggestions it would be greatly appreciated.

Thanks so much

Dan

thanks for the tips Roy.. this is great.. really appreciate it..

I know my notes might be little confusing, so please let me know if there is anything I can clarify.. I tried to make it little more detailed in the actual attached spreadsheet

thanks again

Dan

Roy, the code you changed works great..one question.. what is

Option Explicit

any feedback on the attached file where i need to find and bring in remaining data to last row?

thanks so much

Dan

This is very helpful link. Thanks!
No worries about the attachment.. Whenever you have some time. Thanks again!

Dan

Hi Roy, I am working on amending the code you sent me and running into some trouble..

I should of clarified this, I am copying/pasting data by telling VBA to open one Worksheet(this_month_profit),copy data and then paste data into my different Worksheet(Total_Summary). (I had 3 tabs on my attachment for convenience but should of clarified it)1) Open my original spreadsheet and clear everything..(this is all good and works)

With Sheets("total_summary")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(3, 1), .Cells(LastRow, 7)).ClearContents
    End With

This is where I get stuck... I need to switch back to this_month_profit workbook.. this is why I had below code:

'copy paste ACCOUNT NUMBER
    Windows("this_month_profit_workbook.xls").Activate
    Sheets("this_month_profit").Select
    Range("A3:A" & LastRow).Select
    Selection.Copy
    Windows("testing.xls").Activate
    Sheets("total_summary").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Is there a way to incorporate in the code you provided me below? If I understand correctly, the below code, only assumes to switch between different tabs?

With Sheets("this_month_profit")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(3, 1), .Cells(LastRow, 1)).Copy Sheets("total_summary").Cells(3, 1)
    End With

Hi everyone, just figured will bump the post.. was wondering if anyone had any comments on below..Any advice is very appreciated!

thanks so much
Danny

Hi Roy, let's scratch everything from above..basically to summarize everything I am attaching amended workbooks.. thanks again so much for your time with this..

1) testing.xls
2) this_month.xls (need to change dir name in VBA wherever you save this)

if you run the macro I put together it works fine( at least I think so.. I know it needs some tweaking to make it more effcient).

What I am more concerned is the part that I am stuck on hehe...So I pulled all the profits for this month and matched to any profits with previous months based per "Account Number" and "ID"

Now, I need to find any remaining profits in Prior_month_Profits tab that I did not match with Current Month per account number and ID and transfer them to column G.. (after you run the macro, and look in prior_month_profits tab I highlighted remaining items in red)

the spreadsheet I attached I think breaks it down little bit better

Hi Roy, how is it going? not to bug you.. just curious if you had any comments

Thanks! Danny