none
Copy Data from Sheet 2 to Sheet1 with VBA RRS feed

  • Question

  • I don't see an option for uploading a spreadsheet that would be a hands on example....

    On my sheet2, there is a query that is set-up to this field mappings

    ComputerName   EmployeeName  PhoneExt   Onsite

    And this query returns over 1,000 results....And if the Computer name is returned on Sheet2, it exists on sheet1 EVERY time.  So what I am wanting to do is, iterate through each ComputerName on Sheet2, find the corresponding computer name on Sheet1 then copy over EmployeeName, PhoneExt and Onsite to sheet1. This is how I want the vba to set-up worksheet1

    'This is where EmployeeName would go

    ComputerName

    'This is where PhoneExt would map to

    'This is where Onsite would map to

    Monday, April 22, 2013 2:37 AM

Answers

  • If you use the $ sign between the range you can drage the code aross the cells.

    Then when you need to move down to the next set of boxes set up the 1st box by manually entering the data, and again if you use the $ signs you can just drag the code over.

    A benefit of using the VLookup is that, let's say for example that on your worksheet that contains the real data, data gets input on line 100, if you have your worksheet with the boxes alread mapped to row 100, then the box will automatically update as data is entered onto the (as I called it) 'real data' worksheet.

    Does that help clarify any questions?

    • Marked as answer by toptierdawg03 Monday, April 29, 2013 1:09 PM
    Sunday, April 28, 2013 6:08 PM

All replies

  • I don't see an option for uploading a spreadsheet that would be a hands on example....

    Use Skydrive.


    The following link contains instructions for Skydrive. 

    http://windows.microsoft.com/en-US/skydrive/change-access-permissions-faq 

    The first step of the instructions contains a link to Skydrive. 

    Use the same Login Id and password as you use for this forum. 

    You should zip any files containing VBA code before uploading them. 

    When you right click the uploaded file and select sharing, don't fill in the form; select "Get a Link" in the left column and then Create for anyone who has the link. Copy the highlighted link and paste into your reply.


    Regards, OssieMac

    Monday, April 22, 2013 3:05 AM
  • How about creating a vlookup function with your VBA code or manually create it? That would do what you want. One Vlookup for each column looking up the computer name. See help for details and an example.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, April 22, 2013 3:25 AM
  • I don't see an option for uploading a spreadsheet that would be a hands on example....

    Use Skydrive.


    The following link contains instructions for Skydrive. 

    http://windows.microsoft.com/en-US/skydrive/change-access-permissions-faq 

    The first step of the instructions contains a link to Skydrive. 

    Use the same Login Id and password as you use for this forum. 

    You should zip any files containing VBA code before uploading them. 

    When you right click the uploaded file and select sharing, don't fill in the form; select "Get a Link" in the left column and then Create for anyone who has the link. Copy the highlighted link and paste into your reply.


    Regards, OssieMac

    Okay, I uploaded a template file to skydrive and I believe I set this up properly for others to view.  Thanks for viewing and I look forward to hearing your solutions.

    File

    Monday, April 22, 2013 6:28 AM
  • How about creating a vlookup function with your VBA code or manually create it? That would do what you want. One Vlookup for each column looking up the computer name. See help for details and an example.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    A VLookup would probably work, I am wanting to be able to open the workbook, have the query refresh on workbook open, and have the mappings update once a button is pressed.  If that is something that a VLookup can accomplish I am def game! :)
    Monday, April 22, 2013 6:29 AM
  • I was toying with the VLookup function and I have this formula which pulls in the EmployeeName for me.

    =VLOOKUP(B3,Sheet2!$A$2:$D$5,2,TRUE)


    However, I am manually having to hardcode which cell I want to use as the lookup Value.  Is there a way to be able to have this auto-change easier?  Like being able to drag the forumula to the next cell etc?

    EDIT:

    As you can see on my sample spreadsheet when I try to drag the VLookup across empty cells it gives me a NA value for those cells, I don't want that to happen either.

    Tuesday, April 23, 2013 2:59 AM
  • As you can see on my sample spreadsheet when I try to drag the VLookup across empty cells it gives me a NA value for those cells, I don't want that to happen either.

    I can't see your sample workbook because I can't find where you copied the link to the workbook as per my post where I said "When you right click the uploaded file and select sharing, don't fill in the form; select "Get a Link" in the left column and then Create for anyone who has the link. Copy the highlighted link and paste into your reply."

    To be sure of what you want then need to see the example workbook. However, you can use variations of the absolute addressing to fix columns or rows in the formula.

    If you always want it to address column B but allow the row to change then place a $ sign in front of the column and no $ sign in front of the row like the following that will always reference column B for the lookup value but the row will change if the formula is copied down.

    =VLOOKUP($B3,Sheet2!$A$2:$D$5,2,TRUE)


    Regards, OssieMac

    Tuesday, April 23, 2013 7:03 AM
  • As you can see on my sample spreadsheet when I try to drag the VLookup across empty cells it gives me a NA value for those cells, I don't want that to happen either.

    I can't see your sample workbook because I can't find where you copied the link to the workbook as per my post where I said "When you right click the uploaded file and select sharing, don't fill in the form; select "Get a Link" in the left column and then Create for anyone who has the link. Copy the highlighted link and paste into your reply."

    To be sure of what you want then need to see the example workbook. However, you can use variations of the absolute addressing to fix columns or rows in the formula.

    If you always want it to address column B but allow the row to change then place a $ sign in front of the column and no $ sign in front of the row like the following that will always reference column B for the lookup value but the row will change if the formula is copied down.

    =VLOOKUP($B3,Sheet2!$A$2:$D$5,2,TRUE)


    Regards, OssieMac

    In my above post I have the word File in blue and that is a link to the file I uploaded.  Does that not work for allowing you to view it?  I followed the instructions and said anyone with the link can view/edit?

    Or try this link...  File

    • Edited by toptierdawg03 Tuesday, April 23, 2013 10:52 AM added link to workbook
    Tuesday, April 23, 2013 10:48 AM
  • I edit this file for you using formula.

    regards


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, April 23, 2013 11:47 AM
    Answerer
  • I edit this file for you using formula.

    regards


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Is there a quick way to fill in all the 'boxes' on my worksheet, or will I have to manually input that formula for all of the blanks?

    (The workbook itself actually has close to 1,000 boxes I need to fill in)

    Tuesday, April 23, 2013 12:07 PM
  • If you use the $ sign between the range you can drage the code aross the cells.

    Then when you need to move down to the next set of boxes set up the 1st box by manually entering the data, and again if you use the $ signs you can just drag the code over.

    A benefit of using the VLookup is that, let's say for example that on your worksheet that contains the real data, data gets input on line 100, if you have your worksheet with the boxes alread mapped to row 100, then the box will automatically update as data is entered onto the (as I called it) 'real data' worksheet.

    Does that help clarify any questions?

    • Marked as answer by toptierdawg03 Monday, April 29, 2013 1:09 PM
    Sunday, April 28, 2013 6:08 PM
  • I edit this file for you using formula.

    regards


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Is there a quick way to fill in all the 'boxes' on my worksheet, or will I have to manually input that formula for all of the blanks?

    (The workbook itself actually has close to 1,000 boxes I need to fill in)

    Hi

    You can use the Range.Formula property (http://msdn.microsoft.com/en-us/library/office/ff838835.aspx)

    http://msdn.microsoft.com/en-us/library/office/ff823188.aspx

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com

    Monday, April 29, 2013 10:11 AM