Solved

Excel vba find and copy cells

Posted on 2014-01-14
16
763 Views
Last Modified: 2014-01-19
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.
MBS.xlsm
CRG-OPERATING-PARTNER-830-P-L-FI.xlsx
0
Comment
Question by:mike637
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 43

Expert Comment

by:Rob
ID: 39784362
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)
MBS.xlsm
0
 

Author Comment

by:mike637
ID: 39784495
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,
Michael
0
 
LVL 43

Expert Comment

by:Rob
ID: 39784631
No problem Michael.  Try the easiest route first. I'll take a more in depth look at your code
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 43

Expert Comment

by:Rob
ID: 39787313
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
    range("J3:U13").clear
    
    arr = Array("J17", "M18", "J24", "J28", "I47", "I41", "I42", "I47", "I70", "I71", "I72", "I84")
    
    KEYDATA.Activate
    
    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

MBS.xlsm
0
 

Author Comment

by:mike637
ID: 39787415
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!
Michael
0
 
LVL 43

Expert Comment

by:Rob
ID: 39787624
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?
0
 

Author Comment

by:mike637
ID: 39788383
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
        Beep
            MsgBox "CRG OPERATING PARTNER P&L 3) FINAL" & vbCr & " EXCEL FILE IS NOT OPEN"
            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!!
0
 
LVL 43

Expert Comment

by:Rob
ID: 39789609
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
0
 

Author Comment

by:mike637
ID: 39789975
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?

Michael
0
 
LVL 43

Expert Comment

by:Rob
ID: 39789994
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))
0
 

Author Comment

by:mike637
ID: 39790006
Hi Rob:

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

Michael
0
 
LVL 43

Expert Comment

by:Rob
ID: 39790035
Then it's something I can't see. Can you attach your workbook and I'll take a look?
0
 

Author Comment

by:mike637
ID: 39790044
As instructed....

2 attachments
NEW-MBS.xlsm
CRG-OPERATING-PARTNER.xlsx
0
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39792011
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.
MBS.xlsm
0
 

Author Closing Comment

by:mike637
ID: 39792913
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!!

Michael
0
 
LVL 43

Expert Comment

by:Rob
ID: 39792992
Thanks Michael, appreciate the points
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question