i need a vba code that can get the data from a different sheet using a data from a column. if it matched the data then we can get the desired data and copy paste it to the targetted range or column..
heres a code i used.. its unfinished yet.. and its not dynamic.. if the user inserts a column / rows the program gets messed up.. so i need a program that uses range and tables so that even if the user inserts column.. the range is untouched...
Const constShtname = "Table1"
Const data = "Table2"
Const reqrng = "keyrng"
Sub test()
Dim wb As Workbook, ws As Worksheet
Dim shtrng As Range, reqkeyrng As Range
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Dim intRecCount As Integer
Set shtrng = ws.Range(constShtname)
Set reqkeyrng = ws.Range(reqrng)
intRecCount = shtrng.Rows.Count
Dim LOda As ListObject
Set LOda = ws.ListObjects(data)
' ==========================================================
' = Variables for Validating Columns
Dim intColCntToVal As Integer, rc As Integer
rc = ws.Cells(Rows.Count, 1).End(xlUp).Row
cc = ws.Cells(Rows.Count, 1).End(xlUp).Column
Dim intCtr As Integer
Dim rngReference As Range
Dim rngReferee As Range
'===========================================================
Dim shtDSource As Worksheet
Dim rngDSourceTmp As Range
Dim rngDSource As Range
For Each cell In shtrng
intCtr = intCtr + 1
If SheetExists(cell.Value, wb) Then
Set shtDSource = wb.Worksheets(cell.Value)
intColCntToVal = shtDSource.Cells(Rows.Count, 1).End(xlUp).Row
Set rngReference = ws.Range(LOda.Range(1, 1), LOda.Range(1, rc))
Set rngDSourceTmp = shtDSource.UsedRange
' Validate Columns
Set rngReferee = shtDSource.Range(rngDSourceTmp(1, 1), rngDSourceTmp(1, intColCntToVal))
With ws
For Each rngReference In reqkeyrng
If rngReference <> "" Then
Set rngDSource = shtDSource.Columns(1).Find(rngReference, LookAt:=xlWhole)
If Not rngDSource Is Nothing Then
rngReference.Offset(, 3).Value = shtDSource.Cells(rngDSource.Row, 2).Value
Set rngDSource = Nothing
End If
End If
Next rngReference
End With
End If
Next
End Sub
Public Function SheetExists(shtName As String, Optional wb1 As Workbook) As Boolean
Dim sht As Worksheet
If wb1 Is Nothing Then Set wb1 = ThisWorkbook
On Error Resume Next
Set sht = wb1.Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing
End Function
Bookmarks