Pasting to a Row with a Specific Value in another Workbook

[Maccers93]

Hi All,

I am quite new to this and I have gotten so far but I can't seem to find a structure to do what I need to do.

Below I have attached Workbook1 and Workbook2. I can move data from both workbooks, but I was to paste to a row/column that has a specific value in Workbook2.

In my code, I can find the rows containing 301 and 302, and then copy them. I do not want to assign a column/row when pasting to the Workbook2. Is there anyway I could read Workbook2, find "Account 301" in column A, skip the subheadings (row below), and search for "- AE" in column E, then paste all additional rows copied from 301 from Workbook1 to this row?

Workbook1.xlsm
	A	B	C	D	E	F	G	H
1		Account: 301
2
3		Date	Details	Reference	Type	Debit	Credit	Balance
4
5		21/01/2019	PURCHASE	301	Bank	665.00	-	665.00
6		19/03/2019	PURCHASE	301	Bank	120.00	-	785.00
7		01/04/2019	PURCHASE	301	Bank	384.00	-	1,169.00
8		03/05/2019	PURCHASE	301	Bank	500.00	-	1,669.00
9		10/05/2019	PURCHASE	301	Bank	500.00	-	2,169.00
10		10/05/2019	PURCHASE	301	Bank	500.00	-	2,669.00
11		17/05/2019	PURCHASE	301	Bank	500.00	-	3,169.00
12		23/05/2019	PURCHASE	301	Bank	500.00	-	3,669.00
13		29/05/2019	PURCHASE	301	Bank	585.00	-	4,254.00
14
15		Account: 302
16
17		Date	Details	Reference	Type	Debit	Credit	Balance
18
19		21/01/2019	PURCHASE	302	Bank	1,000.00	-	1,000.00
20		18/02/2019	PURCHASE	302	Bank	500.00	-	1,500.00
21		01/03/2019	PURCHASE	302	Bank	1,000.00	-	2,500.00
22		13/03/2019	PURCHASE	302	Bank	1,000.00	-	3,500.00
23		18/04/2019	PURCHASE	302	Bank	1,000.00	-	4,500.00
24		31/05/2019	PURCHASE	302	Bank	1,000.00	-	5,500.00
25		19/06/2019	PURCHASE	302	Bank	1,000.00	-	6,500.00
26		02/07/2019	PURCHASE	302	Bank	1,000.00	-	7,500.00
27		26/07/2019	PURCHASE	302	Bank	1,000.00	-	8,500.00
28		06/08/2019	PURCHASE	302	Bank	1,000.00	-	9,500.00
Sheet6

Workbook2.xlsx
	A	B	C	D	E	F	G	H
1	Account 301
2	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
3	744	331	31/12/2019	EC	Wages - AE	21439.00	0.00	21439.00
4	772	336	31/12/2019	EC	Narrative	6580.96	0.00	28019.96
5	775	337	31/12/2019	EC	Narrative	19.64	0.00	28039.60
6						28039.60	0.00	28039.60
7
8	Account 302
9	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
10	745	331	31/12/2019	EC	D.R- AE	16500.00	0.00	16500.00
11	773	336	31/12/2019	EC	Narrative	9953.52	0.00	26453.52
Sheet2

Sub Test()

Dim x As Workbook 'Determining Workbook
Dim y As Workbook 'Determining Workbook

Set x = Workbooks.Open("Workbook1.xlsm") 'Opens Workbook1
Set y = Workbooks.Open("Workbook2.xlsx") 'Opens Workbook2

Dim rw As Long, Cell As Range

        For Each Cell In x.Sheets("Sheet5").Range("D2:D1000") 'Range of read first workbook
            rw = Cell.Row
            If Cell.Value = "301" Then 'Search for 301
                Cell.EntireRow.Copy 'Copies entire row containing 302
                y.Sheets("Sheet2").Range("A1").Insert xlShiftDown 'Pastes to Workbook2 on a designated line and creates more below it
            End If
            If Cell.Value = "302" Then 'Search for 302
                Cell.EntireRow.Copy 'Copies entire row containing 302
                y.Sheets("Sheet2").Range("A50").Insert xlShiftDown 'Pastes to Workbook2 on a designated line and creates more below it
            End If
        Next
End Sub

Thanks in advance!

 

[Clarkwr3]

Just replace your sheet names and workbooks with what youve defiened

Sub Find_AcctEnd()
'
Dim i As Long
Dim L As Long
'
'Returns Account Row
'
    i = Application.WorksheetFunction.Match("Account 301", ActiveSheet.Range("A:A"), 0)
'
'Finds Next Blank
'
    ActiveSheet.Cells(i, 1).Select
    L = ActiveCell.End(xlDown).Row
'
End Sub

 

[Zot]

Here is sample of code you can find Account 302. Then find - AE within defined range

Note that Find will get range object, thus why Set is used.

Sub FindAcc()

Dim rngFound As Range, rngSearch As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim x As Workbook, y As Workbook

Set x = Workbooks("Workbook1.xlsm")
Set y = Workbooks("Workbook2.xlsx")
Set ws1 = y.Sheets("Sheet1")

Set rngFound = ws1.Range("A1", ws1.Cells(ws1.Rows.Count, "A").End(xlUp)).Find("Account 302")
If Not rngFound Is Nothing Then
    Set rngSearch = ws1.Range("E" & rngFound.Row, ws1.Cells(ws1.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart)
    If Not rngSearch Is Nothing Then
        'your copy code here'
    End If
End If

End Sub

 

[Maccers93]

Here is sample of code you can find Account 302. Then find - AE within defined range

Note that Find will get range object, thus why Set is used.

Sub FindAcc()

Dim rngFound As Range, rngSearch As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim x As Workbook, y As Workbook

Set x = Workbooks("Workbook1.xlsm")
Set y = Workbooks("Workbook2.xlsx")
Set ws1 = y.Sheets("Sheet1")

Set rngFound = ws1.Range("A1", ws1.Cells(ws1.Rows.Count, "A").End(xlUp)).Find("Account 302")
If Not rngFound Is Nothing Then
    Set rngSearch = ws1.Range("E" & rngFound.Row, ws1.Cells(ws1.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart)
    If Not rngSearch Is Nothing Then
        'your copy code here'
    End If
End If

End Sub

Hi @Zot

I ran the code and had to manipulate it a little, but works a charm thank you!

I just have one more question in regards to the above, I have numbers ranging from 000 to 999, and I want the code to loop through these numbers to find the same additional values the same process as above.

Could you assist in how I could loop through these number and replace the 302 with the numbers in the loop? This is due to the fact we have more than just 302.

I would greatly appreciate it!

Also thank you @Clarkwr3, I tried to run the code with mine and it wouldn't paste to the designated line I needed, thank you for your help!

I have attached my code below (sorry if it's messy)

Sub Test()

Dim x As Workbook 'Determining Workbook
Dim y As Workbook 'Determining Workbook
Dim ws1 As Worksheet 'Determining Worksheet
Dim ws2 As Worksheet 'Determining Worksheet
Dim rngFound As Range 'Determining Range
Dim rngSearch As Range 'Determining Range


Set x = Workbooks.Open("Workbook1.xlsm") 'Opens Workbook
Set y = Workbooks.Open("Workbook2.xlsx") 'Opens Workbookt
Set ws1 = x.Sheets("Sheet5") 'Worksheet
Set ws2 = y.Sheets("Sheet1") 'Worksheet
Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find("Account 302") 'Search for string Account 302
Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for String - AE


Dim rw As Long, Cell As Range

        For Each Cell In ws1.Range("D2:D1000") 'Range of read first workbook
            rw = Cell.Row 'Cell Row is long
            If Cell.Value = "302" Then 'Search for 302
                Cell.EntireRow.Copy 'Copies entire rows containing 302
                If Not rngFound Is Nothing Then 'If rngFound is found then next, if not it stops
                    If Not rngSearch Is Nothing Then 'If rngSearch is found then next, if not it stops
                        ws2.Rows(rngSearch.Row).Insert xlShiftDown 'Pastes to Workbook2 on a designated line and creates more below it
                    End If
                End If
            End If
        Next
End Sub

 

[Zot]

I guess the number is not 000, 001, 002, 003 -> 999; right? If not, where these number are listed or stored?

 

[Maccers93]

I guess the number is not 000, 001, 002, 003 -> 999; right? If not, where these number are listed or stored?

The numbers are stored the same way as 302. It's being read from one workbook1 and pasting into workbook2. The number would be 001 -> 999. They are separate tables with different account numbers of three digits. So I want the same method to loop from 001 -> 999 to move all data to workbook2.

 

[Maccers93]

The numbers are stored the same way as 302. It's being read from one workbook1 and pasting into workbook2. The number would be 001 -> 999. They are separate tables with different account numbers of three digits. So I want the same method to loop from 001 -> 999 to move all data to workbook2.

To confirm, I think the above is a little complicated to understand.

I need to loop through numbers 001 -> 999, first it will find the value of 001 copy those rows and paste to the designated rows in workbook2.

Then 002, 003 and etc. until 999.

is this possible?

 

[Zot]

To confirm, I think the above is a little complicated to understand.

I need to loop through numbers 001 -> 999, first it will find the value of 001 copy those rows and paste to the designated rows in workbook2.

Then 002, 003 and etc. until 999.

is this possible?

If my understanding is right:
Workbook1 has all the Account xxx. Possible numbers are from 000 to 999 but the number all number existed and they are not contiguous.
Workbook2 has all the account number existed in Workbook1.

What need to be done is:
Find all the acc number in Workbook1 column D. Copy entire row
Find matching account number in Workbook2 and then insert it on line with -AE in column E (line -AE will shift down)
Do this for all account found in Worksheet1

Is this what you were looking for?

 

[Maccers93]

If my understanding is right:
Workbook1 has all the Account xxx. Possible numbers are from 000 to 999 but the number all number existed and they are not contiguous.
Workbook2 has all the account number existed in Workbook1.

What need to be done is:
Find all the acc number in Workbook1 column D. Copy entire row
Find matching account number in Workbook2 and then insert it on line with -AE in column E (line -AE will shift down)
Do this for all account found in Worksheet1

Is this what you were looking for?

@Zot That would be correct.

i tried to loop the numbers from 001 to 999 but it doesnt copy the rows or paste them unfortunately. Maybe I have it in the wrong order, I’m not sure.

But once the data copies over, the row containing - AE would need to be deleted and a sum on Workbook2 would need to be functioned.

I tried to research and I get so far but it doesn’t work unfortunately.

thanks again in advance for your assistance.

 

[Zot]

@Zot That would be correct.

i tried to loop the numbers from 001 to 999 but it doesnt copy the rows or paste them unfortunately. Maybe I have it in the wrong order, I’m not sure.

But once the data copies over, the row containing - AE would need to be deleted and a sum on Workbook2 would need to be functioned.

I tried to research and I get so far but it doesn’t work unfortunately.

thanks again in advance for your assistance.

To simplify code I need to know if:
1) The file structure for Workbook 1 Sheet 5 is always fixed like after Account: 301 > Blank row > Date, Detail, Reference ... line > Blank row > Data... Started B?
2) The file structure for Workbook 2 Sheet 1 is similarly laid out? The "- AE" is always on 1st row after title/category line?

I see that the number in Workbook2 is not number but text (left aligned). Is this true?
One more thing I noticed is that if I just copy entire row from Workbook1 to Workbook2, then the category in each column is not aligned between workbooks. The Date is in column B in Workbook1 but column C in Workbook2. You also have Detail column (column C) on Worknbook1 but nothing related in Workbook2 as I see it.