PDA

View Full Version : Solved: Copy Values from Information Tab to Main Tab



jo15765
03-26-2013, 06:52 AM
I found this thread which is pretty close to what I want to accomplish, but not close enough for me to ammend and have it work for me... http://www.vbaexpress.com/forum/showthread.php?t=45749

What I want to do is find each instance of Employee Name: on the Main tab and go to the Information tab and find the same employee name and copy over the information.

This must be done through VBA as it is going to be a Excel UserForm botton Event.

I am attaching a sample workbook to hopefully help clarify any ?'s

SamT
03-26-2013, 03:04 PM
I'm just rephrasing what I think you want

For Each NAME on the DB Sheet("Information")
Format Values as indicated on Sheet("Main")
Insert new Values into a virtual Range
Format the virtual Range borders.
Find the NAME on Sheet("Main")
Replace block(Bordered) of NAME.cells on Main with the virtual Range
End With

jo15765
03-26-2013, 03:14 PM
I'm just rephrasing what I think you want

For Each NAME on the DB Sheet("Information")
Format Values as indicated on Sheet("Main")
Insert new Values into a virtual Range
Format the virtual Range borders.
Find the NAME on Sheet("Main")
Replace block(Bordered) of NAME.cells on Main with the virtual Range
End With

Sounds just like what I need m8

jo15765
03-29-2013, 08:13 AM
BUMP!

Anyone have any possible solutions?

mdmackillop
03-29-2013, 09:51 AM
Option Explicit

Sub Macro1()
Dim Sht As Worksheet
Dim EName As String
Dim c As Range, r As Range
Dim txt As String, FirstAddress As String

txt = "Employee Name:"
Set Sht = Sheets("Information")
With Worksheets("Main").Cells
Set c = .Find(txt, LookIn:=xlValues)
If Not c Is Nothing Then
Set r = c
FirstAddress = c.Address
Do
Set c = .FindNext(c)
Set r = Union(r, c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

For Each c In r
EName = Trim(Split(c, ":")(1))
Set r = Sht.Columns(1).Find(EName)
c.Offset(1) = r.Offset(, 1)
c.Offset(2) = r.Offset(, 2) & " " & r.Offset(, 3) & ", " & r.Offset(, 4)
c.Offset(3) = r.Offset(, 5)
Next
End Sub

jo15765
03-29-2013, 11:09 AM
Thank you for that. I am getting a debug error of subscript out of range on the line of code that states this

EName = TRIM(SPLIT(c,":")(1))

mdmackillop
03-29-2013, 11:31 AM
Check your data!

jo15765
03-29-2013, 12:06 PM
Check your data!

Oh silly typo on my end. Gotta remember to use the copy paste!

It's populating Michael Jordan with Jorge Pasada's info...and not populating Jorge Pasada at all?

mdmackillop
03-29-2013, 12:37 PM
This works for me. Try stepping though the code to identify the issues.