Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula, Macro, or VBA code to find and insert | Excel Worksheet Functions | |||
Problem when insert sheets by macro | Excel Discussion (Misc queries) | |||
Find last row macro problem | Excel Discussion (Misc queries) | |||
Search and Find Macro | Excel Discussion (Misc queries) | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |