Populate table based on data in another sheet

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
345
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

Active Member
Joined
Oct 30, 2015
Messages
436
Give this code a try

Code:
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,604
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

Active Member
Joined
Oct 30, 2015
Messages
436
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

Active Member
Joined
Oct 1, 2011
Messages
345
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

Code:
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

Active Member
Joined
Oct 1, 2011
Messages
345
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

Active Member
Joined
Oct 30, 2015
Messages
436
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.
Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,604
Assuming that the "Workflow" has headers in row 3, try this macro:
Code:
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
 

Forum statistics

Threads
1,085,428
Messages
5,383,610
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top