VBA to pull dat from one sheet to anther

Microsoft Office excel 2007 - upgrade
November 24, 2010 at 11:17:26
Specs: Windows XP
I have been searching for hours for a solution that will work. Hoping some one here can help me.

I need a macro solution to compare data in Sheet1, column D and compare that to Sheet2 column A. Where they match, I want to have the value in Sheet2 column C to be copied to Sheet 1 column A.

Thanks!


See More: VBA to pull dat from one sheet to anther

Report •

#1
November 24, 2010 at 12:13:45
It doesn't sound like you need a macro.

Have you tried VLOOKUP?

In Sheet1 Column A you would use something like this:

e.g. Sheet1!A1:

=VLOOKUP(D1, Sheet2!$A$1:$C$100, 3, 0)

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 24, 2010 at 12:18:18
True - I can do this using Vlook-up. Problem is that the data on Sheet 1 will get refreshed daily, so I would have to build and copy the formula for the hundreds of rows in the table every time the data is refreshed as it method for refresh would clear the entire sheet. My preference is to do this as a macro...unless I can't.

Report •

#3
November 24, 2010 at 14:05:52
Well, in that case, we could write a macro to loop through Sheet1 Column D and find the data in Sheet2 Column A and move over to Column C and copy the data back to Sheet1, or...

We could write a macro to place the VLOOKUP formula in Sheet 1 Column A.

Sub PlaceVlookup()
    Sheets(1).Range("A1:A100").FormulaR1C1 = _
       "=VLOOKUP(RC[3],Sheet2!RC:R[99]C[2],3,0)"
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
November 24, 2010 at 14:26:10
Yeah....we could do that...of course your example has a fixed range, I'd have to substitute that and have a variable range since the data is always changing...

I have concerns with having a formula in the worksheet...based on nothing concrete other than knowing my audience and how they like to manipulate the spreadsheets...I was trying to keep it simple so the executives could have there way with the document. I've been bitten before by them moving columns adding columns and the like. I was thinking this would be cleaner, then I could also get rid of sheet2 when I sent it out. Again - so as not to confuse or tempt those receiving the data to make my life a nightmare. :-)

And maybe it's too difficult to do that...and I should just go and the formula...then I can do a copy and paste special, values only to accomplish what I was looking to do.


Report •

#5
November 24, 2010 at 17:32:42
- Variable range based on Sheet1 Column D
- VLOOKUP used to get data
- VLOOKUP formulas replaced with values
- Sheet 2 deleted.

Sub PlaceVlookup()
'Determine last row based on Column D
  lastRw = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
'Insert formula using lastRw variable
  Sheets(1).Range("A1:A" & lastRw).FormulaR1C1 = _
       "=VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0)"
'Copy/PasteSpecial Values
  With Sheets(1).Range("A1:A" & lastRw)
   .Copy
   .PasteSpecial Paste:=xlPasteValues
  End With
'Delete Sheet2 without warning
  Application.DisplayAlerts = False
   Sheets(2).Delete
  Application.DisplayAlerts = True
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
November 24, 2010 at 18:14:58
Thanks for the help...but that didn't work. All it displayed in column A of Sheet 1 was the formula =VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0)

When I selected one of the cells and hit F2 and enter, it gave me an error...formula you typed contains an error. After pressing ok it highlights the RC[3].


Report •

#7
November 24, 2010 at 19:40:41
You said something about the data being refreshed. How is that done? Many methods of refreshing worksheets force the cell format to be Text. Excel has a nasty habit of retaining that Text format even when you rather it wouldn't.

Try this version. It formats the Column A range as General before placing the formula in the cell.

Sub PlaceVlookup()
'Determine last row based on Column D
  lastRw = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
'Format Column A range as General
  Sheets(1).Range("A1:A" & lastRw).NumberFormat = "General"
'Insert formula using lastRw variable
  Sheets(1).Range("A1:A" & lastRw).FormulaR1C1 = _
       "=VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0)"
'Copy/PasteSpecial Values
  With Sheets(1).Range("A1:A" & lastRw)
   .Copy
   .PasteSpecial Paste:=xlPasteValues
  End With
'Delete Sheet2 without warning
  Application.DisplayAlerts = False
   Sheets(2).Delete
  Application.DisplayAlerts = True
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#8
November 24, 2010 at 19:59:37
Thank You so much for your help! It works!!!

Report •

#9
November 30, 2010 at 14:38:22
So I began looking through the hundreds of lines of data this week and discovered that the formula/code you helped me with is not working 100%. for one particular value I was expecting to get 20 matches. Instead I only recieved 9. When I added a new column to sheet1 and physically typed in a a vlookup formula I was able to get 19 matches. I think there is one data issue. Any thoughts on why it's not working? Does it matter that neither sheet1 nor sheet2 is sorted in any particular order?


Report •

#10
November 30, 2010 at 17:34:59
Since I can't see your workbook from where I'm sitting, it's kind of hard for me to tell you why you aren't getting the results you expect.

All my code does is put VLOOKUP formulas in a column, then Copy/Paste Values over them.

You could try this:

This is the same code with the sections that Paste Values and deletes Sheet 2 commented out.

When it's done, you'll be left with the formulas. Compare your formulas to mine and see what's different.

Sub PlaceVlookup()
'Determine last row based on Column D
  lastRw = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
'Format Column A range as General
  Sheets(1).Range("A1:A" & lastRw).NumberFormat = "General"
'Insert formula using lastRw variable
  Sheets(1).Range("A1:A" & lastRw).FormulaR1C1 = _
       "=VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0)"
''Copy/PasteSpecial Values
'  With Sheets(1).Range("A1:A" & lastRw)
'   .Copy
'   .PasteSpecial Paste:=xlPasteValues
'  End With
''Delete Sheet2 without warning
'  Application.DisplayAlerts = False
'   Sheets(2).Delete
'  Application.DisplayAlerts = True
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#11
November 30, 2010 at 18:23:04
Thanks for the help. I actually figured out another way to get the same results by tweaking some of your code. Thank you for all the help!

Report •

Ask Question