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()
- Dim ShtNum As Integer
- Dim Index As Integer
- Dim LR As Integer
- ShtNum = ActiveWorkbook.Worksheets.Count
- For Index = 1 To ShtNum
- Selection.NumberFormat = "General"
- 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
- 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.