Solved

Excel vba find and copy cells

Posted on 2014-01-14
16
700 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
  • 9
  • 7
16 Comments
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
No problem Michael.  Try the easiest route first. I'll take a more in depth look at your code
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mike637
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Then it's something I can't see. Can you attach your workbook and I'll take a look?
0
 

Author Comment

by:mike637
Comment Utility
As instructed....

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

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Thanks Michael, appreciate the points
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now