Populate table based on data in another sheet

[surkdidat]

I have a sheet called "Workflow, and another sheet called "Overview"

"Overview" is my dashboard
"Workflow" is my data"

In "Overview" (dashboard) I have a table From C13:I28

In "Workflow" I have the data I want to populate in that table.

My "Workflow" sheet has data from cells A4:J723

What I need is it to look at the value in row K, if it is a "YES" I need to ignore that line of data

If it is blank, then I want the data in Cells A, E, F, G,H,I and J copied into the table on the dashboard sheet.

Can someone advise please?

Many thanks

 

[frank_AL]

Give this code a try

Option Explicit


Sub CopyToOverview()


Dim cfws As Worksheet
Dim ctws As Worksheet
Dim lr As Long
Dim nr As Long
Dim i As Long


Set cfws = Worksheets("Workflow")
Set ctws = Worksheets("Overview")


lr = cfws.Cells(cfws.Rows.Count, "A").End(xlUp).Row
nr = ctws.Cells(ctws.Rows.Count, "C").End(xlUp).Row + 1


For i = 4 To lr
    If IsEmpty(Cells(i, "K").Value) = True Then
        ctws.Cells(nr, "C").Value = cfws.Cells(i, "A").Value
        ctws.Range("D" & nr & ":I" & nr).Value = cfws.Range("E" & i & ":J" & i).Value
        nr = nr + 1
    End If
Next i


End Sub

 

[surkdidat]

Apologies where in the spreadsheet do I insert the code please?

 

[mumps]

Does the "Workflow" sheet have headers in rows 1, 2 or 3 from columns A to J? If so, in which row? Can I assume that you want columns A, E, F, G, H,I and J copied to columns C, D, E, F, G, H and I in the "Overview" sheet in the first empty row at the bottom?

 

[frank_AL]

Hopefully you have the Developer feature enabled. If so, you will have a choice for "Developer" in you top menu (where File, Home, etc.) reside.
Click on Developer
Click on Visual Basic
You should see your file name listed on the left of the VBA Project screen that opens. Click on your file name
In the topline menu click on Insert, then Module
You should see Module1 under where your filename exists
Double Click on Module 1 and then in the window on the right paste the code I provided for you.

 

[surkdidat]

Thank you for this
.[
At present the code is populating in the "Overview" sheet from C2, however, I need it populating from C13.

Also to aide my understanding of the code, where you have

If IsEmpty(Cells(i, "K").Value) = True Then

I get this - this is the bit where its looking up for a blank value.

How would I

a) - Make this a multiple criteria i.e. if column E contains ANY value (aswell as K being blank)
b) - Is there a way of making this look up the first 10 or 15 occasions before ending?

Many thanks in advance!

Hopefully you have the Developer feature enabled. If so, you will have a choice for "Developer" in you top menu (where File, Home, etc.) reside.
Click on Developer
Click on Visual Basic
You should see your file name listed on the left of the VBA Project screen that opens. Click on your file name
In the topline menu click on Insert, then Module
You should see Module1 under where your filename exists
Double Click on Module 1 and then in the window on the right paste the code I provided for you.

 

[surkdidat]

Hi - yes, the columns do have headers, Analyst, Assigned, Investigated, Reviewed, Complete, SignOff.

The code provided by frank_AL does appear to work though in a blank spreadsheet, just need it starting in C13, not C2, before I try it in my main data sheet

Does the "Workflow" sheet have headers in rows 1, 2 or 3 from columns A to J? If so, in which row? Can I assume that you want columns A, E, F, G, H,I and J copied to columns C, D, E, F, G, H and I in the "Overview" sheet in the first empty row at the bottom?

 

[frank_AL]

Okay, I have modified the code to look for Col K being blank and Col E contains any value
I set the code to start on Row 13 on the Overview worksheet
The code originally was written to identify the last row of data on the Workflow Worksheet. That value was stored to the "lr" variable.
I added a line of code to check if "lr" is greater than 15 and if it is set it to 15. You can modify that line of code to be whatever value you want it to be. This only impacts the rows that are evaluated, not the number of Rows that match the expected criteria.

Option Explicit


Sub CopyToOverview()


Dim cfws As Worksheet
Dim ctws As Worksheet
Dim lr As Long
Dim nr As Long
Dim i As Long


Set cfws = Worksheets("Workflow")
Set ctws = Worksheets("Overview")


lr = cfws.Cells(cfws.Rows.Count, "A").End(xlUp).Row
If lr > 15 Then lr = 15
nr = ctws.Cells(ctws.Rows.Count, "C").End(xlUp).Row + 1
If nr < 13 Then nr = 13


For i = 4 To lr
    If IsEmpty(Cells(i, "K").Value) = True And IsEmpty(Cells(i, "E").Value) = False Then
        ctws.Cells(nr, "C").Value = cfws.Cells(i, "A").Value
        ctws.Range("D" & nr & ":I" & nr).Value = cfws.Range("E" & i & ":J" & i).Value
        nr = nr + 1
    End If
Next i


End Sub

 

[mumps]

Assuming that the "Workflow" has headers in row 3, try this macro:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Workflow").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Workflow").Range("A3:K" & LastRow).AutoFilter Field:=11, Criteria1:="="
    Sheets("Workflow").Range("A4:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Overview").Cells(13, "C")
    Sheets("Workflow").Range("E4:I" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Overview").Cells(13, "D")
    If Sheets("Workflow").AutoFilterMode = True Then Sheets("Workflow").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub