Pasting to a Row with a Specific Value in another Workbook

M

Maccers93

New Member
Joined
Feb 12, 2021
Messages
29
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!
 
Sort by date Sort by votes
Zot said:
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.
Click to expand...
Hey @Zot

Yes, the structure in WB1 Sheet5 would be the same structure continuously from Account: 001 to Account: 999. The first 7 lines would be “Blank > Header > Header > Blank > Header > Blank > Blank” and then the Account: Number.

There won’t always be 999 accounts, varies on the report. The Account number in WB1 would be Account: 3, while WB2 has Account 003. This is the reason why I used the reference column to read the data.

The “- AE” attribute would not be on the first line, this would also vary, depending on the account.

Yes the text seems to be left aligned.

I have a button to sort the rows, I created a macro for this, I will attach to the next reply.
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
VBA Code: 
Private Sub CommandButton1_Click()
'
' DataSort Macro
'

'
    Columns("A:A").Select
    Range("A76").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.Left = -1429.25
    Application.Top = 132.25
    Windows("Workbook2.xlsm").Activate
    Application.Left = -638.75
    Application.Top = 143.5
    Windows("Workbook1.xlsm").Activate
    Columns("G:G").Select
    Range("G76").Activate
    Selection.Cut
    Columns("B:B").Select
    Range("B76").Activate
    ActiveSheet.Paste
    Application.Left = -1490
    Application.Top = 115.75
    Columns("E:E").Select
    Range("E76").Activate
    Selection.Cut
    Columns("D:D").Select
    Range("D76").Activate
    Selection.Insert Shift:=xlToRight
    Columns("H:H").Select
    Range("H76").Activate
    Selection.Cut
    Columns("F:F").Select
    Range("F76").Activate
    Selection.Insert Shift:=xlToRight
    Columns("J:J").Select
    Range("J76").Activate
    Selection.Cut
    Columns("G:G").Select
    Range("G76").Activate
    Selection.Insert Shift:=xlToRight
    Columns("L:L").Select
    Range("L76").Activate
    Selection.Cut
    Columns("H:H").Select
    Range("H76").Activate
    Selection.Insert Shift:=xlToRight
    Range("K85").Select
End Sub

This sorts the columns in WB1
 
Upvote 0
I will try to understand what it is doing here once I reached home. End of day for me here now ?
 
Upvote 0
Your code gave me an error.

Is the Workbook 1 originally like that with blank column A? You have
Date | Details | Reference | Type | Debit | Credit | Balance

Workbook 2 you have:
Trans No | Batch No | Date | Ref No | Narrative | Debit | Credit | Balance

The Debit Credit and Balance will go accordingly including Date. WHere the Details, Reference and Type in Workbook 1 will go to in Workbook 2?
 
Upvote 0
Zot said:
I will try to understand what it is doing here once I reached home. End of day for me here now ?
Click to expand...
That is no problem, thank you @Zot , I appreciate everything you've done so far!

All I need is data from Workbook1 to copy the rows containing the values in reference and replace the "- AE" row in Workbook2 with the selected data in Workbook1.
Zot said:
Your code gave me an error.

Is the Workbook 1 originally like that with blank column A? You have
Date | Details | Reference | Type | Debit | Credit | Balance

Workbook 2 you have:
Trans No | Batch No | Date | Ref No | Narrative | Debit | Credit | Balance

The Debit Credit and Balance will go accordingly including Date. WHere the Details, Reference and Type in Workbook 1 will go to in Workbook 2?
Click to expand...
That is correct. The blank is originally there after processing from the software.

The headings I have on my original documents I could not provide due to some GDPR regulations unfortunately. I can send you the documentation with demo data if it's easier to work with?

To make it easier I just created a sort button on my Workbook1 and it works fine to move the columns to same as Workbook2.

What I tried to do was loop the vba code you provided that worked the first time, which worked for one value, but would not work with the loop unfortunately. Everything else was working as should.

If you have an alternative method or such I would be more than happy to try it out!
 
Upvote 0
Since not much detail due to regulation. I'll just provide how data can be copied by looping through all the account number. The Date column pasted on Workbook 2 will not be in the same column.

Note that I could not get to run your code due to error no file found, I created an empty workbook and put macro in it. When I run the macro, I just select Workbook 1 and Workbook 2. This way I can put Workbook 1 and/or Workbook 2 anywhere I like. No need to be in same folder.

How macro run?
The program will go into Workbook 1 and find all accounts in it and store data in dictionary the account row number (key) and account number (value).
Then program will go one by one all stored accounts in dictionary and loop the Workbook 2 to search for matching account.
Once match is found, copy a block of data under that account in workbook 1 into workbook 2.
Delete -AE line and format number and create SUM formula.

You just need to modify the code according to your need.

VBA Code: 
Sub Test()

Dim wbX As Workbook 'Determining Workbook
Dim wbY 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
Dim Fname As Variant
Dim DictAcc As Object

' Select file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbX = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws1 = wbX.Sheets("Sheet5") ' Worksheet

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbY = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws2 = wbY.Sheets("Sheet1") 'Worksheet

Set DictAcc = CreateObject("Scripting.Dictionary")

Dim rw As Long, lngAcc As Long, Cell As Range
For Each Cell In ws1.Range("B1:B1000") ' Search for all existed Account numbers. Store row number as key and account number as value
    If Cell Like "*Account*" Then
        DictAcc.Add Cell.Row, Format(Right(Cell, Len(Cell) - 9), "000")
        Debug.Print Format(Right(Cell, Len(Cell) - 9), "000")
    End If
Next

Dim iRow As Long, eRow As Long, BlockSize As Long
Dim strAcc As String
Dim key As Variant, AccNo As Variant
Dim rngSum As Range

For Each key In DictAcc ' Go through each account number in Dictionary
    strAcc = "Account " & DictAcc(key)
    Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find(strAcc)  ' Find matching account number
    Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for "- AE"
    If Not rngFound Is Nothing Then
        If Not Len(ws1.Range("B" & key + 4)) = 0 Then                     ' Check if specific account has more than one line to copy
            BlockSize = (ws1.Range("B" & key + 4).End(xlDown).Row) - (key + 4)
            ws1.Range("B" & key + 4, "B" & ws1.Range("B" & key + 4).End(xlDown).Row).EntireRow.Copy
        Else
            BlockSize = 1
            ws1.Range("B" & key + 4).EntireRow.Copy
        End If
        iRow = rngSearch.Row
        With ws2.Range("A" & rngSearch.Row)
            .Insert
            .EntireRow.Delete                   ' Delete row "- AE"
        End With
        With ws2
            eRow = ws2.Range("F" & iRow).End(xlDown).Row - 1
            Set rngSum = ws2.Range("F" & iRow, "F" & eRow)
            rngSum.NumberFormat = "#.00"
            rngSum.HorizontalAlignment = xlGeneral
            .Range("F" & eRow + 1).NumberFormat = xlGeneral
            .Range("F" & eRow + 1).Formula = "=SUM(" & rngSum.Address(0, 0) & ")"
        End With
    End If
Next

End Sub

* Do not move the Set rngFound and Set rngSearch line outside loop since the Workbook 2 rows are dynamic due to inserting rows. Therefore range search is dynamic too.
 
Upvote 0
Zot said:
Since not much detail due to regulation. I'll just provide how data can be copied by looping through all the account number. The Date column pasted on Workbook 2 will not be in the same column.

Note that I could not get to run your code due to error no file found, I created an empty workbook and put macro in it. When I run the macro, I just select Workbook 1 and Workbook 2. This way I can put Workbook 1 and/or Workbook 2 anywhere I like. No need to be in same folder.

How macro run?
The program will go into Workbook 1 and find all accounts in it and store data in dictionary the account row number (key) and account number (value).
Then program will go one by one all stored accounts in dictionary and loop the Workbook 2 to search for matching account.
Once match is found, copy a block of data under that account in workbook 1 into workbook 2.
Delete -AE line and format number and create SUM formula.

You just need to modify the code according to your need.

VBA Code: 
Sub Test()

Dim wbX As Workbook 'Determining Workbook
Dim wbY 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
Dim Fname As Variant
Dim DictAcc As Object

' Select file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbX = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws1 = wbX.Sheets("Sheet5") ' Worksheet

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbY = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws2 = wbY.Sheets("Sheet1") 'Worksheet

Set DictAcc = CreateObject("Scripting.Dictionary")

Dim rw As Long, lngAcc As Long, Cell As Range
For Each Cell In ws1.Range("B1:B1000") ' Search for all existed Account numbers. Store row number as key and account number as value
    If Cell Like "*Account*" Then
        DictAcc.Add Cell.Row, Format(Right(Cell, Len(Cell) - 9), "000")
        Debug.Print Format(Right(Cell, Len(Cell) - 9), "000")
    End If
Next

Dim iRow As Long, eRow As Long, BlockSize As Long
Dim strAcc As String
Dim key As Variant, AccNo As Variant
Dim rngSum As Range

For Each key In DictAcc ' Go through each account number in Dictionary
    strAcc = "Account " & DictAcc(key)
    Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find(strAcc)  ' Find matching account number
    Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for "- AE"
    If Not rngFound Is Nothing Then
        If Not Len(ws1.Range("B" & key + 4)) = 0 Then                     ' Check if specific account has more than one line to copy
            BlockSize = (ws1.Range("B" & key + 4).End(xlDown).Row) - (key + 4)
            ws1.Range("B" & key + 4, "B" & ws1.Range("B" & key + 4).End(xlDown).Row).EntireRow.Copy
        Else
            BlockSize = 1
            ws1.Range("B" & key + 4).EntireRow.Copy
        End If
        iRow = rngSearch.Row
        With ws2.Range("A" & rngSearch.Row)
            .Insert
            .EntireRow.Delete                   ' Delete row "- AE"
        End With
        With ws2
            eRow = ws2.Range("F" & iRow).End(xlDown).Row - 1
            Set rngSum = ws2.Range("F" & iRow, "F" & eRow)
            rngSum.NumberFormat = "#.00"
            rngSum.HorizontalAlignment = xlGeneral
            .Range("F" & eRow + 1).NumberFormat = xlGeneral
            .Range("F" & eRow + 1).Formula = "=SUM(" & rngSum.Address(0, 0) & ")"
        End With
    End If
Next

End Sub

* Do not move the Set rngFound and Set rngSearch line outside loop since the Workbook 2 rows are dynamic due to inserting rows. Therefore range search is dynamic too.
Click to expand...
Thank you very much @Zot , I will try this when I wake for work, I appreciate the time you have put into this and patience.

The macro I had was for sorting columns to match the columns in wb2, apologies for it not working as I am new to the Excel format of vba.

I’m hoping this will work, if it does I am forever grateful as it seems like it’s never been done or questioned on how it’s done before.

I will let you know how this goes as soon as I test it, thank you again, means a lot!
 
Upvote 0
Zot said:
Since not much detail due to regulation. I'll just provide how data can be copied by looping through all the account number. The Date column pasted on Workbook 2 will not be in the same column.

Note that I could not get to run your code due to error no file found, I created an empty workbook and put macro in it. When I run the macro, I just select Workbook 1 and Workbook 2. This way I can put Workbook 1 and/or Workbook 2 anywhere I like. No need to be in same folder.

How macro run?
The program will go into Workbook 1 and find all accounts in it and store data in dictionary the account row number (key) and account number (value).
Then program will go one by one all stored accounts in dictionary and loop the Workbook 2 to search for matching account.
Once match is found, copy a block of data under that account in workbook 1 into workbook 2.
Delete -AE line and format number and create SUM formula.

You just need to modify the code according to your need.

VBA Code: 
Sub Test()

Dim wbX As Workbook 'Determining Workbook
Dim wbY 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
Dim Fname As Variant
Dim DictAcc As Object

' Select file
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbX = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws1 = wbX.Sheets("Sheet5") ' Worksheet

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbY = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set ws2 = wbY.Sheets("Sheet1") 'Worksheet

Set DictAcc = CreateObject("Scripting.Dictionary")

Dim rw As Long, lngAcc As Long, Cell As Range
For Each Cell In ws1.Range("B1:B1000") ' Search for all existed Account numbers. Store row number as key and account number as value
    If Cell Like "*Account*" Then
        DictAcc.Add Cell.Row, Format(Right(Cell, Len(Cell) - 9), "000")
        Debug.Print Format(Right(Cell, Len(Cell) - 9), "000")
    End If
Next

Dim iRow As Long, eRow As Long, BlockSize As Long
Dim strAcc As String
Dim key As Variant, AccNo As Variant
Dim rngSum As Range

For Each key In DictAcc ' Go through each account number in Dictionary
    strAcc = "Account " & DictAcc(key)
    Set rngFound = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)).Find(strAcc)  ' Find matching account number
    Set rngSearch = ws2.Range("E" & rngFound.Row, ws2.Cells(ws2.Rows.Count, "E").End(xlDown)).Find("- AE", LookAt:=xlPart) 'Search for "- AE"
    If Not rngFound Is Nothing Then
        If Not Len(ws1.Range("B" & key + 4)) = 0 Then                     ' Check if specific account has more than one line to copy
            BlockSize = (ws1.Range("B" & key + 4).End(xlDown).Row) - (key + 4)
            ws1.Range("B" & key + 4, "B" & ws1.Range("B" & key + 4).End(xlDown).Row).EntireRow.Copy
        Else
            BlockSize = 1
            ws1.Range("B" & key + 4).EntireRow.Copy
        End If
        iRow = rngSearch.Row
        With ws2.Range("A" & rngSearch.Row)
            .Insert
            .EntireRow.Delete                   ' Delete row "- AE"
        End With
        With ws2
            eRow = ws2.Range("F" & iRow).End(xlDown).Row - 1
            Set rngSum = ws2.Range("F" & iRow, "F" & eRow)
            rngSum.NumberFormat = "#.00"
            rngSum.HorizontalAlignment = xlGeneral
            .Range("F" & eRow + 1).NumberFormat = xlGeneral
            .Range("F" & eRow + 1).Formula = "=SUM(" & rngSum.Address(0, 0) & ")"
        End With
    End If
Next

End Sub

* Do not move the Set rngFound and Set rngSearch line outside loop since the Workbook 2 rows are dynamic due to inserting rows. Therefore range search is dynamic too.
Click to expand...
Hi @Zot

This seems to work to an extent but doesn't seem to copy over the data properly unfortunately.

I will provide a two demo sheets that would look similar to the workbooks I am working with.
 
Upvote 0
AE_Workbook1.xlsm
ABCDEFGHI
1
2
3Heading1
4Heading2
5
6
7
8Account: 3
9
10BatchDateReferenceDetailsDebitCreditBalanceType
11
121002/07/2019003Details1-117.41(117.41)Bank
131017/10/2019003Details2-7,162.40(7,279.81)Bank
141003/12/2019003Details3-2,951.68(10,231.49)Bank
15
16-10,231.49(10,231.49)
17
18Account: 301
19
20BatchDateReferenceDetailsDebitCreditBalanceType
21
22321/01/2019301Details1665.00-665.00Bank
23319/03/2019301Details2120.00-785.00Bank
24301/04/2019301Details3384.00-1,169.00Bank
254703/05/2019301Details4500.00-1,669.00Bank
264710/05/2019301Details5500.00-2,169.00Bank
27410/05/2019301Details6500.00-2,669.00Bank
28417/05/2019301Details7500.00-3,169.00Bank
29423/05/2019301Details8500.00-3,669.00Bank
30729/05/2019301Details9585.00-4,254.00Bank
31430/05/2019301Details10500.00-4,754.00Bank
32831/05/2019301Details11375.00-5,129.00Bank
338404/06/2019301Details121,100.00-6,229.00Bank
34705/06/2019301Details13550.00-6,779.00Bank
35406/06/2019301Details14500.00-7,279.00Bank
36807/06/2019301Details15460.00-7,739.00Bank
37707/06/2019301Details16250.00-7,989.00Bank
38413/06/2019301Details17500.00-8,489.00Bank
39814/06/2019301Details18445.00-8,934.00Bank
40714/06/2019301Details19430.00-9,364.00Bank
416017/06/2019301Details20350.00-9,714.00Bank
428418/06/2019301Details21700.00-10,414.00Bank
43420/06/2019301Details22500.00-10,914.00Bank
446125/06/2019301Details23350.00-11,264.00Bank
45427/06/2019301Details24500.00-11,764.00Bank
463228/06/2019301Details2590.00-11,854.00Bank
47728/06/2019301Details2690.00-11,944.00Bank
486202/07/2019301Details27350.00-12,294.00Bank
49404/07/2019301Details28500.00-12,794.00Bank
506310/07/2019301Details29350.00-13,144.00Bank
51411/07/2019301Details30500.00-13,644.00Bank
52815/07/2019301Details31110.00-13,754.00Bank
53715/07/2019301Details32140.00-13,894.00Bank
54418/07/2019301Details33500.00-14,394.00Bank
559222/07/2019301Details34300.00-14,694.00Bank
56425/07/2019301Details35500.00-15,194.00Bank
57826/07/2019301Details36110.00-15,304.00Bank
58726/07/2019301Details37150.00-15,454.00Bank
59401/08/2019301Details38500.00-15,954.00Bank
60802/08/2019301Details39265.00-16,219.00Bank
61702/08/2019301Details40280.00-16,499.00Bank
62408/08/2019301Details41500.00-16,999.00Bank
63415/08/2019301Details42500.00-17,499.00Bank
64816/08/2019301Details43195.00-17,694.00Bank
655121/08/2019301Details44450.00-18,144.00Bank
66422/08/2019301Details45500.00-18,644.00Bank
67429/08/2019301Details46500.00-19,144.00Bank
68830/08/2019301Details4780.00-19,224.00Bank
69730/08/2019301Details4880.00-19,304.00Bank
70405/09/2019301Details49500.00-19,804.00Bank
71412/09/2019301Details50500.00-20,304.00Bank
72419/09/2019301Details51500.00-20,804.00Bank
738818/10/2019301Details52235.00-21,039.00Bank
748423/10/2019301Details53400.00-21,439.00Bank
75
7621,439.00-21,439.00
77
78Account: 302
79
80BatchDateReferenceDetailsDebitCreditBalanceType
81
82221/01/2019302Details11,000.00-1,000.00Bank
83218/02/2019302Details2500.00-1,500.00Bank
84201/03/2019302Details31,000.00-2,500.00Bank
85213/03/2019302Details41,000.00-3,500.00Bank
86218/04/2019302Details51,000.00-4,500.00Bank
87231/05/2019302Details61,000.00-5,500.00Bank
88219/06/2019302Details71,000.00-6,500.00Bank
89202/07/2019302Details81,000.00-7,500.00Bank
90226/07/2019302Details91,000.00-8,500.00Bank
91206/08/2019302Details101,000.00-9,500.00Bank
92203/09/2019302Details111,000.00-10,500.00Bank
93211/09/2019302Details121,000.00-11,500.00Bank
94203/10/2019302Details131,000.00-12,500.00Bank
95229/10/2019302Details141,000.00-13,500.00Bank
96219/11/2019302Details151,000.00-14,500.00Bank
97203/12/2019302Details161,000.00-15,500.00Bank
98211/12/2019302Details171,000.00-16,500.00Bank
99
10016,500.00-16,500.00
101
102Account: 322
103
104BatchDateReferenceDetailsDebitCreditBalanceType
105
1068211/12/2019322Details11,000.00-1,000.00Bank
107
1081,000.00-1,000.00
AccountDetails


Workbook2.xlsx
ABCDEFGHI
70Account 002
71Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
7252234331/12/2019Details10.008005.87-8005.87SAI
7377833731/12/2019Details20.009620.01-17625.88JNL
740.0017625.88-17625.88
75
76Account 003
77Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
7874333131/12/2019Details1 - AE0.0010231.49-10231.49JNL
790.0010231.49-10231.49
80
81Account 004
82Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
8349232607/08/2019Details0.006000.00-6000.00SAI
840.006000.00-6000.00
85
86Account 111
87Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
88200533331/12/2019Details277.680.00277.68PAI
89277.680.00277.68
90
91Account 112A
92Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
93200033331/12/2019Details1425.000.001425.00PAI
94200233331/12/2019Details47.400.001472.40PAI
95200833331/12/2019Details3280.000.004752.40PAI
964752.400.004752.40
97
98Account 112C
99Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
100189932231/05/2019Details112.000.00112.00PAI
101189932231/05/2019Details1552.040.001664.04PAI
102194332526/07/2019Details28.000.001692.04PAI
103194332526/07/2019Details223.580.001915.62PAI
104200933331/12/2019Details147.500.002063.12PAI
105201033331/12/2019Details1475.000.003538.12PAI
106201133331/12/2019Details742.500.004280.62PAI
107201233331/12/2019Details1230.000.005510.62PAI
108201333331/12/2019Details1626.000.007136.62PAI
109201733431/12/2019Details0.0088.507048.12PAC
1107136.6288.507048.12
111
112Account 301
113Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
11474433131/12/2019Details - AE21439.000.0021439.00JNL
11577233631/12/2019Details6580.960.0028019.96JNL
11677533731/12/2019Details19.640.0028039.60JNL
11728039.600.0028039.60
118
119Account 302
120Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
12174533131/12/2019Details - AE16500.000.0016500.00JNL
12277333631/12/2019Details9953.520.0026453.52JNL
12326453.520.0026453.52
124
125Account 322
126Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceType
12774633131/12/2019Details- AE1000.000.001000.00JNL
1281000.000.001000.00
Sheet1


These are little segments of the layout of how the spreadsheets would look like. It's the best I could do without using personal data.
 
Upvote 0
The worksheet 2, some of the accounts do not have that - AE keyword, thus will cause problem. Can the data be pasted on line just after heading
Trans No. Bat No. Date etc?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Macro to open multiple workbooks and copy a range of data based on todays date. Then paste the data in master workbook in one sheet under each other.
Replies
8
Views
643
Luke1690
L
T
  • Solved
Copy and Past the Data from Multiple Closed Workbooks
2
Replies
16
Views
1K
thespardian
T
L
  • Solved
Importing specific cell data from one workbook to another
Replies
1
Views
786
rlv01
R
L
  • Question Question
Copy Subtotals from Source Workbook to Current Workbook
Replies
1
Views
418
DanteAmor
DanteAmor
U
  • Question Question
Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.
Replies
0
Views
136
user29383902
U

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top