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
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 .
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
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.
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 .
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
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
Sub collectpostedpending()
Dim typ As Variant
For Each typ In Split("Posted,Pending", ",")
Sheets(typ).Cells.Find("Na
ActiveSheet.Cells.Find(typ
Next typ
End Sub