Extracting data from another workbook

[BradleyN1]

Hi,

I've spent some time trying to figure this out but I've given up, so, I'm seeking some help please!

I have two workbooks, workbook 1 works as an overiew page and workbook 2 has all the data I want to extract to it.
I have an example below.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	WORKBOOK 1						WORKBOOK 2
2
3		REVIEW 1		REVIEW 2			ID	Review	Status	Completed by
4	ID	Status	Completed by	Status	Completed by		1	Review 1	Complete	Name 1
5	1	Complete	Name 1				2	Review 1	Not started	Name 2
6	2						3	Review 2	Needs action	Name 3
7	3			Needs action	Name 3
8
9	I want this workbook to pull across the status of reviews if the ID matches, and the review aligns to the header. Example results are populated.
10
11
12
13
14
15
Sheet1

Thanks

 

[Saurabhj]

Hi,

Check below code:

Assuming, data in your sheet is as below:

WB1.xlsx
	A	B	C	D	E
1		REVIEW 1		REVIEW 2
2	ID	Status	Completed by	Status	Completed by
3	1	Complete	Name 1
4	2	Not Started	Name 2
5	3			Needs action	Name 3
Sheet1

Option Explicit

Sub copyData()
    Dim wb1 As Workbook, targetRow As Integer
    Dim rowno As Integer, totalRows As Integer
    
    Set wb1 = Workbooks("WB1.xlsx")
    totalRows = wb1.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For rowno = 3 To totalRows
        targetRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
        wb1.Sheets("Sheet1").Range("A" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("A" & targetRow)
        If wb1.Sheets("Sheet1").Range("B" & rowno) <> "" Then
            ThisWorkbook.Sheets("Sheet1").Range("B" & targetRow) = "Review 1"
            wb1.Sheets("Sheet1").Range("B" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("C" & targetRow)
            wb1.Sheets("Sheet1").Range("C" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("D" & targetRow)
        Else
            ThisWorkbook.Sheets("Sheet1").Range("B" & targetRow) = "Review 2"
            wb1.Sheets("Sheet1").Range("D" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("C" & targetRow)
            wb1.Sheets("Sheet1").Range("E" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("D" & targetRow)
        End If
    Next

End Sub

 

[BradleyN1]

Thanks for your very prompt response
I will check this out! Can I just clarify, is this only possible via VBA, there's not a formula for this?

 

[BradleyN1]

Hi,

Check below code:

Assuming, data in your sheet is as below:

WB1.xlsx
	A	B	C	D	E
1		REVIEW 1		REVIEW 2
2	ID	Status	Completed by	Status	Completed by
3	1	Complete	Name 1
4	2	Not Started	Name 2
5	3			Needs action	Name 3
Sheet1

Option Explicit

Sub copyData()
    Dim wb1 As Workbook, targetRow As Integer
    Dim rowno As Integer, totalRows As Integer
   
    Set wb1 = Workbooks("WB1.xlsx")
    totalRows = wb1.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
   
    For rowno = 3 To totalRows
        targetRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
        wb1.Sheets("Sheet1").Range("A" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("A" & targetRow)
        If wb1.Sheets("Sheet1").Range("B" & rowno) <> "" Then
            ThisWorkbook.Sheets("Sheet1").Range("B" & targetRow) = "Review 1"
            wb1.Sheets("Sheet1").Range("B" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("C" & targetRow)
            wb1.Sheets("Sheet1").Range("C" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("D" & targetRow)
        Else
            ThisWorkbook.Sheets("Sheet1").Range("B" & targetRow) = "Review 2"
            wb1.Sheets("Sheet1").Range("D" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("C" & targetRow)
            wb1.Sheets("Sheet1").Range("E" & rowno).Copy ThisWorkbook.Sheets("Sheet1").Range("D" & targetRow)
        End If
    Next

End Sub

Sorry, forgot to actually reply to your message!