Need vbcode to paste data from one worksheet

Need to copy data from one worksheet to another based on Particular value in the Cell.I am attaching the format and how the copy should work.

I have three worksheets named Report,Posted and Pending

On the Report work sheet i will have two cells with values (Posted and Pending respectively).

The data from Posted worksheet has to get copied below the Posted Cell and Pending has to be pasted below the Pending Cell

The Cell numbers of posted and pending is dynamic and will vary from report to report

I am attaching the format of the report .It would be great if someone can provide me a vb code which does this.

Thanks,
Chaitu235
TEST_FORMAT_REPORT.xlsx
Chaitu235Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Try

Sub collectpostedpending()
    Dim typ As Variant
    For Each typ In Split("Posted,Pending", ",")
        Sheets(typ).Cells.Find("Name", , , xlPart).CurrentRegion.Copy
        ActiveSheet.Cells.Find(typ, , , xlPart).Offset(1).Insert Shift:=xlShiftDown
    Next typ
End Sub
Chaitu235Author Commented:
Can you explain what is typ here?
Saqib Husain, SyedEngineerCommented:
Did it work?

typ is a variable to do the process
once with posted
and then with pending
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Learn More!
Chaitu235Author Commented:
It worked on the dummy sheet i sent....the requirements have changed

1) The requirement is Everytime data is copied the contents have to be cleared in the Report Sheet under Posted and pending cells Respectively.
2) The data has to get copied to Report worksheet only when Report sheet is active.

I am atatching an updated version of the spreadsheet I received from my user.can you please help me on this .

Thanks
TEST_FORMAT_REPORT.xlsx
Chaitu235Author Commented:
Please take a look at the updated spreadsheet and let me know if you have any solution.
Saqib Husain, SyedEngineerCommented:
Try

Sub collectpostedpending()
Dim typ As Variant
For Each typ In Split("Posted,Pending", ",")
    Sheets(typ).Cells.Find("*", , , xlPart).CurrentRegion.Copy
    ActiveSheet.Cells.Find(typ, , , xlPart).Offset(1).Insert Shift:=xlShiftDown
Next typ
End Sub
Chaitu235Author Commented:
I want to clear the contents in the report tab everytime the data is pasted there can you please help on that..
Saqib Husain, SyedEngineerCommented:
Sub collectpostedpending()
Dim typ As Variant
Dim typpos As Range
For Each typ In Split("Posted,Pending", ",")
    Set typpos = ActiveSheet.Cells.Find(typ, , , xlPart)
    If typpos.Offset(1) <> "" Then
        typpos.Offset(1).EntireRow.Insert
        typpos.Offset(2).CurrentRegion.EntireRow.Delete
        typpos.Offset(1).EntireRow.Delete
    End If
    Sheets(typ).Cells.Find("*", , , xlPart).CurrentRegion.Copy
    typpos.Offset(1).Insert Shift:=xlShiftDown
Next typ
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chaitu235Author Commented:
Excellent work .Thanks for the help
Saqib Husain, SyedEngineerCommented:
YW
Chaitu235Author Commented:
Syed, When the data is copied it has to get copied from 2nd row and not first row onwards.can you tell me what needs to be done to achieve this.
Saqib Husain, SyedEngineerCommented:
Try

currentregion.offset(1).copy

instead of

currentregion.copy
Chaitu235Author Commented:
Hi Syed,

I have a question for you..if i have a formula on Column B on the starting cell i want that formula to apply for every cell in that column when data gets pasted .Can you tell me how can i achieve that.
Saqib Husain, SyedEngineerCommented:
Can you be a bit more specific.

Where is the formula
What is changed
Where to paste the formula
Chaitu235Author Commented:
the formula will be there on the main tab where the data will get copied it will be there on say H  column.We are pasting data from A to G columns.The formula on H will do a vlookup based on value in A column.

Now if i have the formula on say H1 cell and paste the data in A to G columns on 20 cells .I want to know if the formula which i have on H1 can be automated to be executed till H20 .

Please let me know.

Thanks,
Chaitu235
Chaitu235Author Commented:
Syed i have a question.I wrote the macro code you provided on Worksheet_activate.Can you please let me know how to activate the sheet by default in the code so that the user need not click the Report sheet for the copy to happen
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.