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

Abhishekghorpade

Board Regular
Joined
Oct 3, 2018
Messages
78
[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]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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..
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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