• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy data from one worksheet to another using Vlookup macro

I

iceblocks

Member
Hi all,
I am new to macro so would really appreciate your help.

I need a macro that allows me to click a button and then copy data from worksheet 1 and paste into worksheet 2. However, when pasting the data into worksheet 2, I need it to search according to its ID number and paste the data according to its ID number.

I have attached a simplified workbook on this. Basically, I will type data into cells B1:B3 in the ‘input’ tab. Then with a click of a button, the macro will paste data at cells B2 and B3 into the ‘all staff’ tab according to the ID number listed in the ‘all staff’ master tab. Then if possible, after data has been pasted, a message box that pops up to say data copied successfully.

Many thanks in advance.
Iceblocks
 

Attachments

  • Book10.xlsx
    8.8 KB · Views: 135
Last edited:
Sorry Nebu, would you be able to help again?

As a slight extension to this, I need to add a messagebox after I have copied the data from sheet 1 to sheet 2. I have tried several ways but it doesn't seem to work when I click the Yes button (the no button is working fine).
I have tried application.goto and tried activating sheet2 first then go to the cell, hence any help would be truly grateful.

I have inserted the following codes after the line 'Application.ScreenUpdating = False', as per your previous post.

Code: 
 Dim Ans As Integer
Ans = MsgBox("Data copied successfully. Do want to view it in All staff master sheet?", vbYesNo, "Copy to summary")
If Ans = vbYes Then
  Sheet2.Range("B" & i).Select
 
ElseIf Ans = vbNo Then
  Sheet1.Range("B2").Select
End If

Thanking you advance.
 
Code: 
Sub staff()
Application.ScreenUpdating = False

Dim i, j As Long
Dim Ans As Integer

j = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
i = Application.WorksheetFunction.Match(Sheet1.Range("B1"), Sheet2.Range("A2:A" & j), 0) + 1

Sheet1.Range("B2").Copy
Sheet2.Range("B" & i).PasteSpecial xlValues
Sheet1.Range("B3").Copy
Sheet2.Range("C" & i).PasteSpecial xlValues
Application.CutCopyMode = False

Ans = MsgBox("Data copied successfully. Do want to view it in All staff master sheet?", vbYesNo, "Copy to summary")
If Ans = vbYes Then
    Sheet2.Activate
ElseIf Ans = vbNo Then
    Exit Sub
End If

Application.ScreenUpdating = False
End Sub

Try this code it will activate sheet 2 if you give yes else it will exit the macro.

Hope this is what you meant.

Thanks
 
An alternative method. Although perhaps just a simple Vlookup would have been my preference.

Code: 
Sub staff2()
Dim fnd As Long
Dim Ans As Integer

fnd = Sheet2.Columns(1).Find([B1]).Row
[B2:B3].Copy
Sheet2.Range("B" & fnd).PasteSpecial xlPasteAll, , , True

Ans = MsgBox("Data copied successfully. Do want to view it in All staff master sheet?", vbYesNo, "Copy to summary")
If Ans = vbYes Then Sheet2.Activate
End Sub

Take care

Smallman
 
Dear all,
I am new in macro.
Herewith attached file for your reference.
I'll entry data in entry sheet of B2:F2 then If I will click save button that the Cont' No. will search to Next 2 sheets P.M Heavy and P.M Vehicle then will copy and go to paste of that column using Macro VLookup or some other function.

Thanking you advance.
 

Attachments

  • P.M Consumable Details for Feb-2015.xlsx
    885 KB · Views: 34
Hi:

This should have started as a new thread. I have done the macro as requested.
Please find the attached.

Thanks
 

Attachments

  • P.M Consumable Details for Feb-2015.xlsm
    894.9 KB · Views: 92
Dear Nebu,
Thank U So much for solved the above.
If u don't mind, I have attached one more file for your reference.
There are two sheets,1)Name List 2)Attn' sheet.
If I click Print button without data entry of S.No. from & to that It'll Print(Attn' sheet) one by one all S.No.(Name List Sheet) with fill data A7:H7 from take Name List Sheet, otherwise If I'll entry the S.No. From 1 to 10 that it'll print only 1-10 S.No's. with fill data one by one A7:H7.

Print will come to default printer in my system.

Thanking You Advance.
 

Attachments

  • ATTN\'SHEET.xlsx
    21.2 KB · Views: 21
Hi:

What do you meant by print one-by-one, you mean to say if you are not giving any value in from and to you need to print 37 separate sheets with info pulled from names list tab Column a thru H? Please clarify.

Thanks
 
Hi Nebu,

Yes, I'll not giving any value in from and to that I need print 37 separate sheet with pulled 7th Row (Nationality,Name,Id.No.JOb Title).If I'll give a value that I need print in particular sheets.

Thanks
 
Hi:

Please find the attached. I have tested the macro only by using print preview . You can run it at your end and let me know how it goes.

Thanks
 

Attachments

  • ATTN\'SHEET.xlsm
    26.1 KB · Views: 28
Hi Nebu,
Actually your macro function is working only one S.Number in attn' sheet and NOT working Print Preview. I mean If I give value in from is 1 and to is 5 that 1st s.no data only viewed in attn' sheet. So try once again or leave it.
And I am removed s.no of from and to value. Now just only one print button. So If I click print button that I need print separate (one by one) attn' sheet 1-37 with info pulled from name list.
 

Attachments

  • ATTN_\'SHEET-01.xlsm
    28.7 KB · Views: 6
Hi:

The macro is working fine. The issue is with what you want to get printed in your print outs (your explanation was not super clear to me about what you want to print) , if you can upload a sample output of what you are looking for it is just a 5 minute job to set the macro accordingly or else,If you have some idea on the coding you urself can change it in the macro.

Forget abt print preview . I did print preview so that it won't print at my end in your macro I have set it as print not print preview.

Thanks
 
Hi Nebu,
Your macro is working fine. But run time error "438" is coming.
Actually I'm new in macro.Now Pls find attached file FYR.
If I Click print button that I need print attn' sheet with pulled row no.7 of Nationality, Id.no, Job Title and Name taken from sheet1.Bcz attn' sheet format is same but I need separate print for all data how many data in sheet1.Here 37 data is available in sheet1 if 10 data that only print 10 sheet with pulled R7.
Thanks,
 

Attachments

  • ATTN_\'SHEET-01.xlsm
    29.8 KB · Views: 5
Nebu said:
Hi:

This should have started as a new thread. I have done the macro as requested.
Please find the attached.

Thanks
Click to expand...


Hi Nebu,

Your macro is working fine on the above file of PM Consumable Details. But I want to clear one more in that macro file. It is, If I give value in B2 & C2 that D2:F2 value is copy and paste to sheet2 file. For Example,1st time I give value in B2 is BD-001,C2 is 2015-02-01,then I'll give value in D2 only (Bcz 1st time I am entry to Eng' Oil Reports only) not give value in E2&F2.But D2:F2 value is copied and paste. Then 2nd time entry to Hyd' Oil Reports. That time B2&C2 same and I give value in E2 only not D2&F2 but D2:F2 value copy and paste to sheet2 file. So In this time my first value is removed,D2:F2 is pasted. But I need particular value only copy to paste. I mean, 1st If I give D2 value that only copy and paste D2 value.2nd, I give E2 value that only copy and paste E2 value not D2:F2.Therefore, Kindly Clear the problem.I hope you understand my problems. Thanks for advance.
 
Hi:

Please find the attached. I have fixed the macro.

Thanks
 

Attachments

  • P.M Consumable Details for Feb-2015.xlsm
    895.2 KB · Views: 54
Hi All,

I am looking for a VBA code to extract the data from one sheet to another using vlookup function.

In the attached sheet, I have created a formula based on the lookup values (Name, Date and Time) to copy “Column E” values from “Raw” and paste into “BTU” accordingly. However, when I made the “RAW” is empty, the cells in “BTU” are showing “#N/A” due to formula.

Therefore, I am looking for VBA code to perform copy/paste values from “Raw” in to “BTU”. If RAW sheet is empty then the BTU also should show empty cells.

Also, once the values are copied into BTU, if we change the values in Raw and run the macro again, it should replace the old values copied in BTU.

I have tried to create the macro by myself (through googling) and I couldn’t understand anything as I have zero knowledge in VBA code writing.

I would really appreciated if anyone help me to draft the Macro for this action?

Thank you in advance
 

Attachments

  • Working Sheet_Ref.xlsm
    513.7 KB · Views: 38
You must log in or register to reply here.
Back
Top