Re: Locate Cell Using its Values and then Copying the Adjacent Cell to Other Workbook
Quote from _Janyce;751946Display More
I am relativley new to VBA and I am trying to write a code that does the following:
- Locates a specific cell based on its content e.g. (Item being forwarded to domestic processing workcentre)
- Selects a cell that is in the same row as this content but different column
- Copies this cell to clipboard
- Pastes this value into a different workbook.
Any help is greatly appreciated.
Sorry this piece of code that i'm trying to figure out is part of a larger one.
So currently my code takes a file of 'x' number of workbooks, extracts sheet 1 from every workbook and merges all of the sheets into a single new workbook. Next it takes column A and column B and concatenate the data in column M for all of the populated rows. Lastly it hides the columns between E and M. (lets call this workbook "Data Book")
Sub ExtractEMData() Dim wbDst As Workbook Dim wbSrc As Workbook Dim wsSrc As Worksheet Dim FilePath As String Dim strFilename As String Dim DataName As String Application.DisplayAlerts = False Application.EnableEvents = False Application.ScreenUpdating = False FilePath = Application.InputBox(Prompt:="Enter file path to folder with event manager data", Type:=2) Set wbDst = Workbooks.Add(xlWBATWorksheet) strFilename = Dir(FilePath & "\*.xls", vbNormal) If Len(strFilename) = 0 Then Exit Sub Do Until strFilename = "" Set wbSrc = Workbooks.Open(Filename:=FilePath & "\" & strFilename) Set wsSrc = wbSrc.Worksheets(1) Sheets("sheet1").Name = ActiveWorkbook.Name wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count) wbSrc.Close False strFilename = Dir() Loop wbDst.Worksheets(1).Delete Dim ShtNum As Integer Dim Index As Integer Dim LR As Integer ShtNum = ActiveWorkbook.Worksheets.Count For Index = 1 To ShtNum Sheets(Index).Select Columns("A:B").Select Selection.NumberFormat = "General" Range("M2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-12],RC[-11])" LR = Range("A" & Rows.Count).End(xlUp).Row Range("M2").AutoFill Destination:=Range("M2:M" & LR), Type:=xlFillDefault Columns("F:L").Select Selection.EntireColumn.Hidden = True ActiveWindow.ScrollColumn = 4 Next Index Application.DisplayAlerts = True Application.EnableEvents = True Application.ScreenUpdating = True End Sub
(This code created the execl file attached below)
I run this code in a blank workbook (lets call it "Analysis Book"), and it creates a new workbook ("Data Book") with 'x' number of sheets based on the number of workbooks in the desiganted file.
For every sheet in "Data Book", I want to find a cell in column E of based on it contents (Lets say im looking for the cell that contains "Item being forwarded to domestic processing workcentre"). Then using this cell I want to copy the corresponding data in column M (In this example it would copy the number "42177.7518"). It wil then take this number and paste it the column called "Item being forwarded to domestic workcentre" in the "Analysis Book".
*Properties of "Analysis Book"*
- Is open
- Has only one sheet
- Contains the VBA code needed to extract data from "Data Book"
- Has been saved to a specific name
*Properties of "Data Book"*
- Has NOT been saved to a specific name yet
- Has multiple sheets, based on the number of workbooks in the file the sheets were extracted from
- Is open
- Is active
I hope this helps decipher what im asking for help on, sorry for being vague. I was not sure how much information was needed.