Populate table based on data in another sheet

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
340
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,506
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
340
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
340
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,506
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,081,765
Messages
5,361,156
Members
400,615
Latest member
inzimam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top