Macro to find specific text in column and copy certain cells in same row to different sheet

[jlevesquire]

Hey all,

Although I am not a complete newbie to Excel, I have no clue on how to work macros or VBA, so please bare with me if this doesn't make sense or is super easy.

I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date
Column D = loan# A
Column E = loan# B
Column F = status
Column H = followup needed
(Columns A,C, and G aren't important for the current need)

What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.

I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.

The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).

I already have the tracker sheet set up and ready to go with the spaces as follows:
Column G&H = Merged cells where due date will need to go
Column I&J = Merged cells where loan# A will need to go
Column K&L = Merged cells where loan# B will need to go
Column M thru S = Merged cells where followup needed will need to go

Hopefully this is understandable, if anyone can help me or has any questions to help clarify please let me know.

 

[NikiCboy]

Hi jlevesquire,

I was just wondering what tweaks you had to make to the macro to make it run properly?

I am trying to do a similar thing by dropping data from from a seperate worksheet onto a summary sheet.

Thanks.

 

[Yogibear88]

Hi, I've a similar problem. Trying to copy and paste the cells as value.

Tried modifying the code to below but could not run the code.

Error msg shows "Expected end of statement"

Sub Copy()

Application.ScreenUpdating = False

Sheets("SUMMARY REPORT").Select

Dim wsSource As Worksheet
Dim setrng As Range
Set wsSource = Sheets("MAIN DATA SHEET")

Set setrng = wsSource.Columns("BQ:BQ")
setrng.AutoFilter Field:=1, Criteria1:="PENDING"

wsSource.Range(wsSource.Range("D11").Offset(1), wsSource.Range("D11").End(xlDown)).Copy _
Sheets("SUMMARY REPORT").Range("K" & Rows.Count).End(xlUp).Offset(1)
wsSource.Range(wsSource.Range("J11").Offset(1), wsSource.Range("J11").End(xlDown)).Copy _
Sheets("SUMMARY REPORT").Range("L" & Rows.Count).End(xlUp).Offset(1)
setrng.AutoFilter
    

Application.ScreenUpdating = True

End Sub

' changed the above lines to 
wsSource.Range(wsSource.Range("D11").Offset(1), wsSource.Range("D11").End(xlDown)).Copy _
Sheets("SUMMARY REPORT").Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
wsSource.Range(wsSource.Range("J11").Offset(1), wsSource.Range("J11").End(xlDown)).Copy _
Sheets("SUMMARY REPORT").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

'or 
wsSource.Range(wsSource.Range("D11").Offset(1), wsSource.Range("D11").End(xlDown)).value = Sheets("SUMMARY REPORT").Range("K" & Rows.Count).End(xlUp).Offset(1).value
wsSource.Range(wsSource.Range("J11").Offset(1), wsSource.Range("J11").End(xlDown)).value = Sheets("SUMMARY REPORT").Range("L" & Rows.Count).End(xlUp).Offset(1).value

[/QUOTE]


When I set both source and destination using the .value syntax, nothing appears in my destination worksheet....Appreciate some help here......thanks.