Excel vba find and copy cells

Hello Experts,

I am looking for assistance in writing code to copy the values in one workbook to another workbook. There are a some variables that are complicating it for me to put together.

I am looking for a macro to reside in wkbk 1; to copy/paste data from each sheet of wkbk 2 into active sheet of wkbk 1.

The working cells in wkbk 1.sheet("KEY DATA") are Range("J3:U13")

There are approximately 9 worksheets in the second workbook, depending on user.
The 9 worksheet names correspond to column "A" matches in active worksheet.  
There are 12 cell values in each worksheet to copy to active worksheet. It is always this 12.
In the attached workbooks - there would be 108 cell values copied and pasted

I am attaching the 2 workbooks to help understand my request.....

What is making it difficult for me is trying to do a function.match for row #'s.

I need to match each value in wkbk 1 Range("J1:U1") to wkbk 2 Range("K9:K88") to identify correct row number. Then copy that cell data that is offset. The row # will be different for different users.
...And I maybe....
I need to match each value in wkbk 1 Range("A1:A13") to wkbk 2 'sheet names' to identify correct row number that is my active cell. But I am not sure I need to do it this way.

First cell would be wkbk1.sheet("KEYDATA").range("J3") = wkbk2.sheet("0004").range("J17"),
then range("K3") is pasted, then the next column... until column "U" is reached
The next worksheet in the workbook is activated and the same process is duplicated.

My source data workbook is:
  source wkbk = Workbooks(i).Name Like "CRG OPERATING PARTNER*"
             I have different users, but all my source workbooks will start with this name.

In the source wkbk, starting with the second worksheet to the last it should be pulling the offset of column "K"

"Food Costs" .offset(0,-1)
"Total Labor" .offset(0,-1)
"Paper Costs" .offset(0,2)
"Gross Profit" .offset(0,-1)
"Cash & Credit Card Over/Short" .offset(0,-1)
"Supplies" .offset(0,-2)
"Cleaning Supplies" .offset(0,-2)
"Equipment Maintenance" .offset(0,-2)
"Technology R&M" .offset(0,-2)
"Building Maintenance" .offset(0,-2)
"Store Operating Income (Loss)"  .offset(0,-2)
"Store Operating Income (Loss)"  .offset(0,-8)

I tried different ways but I am hitting a wall.  Hence I am asking for assistance.
I deleted all my code since it was pretty botched up.  But I can retype & resend.

I thank you kindly for any help you can give me.
Who is Participating?
RobConnect With a Mentor Owner (Aidellio)Commented:
Thanks for posting the workbooks but I'll come back to that after you have a look at this.  You need to have the CRG workbook open.
RobOwner (Aidellio)Commented:
If I'm on the same page then I was able to get your values without a macro, using the INDIRECT function:

Gross Profit % (J28)
=INDIRECT("'[CRG-OPERATING-PARTNER-830-P-L-FI.xlsx]" & RIGHT("000" & $A3,3) & "'!J28",TRUE)

You then modify the J28 to what ever row you need

Foodcost (J17):
 =INDIRECT("'[CRG-OPERATING-PARTNER-830-P-L-FI.xlsx]" & RIGHT("000" & $A3,3) & "'!J17",TRUE)
mike637Author Commented:
Hi Rob,

Thank you for reviewing my question.  However, I do need it to reside in background code.

Hopefully I will get the needed assistance.

Have a great day,
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

RobOwner (Aidellio)Commented:
No problem Michael.  Try the easiest route first. I'll take a more in depth look at your code
RobOwner (Aidellio)Commented:
Let's start with this and build on it.  I realise the references are hardcoded so I'll need to know from you where the faults are with this.
Looping over multiple workbooks is separate and easy to implement.  This macro would just be called each time you open another workbook.

Sub GetInfo()
    Dim res As String
    Dim arr As Variant
    Dim i, j As Integer
    arr = Array("J17", "M18", "J24", "J28", "I47", "I41", "I42", "I47", "I70", "I71", "I72", "I84")
    For j = 0 To UBound(arr)
        For i = 3 To 13
            If arr(j) <> "" Then
                Cells(i, (10 + j)).Value = Evaluate("='[CRG-OPERATING-PARTNER-830-P-L-FI.xlsx]" & Right("000" & Cells(i, 1).Value, 3) & "'!" & arr(j))
            End If
        Next i
    Next j
End Sub

Open in new window

mike637Author Commented:
Hi Rob,

Wow - this is great start.  I did not think of going about it this way.

The faults identified are in the source workbook, which I can declare with a "Like" and wild card since all end user source workbooks start with the same 24 characters.

My main issue is the hard-coded array.   I am wondering if a worksheet function match would be better suited.  Perhaps using the [DATA] worksheet.
The reason I state this is because the row numbers are varied i.e. Row 84 data, from worksheet to worksheet in the source workbook.

Is this something you can help me with?  I believe after I have that resolved - I am situated.

Thank you for all you have done!
RobOwner (Aidellio)Commented:
Ok, that's going to take a bit longer to achieve.  As I see it you need to lookup the column header in the DATA sheet and get the name it's called in your CRG workbook.

At this point the code needs to find that and return the row that heading is on.

Is it possible to continue the work you've done on the DATA sheet to include the offset?  I understand that the Foodcost % may be on a different row each time but the column of info must always be the same.  If it's going to change ie on one sheet its L28 and another O28 (for arguments sake) then we'll have to rethink everything.  In otherwords the offset needs to be hardcoded on the DATA sheet to make this work.  Is that ok?
mike637Author Commented:
Yes that is OK.

The column of info is always the same, and will never change. It is the row number that becomes the variable.  That being stated, the hard-coding of the offset is workable.  And the look-up nomenclature will never change and will always be consistent for every end-user.

Incidentally - I have been toying with declaring the workbook name - but I am not used to the 'EVALUATE' function.  I have something wrong obviously in the function line.

This is the code I added...

 Dim xSrce_Book As Workbook

 For i = 1 To Application.Workbooks.Count
        If Workbooks(i).Name Like "CRG OPERATING PARTNER*" Then
               Set xSrce_Book = Workbooks(i)
            Exit For
        End If
    Next I

If xSrce_Book Is Nothing Then
            Set xSrce_Book = Nothing
        Exit Sub
    End If

when I go to modify this line with the declared name - it does not like it ..........
Cells(i, (10 + j)).Value = Evaluate("=" xSrce_Book & Right("000" & Cells(i, 1).Value, 3) & "'!" & arr(j))

What am I getting wrong on the Evaluate function?

Thank you again for everything!!
RobOwner (Aidellio)Commented:
The easiest thing about the EVALUATE function is you can just use a formula like you would in the spreadsheet.
In your mbs workbook, go to any blank cell, press f2 to edit, type = to start a formula. Now change to an open CRG spreadsheet and select any cell it doesn't matter which, we're only after the reference.
you've done it right you'll see the CRG workbook name before cell reference.
You'll also see that you're just missing the square brackets around the name in your function but you can compare and test now
mike637Author Commented:
Hi Rob,

I added the brackets as you instructed... However it does not seem to play right with the declared name of xSrce_Book.  
It errors out immediately with: Compile error: Expected: list separator or )

Could there be something else that I am missing with statement?

RobOwner (Aidellio)Commented:
Cells(i, (10 + j)).Value = Evaluate("=" xSrce_Book & Right("000" & Cells(i, 1).Value, 3) & "'!" & arr(j))

should be

Cells(i, (10 + j)).Value = Evaluate("='[" & xSrce_Book & "]" & Right("000" & Cells(i, 1).Value, 3) & "'!" & arr(j))
mike637Author Commented:
Hi Rob:

I copied & pasted your code - getting a Run-Time error '438' that Object doesn't support this property or method.

RobOwner (Aidellio)Commented:
Then it's something I can't see. Can you attach your workbook and I'll take a look?
mike637Author Commented:
As instructed....

2 attachments
mike637Author Commented:
Thank you Rob for all your work.

All the code you wrote worked perfectly.  I added the personalized pieces and set the offsets and it worked great.

I wish I could award more than 500.  This will be helpful to many!!

RobOwner (Aidellio)Commented:
Thanks Michael, appreciate the points
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.