VBA to copy data from a sheet1 and sheet2 to different tabs

[Abhishekghorpade]

[FONT=&quot]Hi,[/FONT]
[FONT=&quot]I have amounts in Sheet1 ‘column D’ and Sheet2 ‘column C’, I want to copy and paste the amount in following tabs in column B and column F with the below criteria.[/FONT]

[FONT=&quot]Criteria to be match is date and plan ID (Which is in H1 in all tabs). [/FONT]

[FONT=&quot]Thank you so much in advance[/FONT]

 

[DanteAmor]

Hi,
I have amounts in Sheet1 ‘column D’ and Sheet2 ‘column C’, I want to copy and paste the amount in following tabs in column B and column F with the below criteria.

Criteria to be match is date and plan ID (Which is in H1 in all tabs).

Thank you so much in advance

You could explain it with several examples. The important thing to be able to do the macro is to know in which columns you have all the relevant information, where your data start and exactly where you want to put it. ah, The example includes names of the destination sheets.

 

[Abhishekghorpade]

I am sorry its quite a difficult for me to explain.. is there a way to attach the excel file with that it will be easy for me to explain..

 

[DanteAmor]

I am sorry its quite a difficult for me to explain.. is there a way to attach the excel file with that it will be easy for me to explain..

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Abhishekghorpade]

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

I have added the file.. Sheet1 and 2 has the data.. Rest of the tabs should fetch the amount from sheet1&2 based the plan ID(h1) and date.. Paste that amount in column B and column For all tabs if plan ID is missing it should pull zero

https://www.dropbox.com/s/wu7sud82sj089x0/GGI - Daily Earnings.xlsx?dl=0

 

[DanteAmor]

I have added the file.. Sheet1 and 2 has the data.. Rest of the tabs should fetch the amount from sheet1&2 based the plan ID(h1) and date.. Paste that amount in column B and column For all tabs if plan ID is missing it should pull zero

https://www.dropbox.com/s/wu7sud82sj089x0/GGI - Daily Earnings.xlsx?dl=0

Even so, you should explain at least one record.

 

[Abhishekghorpade]

Even so, you should explain at least one record.

Sheet1 ending balance should come in column B in all tabs and Sheet2 balance amount should come to column For in all tabs.

Ex: tab (Anderson Heating) cell # B184 should pull ending balance from sheet1 column D2. As Anderson heating plan ID doesn't there in sheet2 column F will be blank/zero.

Ex: tab (McBride?) Has plan ID in both sheet1&2. It should pull ending balance from sheet1&2 to column B&F(cell # b426&f426)

Each tab has unique plan ID in H1. Same plan ID's are there in sheet1 &2.

 

[DanteAmor]

Let's see if I understood.
I go to sheet1, I get
The ID: 10779463
the date: 6192019.
and the balance: 1160665.04
I'm going to the Anderson Heating sheet,
I look for the date in column A
And in column B I put the balance.


Is the above correct?


I have a problem with your file, on sheet1 in column B you have this: 6192019
That number is not a date.


In the other sheets you have dates like this 06/19/2019.


You tell me

 

[Abhishekghorpade]

Let's see if I understood.
I go to sheet1, I get
The ID: 10779463
the date: 6192019.
and the balance: 1160665.04
I'm going to the Anderson Heating sheet,
I look for the date in column A
And in column B I put the balance.


Is the above correct?


I have a problem with your file, on sheet1 in column B you have this: 6192019
That number is not a date.


In the other sheets you have dates like this 06/19/2019.


You tell me

Yes.. Your understanding is correct.. I can convert the 6192019 into proper date format. Like 06/19/2019

 

[DanteAmor]

Yes.. Your understanding is correct.. I can convert the 6192019 into proper date format. Like 06/19/2019

ok, Then you must convert the date to dd/mm/yyyy.
Use the following macro:

Sub Copy_Data()
    Dim sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet
    Dim f As Range, d As Range, mount As Double, wdate As Date
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    For Each sh In Sheets
        If sh.Name <> sh1.Name And sh.Name <> sh2.Name Then
            num = sh.Range("H1").Value
            
            'search in sheet1
            Set f = sh1.Range("A:A").Find(num, LookIn:=xlValues, lookat:=xlWhole)
            If Not f Is Nothing Then
                mount = f.Offset(, 3).Value
                wdate = f.Offset(, 1).Value
                Set d = sh.Range("A:A").Find(wdate, LookIn:=xlValues, lookat:=xlWhole)
                If Not d Is Nothing Then
                    sh.Cells(d.Row, "B").Value = mount
                End If
            End If
        
            'search in sheet2
            Set f = sh2.Range("A:A").Find(num, LookIn:=xlValues, lookat:=xlWhole)
            If Not f Is Nothing Then
                mount = f.Offset(, 2).Value
                wdate = f.Offset(, 1).Value
                Set d = sh.Range("A:A").Find(wdate, LookIn:=xlValues, lookat:=xlWhole)
                If Not d Is Nothing Then
                    sh.Cells(d.Row, "F").Value = mount
                End If
            End If
        End If
    Next
    MsgBox "End"
End Sub

File test:

https://www.dropbox.com/s/2wqrhyfdgfjebkh/GGI - Daily Earnings dam.xlsm?dl=0