Solved Can Excel Lookup return values be saved rather than overwrit

May 28, 2012 at 10:00:23
Specs: Windows 7
We have an Excel spreadsheet with a data input sheet which uses lookup/Vlookup to transfer data to an invoice, a PO and a Job Register register sheet. The job register sheet just uses a sequential number in column A and all details are then shown under the relavent subsequent columns.

Is there a way to save the lookup data automatically on each row of the register page without having to highlight it, copy it and then paste values? Obvioulsy when we use the next number and type it into the data input sheet the previous row on the register sheet returns nothing and/or errors.


See More: Can Excel Lookup return values be saved rather than overwrit

Report •


✔ Best Answer
May 29, 2012 at 08:12:28
First, before posting any more code or data in this forum, please click on the blue line at the end of this post and read the instructions found via that link.

Second, your code can be simplified. Rarely is there a need to Select an object in VBA in order to perform a VBA operation in it. This code will do the same thing as yours:

Sub CopyRegister()
  Sheets("Register").Rows("2:2").Copy
  Sheets("Register").Rows("2:2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

Finally, I think this is what you are asking for. I suggest you try this in a backup copy of your workbook since macros cannot easily be undone.

Sub CopyRegisterV2()
'Set JobNum variable to value in Job Card!D1
 JobNum = Sheets("Job Card").Range("D1")
'Find JobNum in Register!A:A
  With Sheets("Register").Range("A:A")
   Set j = .Find(JobNum, LookIn:=xlValues, lookat:=xlWhole)
'If found, Paste ValuesAndNumberFormats in Register Row with matching JobNum
    If Not j Is Nothing Then
      Sheets("Register").Range("A" & j.Row).EntireRow.Copy
      Sheets("Register").Range("A" & j.Row).PasteSpecial _
             Paste:=xlPasteValuesAndNumberFormats
'Display message if value not found.
     Else: MsgBox "Job Number Not Found"
    End If
  End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 28, 2012 at 10:49:49
I have also tried using a Macro on a button at the bottom of the data input sheet to do the copy and paste of the row in the register sheet but then I dont know how to get that to move down a row each time it performs? maybe some visual basic wizards may be able to help on this?

Report •

#2
May 28, 2012 at 16:45:18
Perhaps if you posted the macro you are using we could suggest a modification to have it paste to the next empty line.

Typically, finding the next empty row is done using code similar to this, which assumes you are checking Column A:

nextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

You would then use the number that nextRow returns as the Row number. e.g.

Range("A" & nextRow).Paste

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 29, 2012 at 01:47:28
Thanks for your reply. The simple Macro I used was as below. However after thinking about what I need to do, is there a way to create a macro that looks at the job card number which is typed into cell D1 on the job card sheet, then copy and pastes the row in the register sheet that has the same number in column A (the reason for the copy and paste is to turn that line from looking up the values from the job card so that when the next job card number is typed in the relevant row in the register sheet doesn't go blank)

Sheets("Register").Select
Rows("2:2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Job Card").Select
End Sub


Report •

Related Solutions

#4
May 29, 2012 at 08:12:28
✔ Best Answer
First, before posting any more code or data in this forum, please click on the blue line at the end of this post and read the instructions found via that link.

Second, your code can be simplified. Rarely is there a need to Select an object in VBA in order to perform a VBA operation in it. This code will do the same thing as yours:

Sub CopyRegister()
  Sheets("Register").Rows("2:2").Copy
  Sheets("Register").Rows("2:2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

Finally, I think this is what you are asking for. I suggest you try this in a backup copy of your workbook since macros cannot easily be undone.

Sub CopyRegisterV2()
'Set JobNum variable to value in Job Card!D1
 JobNum = Sheets("Job Card").Range("D1")
'Find JobNum in Register!A:A
  With Sheets("Register").Range("A:A")
   Set j = .Find(JobNum, LookIn:=xlValues, lookat:=xlWhole)
'If found, Paste ValuesAndNumberFormats in Register Row with matching JobNum
    If Not j Is Nothing Then
      Sheets("Register").Range("A" & j.Row).EntireRow.Copy
      Sheets("Register").Range("A" & j.Row).PasteSpecial _
             Paste:=xlPasteValuesAndNumberFormats
'Display message if value not found.
     Else: MsgBox "Job Number Not Found"
    End If
  End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 31, 2012 at 08:34:26
Thank you very much for your help. Your code has soved the problem... much appreciated!

Report •

Ask Question