Excel – Macro Problem – Find/Search and Insert
 

Hey All

Ok I am having trouble with a simple search down column B find (UDT1
UDT2, UDT3, UDT4, or UDT5) then insert X rows and paste.

I can do the insert and paste but I am having big problems with fin
UDT1, UDT2, UDT3, UDT4, or UDT5 in column B in my Master worksheet the
insert and paste.

What I want to do is search column B for UDT1, UDT2, UDT3, UDT4, o
UDT5 and inserts the certain contents of worksheet UDT1, or UDT2, o
UDT3, or UDT4, or UDT5?

The code below shows how I insert the contents from the worksheet. I
inserts the certain contents of UDT5 into the master worksheet.

Sub Insert()
Dim f As Worksheet, t As Worksheet, x As Long, i As Long, k A
Long, j As Integer, p As Integer, LastRow As Long, NxtRow As Long
Application.ScreenUpdating = False
Set t = Sheets("Master")

Set f = Sheets("UDT5")
p = 0

x = f.Cells(65536, 1).End(xlUp).Row
t.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(x - 11
0)).Select
Selection.EntireRow.Insert

'Determine the last row assuming other columns dont surpass th
last row in col A
LastRow = f.Range("A65536").End(xlUp).Row

'Determine the next blank row in the Master sheet
NxtRow = t.Range("A65536").End(xlUp).Offset(1, 0).Row

NxtRow = ActiveCell.Offset(0, p - 1).Row

'Copy values
f.Range("A11:A" & LastRow).Copy t.Cells(NxtRow, 1) 'copy to col A
f.Range("B11:B" & LastRow).Copy t.Cells(NxtRow, 2) 'copy to col B
f.Range("C11:C" & LastRow).Copy t.Cells(NxtRow, 8) 'copy to col H
f.Range("D11:D" & LastRow).Copy t.Cells(NxtRow, 9) 'copy to col I
f.Range("E11:E" & LastRow).Copy t.Cells(NxtRow, 7) 'copy to col G

Application.ScreenUpdating = True

End Sub

So can anyone help me?

I have tired doing this but I am off.

Select Case t.Range(ActiveCell.Select).Value or
Select Case t.Range(B:B).Value
Case "UDT1"
Set f = Sheets("UDT1")
Case "UDT2"
Set f = Sheets("UDT2")
Case "UDT3"
Set f = Sheets("UDT3")
Case "UDT4"
Set f = Sheets("UDT4")
Case "UDT5"
Set f = Sheets("UDT5")
End Select

Select Case t.Range("B7").Value

Using the line above works for b7 as it should but if someone can hel
me with code that can search down column b and find UDT1, or UDT2, o
UDT3, or UDT4, or UDT5 so then I can use my insert and paste code wil
be a great help.

Cheers,

Aussiexce

--
Message posted from http://www.ExcelForum.com