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

jlevesquire

New Member
Joined
Dec 30, 2011
Messages
4
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.
 
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.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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"

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top