Pasting to a Row with a Specific Value in another Workbook

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1Account: 301
2
3DateDetailsReferenceTypeDebitCreditBalance
4
521/01/2019PURCHASE301Bank665.00-665.00
619/03/2019PURCHASE301Bank120.00-785.00
701/04/2019PURCHASE301Bank384.00-1,169.00
803/05/2019PURCHASE301Bank500.00-1,669.00
910/05/2019PURCHASE301Bank500.00-2,169.00
1010/05/2019PURCHASE301Bank500.00-2,669.00
1117/05/2019PURCHASE301Bank500.00-3,169.00
1223/05/2019PURCHASE301Bank500.00-3,669.00
1329/05/2019PURCHASE301Bank585.00-4,254.00
14
15Account: 302
16
17DateDetailsReferenceTypeDebitCreditBalance
18
1921/01/2019PURCHASE302Bank1,000.00-1,000.00
2018/02/2019PURCHASE302Bank500.00-1,500.00
2101/03/2019PURCHASE302Bank1,000.00-2,500.00
2213/03/2019PURCHASE302Bank1,000.00-3,500.00
2318/04/2019PURCHASE302Bank1,000.00-4,500.00
2431/05/2019PURCHASE302Bank1,000.00-5,500.00
2519/06/2019PURCHASE302Bank1,000.00-6,500.00
2602/07/2019PURCHASE302Bank1,000.00-7,500.00
2726/07/2019PURCHASE302Bank1,000.00-8,500.00
2806/08/2019PURCHASE302Bank1,000.00-9,500.00
Sheet6


Workbook2.xlsx
ABCDEFGH
1Account 301
2Tran No.Bat No.DateRef No.NarrativeDebitCreditBalance
374433131/12/2019ECWages - AE21439.000.0021439.00
477233631/12/2019ECNarrative6580.960.0028019.96
577533731/12/2019ECNarrative19.640.0028039.60
628039.600.0028039.60
7
8Account 302
9Tran No.Bat No.DateRef No.NarrativeDebitCreditBalance
1074533131/12/2019ECD.R- AE16500.000.0016500.00
1177333631/12/2019ECNarrative9953.520.0026453.52
Sheet2


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

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Clarkwr3

New Member
Joined
Feb 11, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Just replace your sheet names and workbooks with what youve defiened

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,010
Office Version
  1. 2016
Platform
  1. Windows
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.

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

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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.

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

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,010
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Maccers93

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,010
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,010
Office Version
  1. 2016
Platform
  1. Windows
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,721
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top