Help Please! (This one is a bit tricky)

Dean Goodmen · 02-24-2005, 03:06 PM

I need to look up a cell value from another workbook. Here is what I
have :

Work sheet One at C:\folder1\workbook1
Work sheet Two at C:\folder2\workbook2 (name varies)

In work book one, I need to assign Cell B2 to a cell value found
in Wookbook 2. Here is the catch... The name of work book2 is
depentant on the date in Workbook1 and as well as the sheet name, and
I also need to FIND the cell.

For instance in workbook1 : Cell A1 = 03/01/05
To find the value of B2 I need to find it below
So the name of work book 2 would be backup05 (Backup/YEAR/)
Looking at sheet name 03-05 (MM-YY)
Find the word "Backupvalue" in Colloum1 The cell just to the right of
that word would be the value to send back to workbook1, cell B2

I hope this all makes sence LOL

Thanks,
Ed

Chip · 02-24-2005, 04:06 PM

Well I would need to know the path for backup05, or the user would have
to find the file themselves.

Dean Goodmen · 02-25-2005, 06:06 AM

On 24 Feb 2005 11:30:11 -0800, "Chip" <[email protected]> wrote:

>Well I would need to know the path for backup05, or the user would have
>to find the file themselves.

That would be WorkBook2

Work sheet One at C:\folder1\workbook1
Work sheet Two at C:\folder2\workbook2 (name varies)

**Leith Ross** · 02-25-2005, 09:00 AM

Hello Ed,

This macro will build Workbook 2's name from Workbook 1 and will copy Workbook 2's backup value into Workbook 1's cell B2.
_________________________________________________________________

Public Sub MyMacro()

Dim Wks1 As Worksheet
Dim Wks2 As Worksheet

Set Wks1 = Application.ThisWorkbook.Worksheets("Sheet1")

With Wks1.Range("A1")
MM = Left(.Value, 2)
YY = Right(.Value, 2)
End With

'Build Workbook 2's Name
Wkb2 = "C:\Folder2\" & "Backup" & YY
Wks2Name = MM & "-" & YY

'Open Workbook 2
Workbooks.Open Wkb2

Set Wks2 = Workbooks(Wkb2).Worksheets(Wks2Name)

'Get the Row Count in Column A
RC = Excel.WorksheetFunction.CountA(Wks2.Range("A:A"))

'Get the Row Index where "Backupvalue" was Found
RI = Excel.WorksheetFunction.Match("Backupvalue",Wks2.Range( "A1:A" & RC), 0)

'Copy Worksheet 2 cell data to Worksheet 1 "B2"
Wks1.Range("B2").Value = Wks2.Cells(RI, 2).Value

End Sub

_________________________________________________________________

Hope this is what you were after,
Leith Ross

Help Please! (This one is a bit tricky)

LinkBack

Thread Tools

Rate This Thread

Display

Help Please! (This one is a bit tricky)

Re: Help Please! (This one is a bit tricky)

Re: Help Please! (This one is a bit tricky)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions