Copy data from for each loop

Javi78503 · #1 12-14-2022, 02:05 PM

Hi again everyone,

So I've been trying to do this with as little help as possible but i'm stumped once again, so I'm hoping for some advice. I'm building a spreadsheet dashboard for several budget accounts I need to track. The last part I'm trying to implement (i hope) is a section on my dashboard that populates the last 5 unapproved requesitions for whatever account I have targeted. (bottom right in the dashboard picture below, highlighted in red). After browsing online all morning the best option I was able to find was to have vba create a picture of a table that could then be inserted into that section. With that said, I now have the task of sorting thru the entire list of PO's and sort out the data for the 5 most recent reqs that are unapproved on the "expenses" worksheet(done, i think) and then copy only select data into a third worksheet called "Data chart" so vba can take a picture of it. My issue here is that I can't seem to get the data copied over, and i've tried the .copy method, and varying versions of the code you see below.

This is the current iteration of the code I'm i'm on. I've been at this all morning, and for some reason it worked briefly then stopped working all together again.

*In this code however, I only have it set to copy one cell from the list for now because why include the rest when I can't even get one to work, but eventually there are 6 primary pieces of information from 12 that I want to copy over to show up in that small window.

Code:

Sub fillPOData(ByVal aName As String)
Dim cell As Range

If Worksheets("Chart Data").Range("A2") <> "" Then
    Worksheets("Chart Data").Range(Worksheets("Chart Data").Range("A2"), Worksheets("Chart Data").Range("A2").End(xlDown).Offset(0, 6)).ClearContents
End If

For Each cell In Worksheets("Expenses").Range(Worksheets("Expenses").Range("C1"), Worksheets("Expenses").Range("C1").End(xlDown))
    If cell.Value = Replace(aName, Chr(34), "") And LCase(cell.Offset(0, 12).Value) = "no" Then
        Worksheets("Data Chart").Range("A1").End(xlDown).Offset(1, 0).Value = cell.Value
    End If
Next
End Sub

Any suggestions on how I can get this code to work, or possibly an easier method to accomplish what I'm looking to do, as in reality inserting a random table into that space that sticks out like a sore thumb is not my ideal solution.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel VBA Loop to update formula in certain cells and create copy in new workbook off dynamic range	bwosk	Excel Programming	1	10-07-2021 10:36 AM
Copy Paste Special Loop to End of Col A	ChrisOK	Excel Programming	8	01-19-2020 10:20 PM
Loop through files and Copy Table Row and Paste into Different Document	spiderman1369	Word VBA	2	10-15-2014 08:30 AM
Using data retreived from a loop in a calculation	So New2 This	Word VBA	2	10-12-2014 03:53 PM
Loop through folder of workbooks and copy range to other workbook	Snvlsfoal	Excel Programming	3	07-29-2011 05:55 AM