Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Problem Inserting, Appending Data, Deleting
Gday All
I need help with creating a macro that can insert data from on worksheet to another in the same workbook. I will list all the detail now. Workbook name Test.xls Worksheets name in order of tab D1,D2,D3,D4,D5,Master,D,UD,UD1,UD2,UD3,UD4,UD5 D1 to D5 are in the same format. UD1 to UD5 are in the same format Firstly, what my workbook does so far it copies and appends column from the worksheets D1-D5 to the Master worksheet. Now what I am trying to do. Now in master I have a column B which starts at 5 (B5 startin address). In B it contains information about the types of dat (UD1,UD2,UD3,UD4,UD5,BOOL,BYTE, and so on). What I want to do is search for UD1,UD2,UD3,UD4,UD5 in column B an when I find UD1,UD2,UD3,UD4,UD5 . I want to insert X number of row before (X being the number of columns in the UD1,UD2,UD3,UD4,UD worksheets that I want to copy). Then Copy the UD1 into the rows. Th UD worksheets can be a variable length. Now that I have inserted the UD1-5 from the worksheet. I have to delet the row which contained the UD1-5 but before I can do that I need t exact a unique number in column D and paste that down column D with th X amount of rows I insert. Also before I delete the row containin UDT1-5 in B, I need to append its column As string to the A of th inserted rows. Column A contains a name for both the rows. What I need to do is appen the name of the row that is being deleted to name of the inserted rows. E.g. Column A Name INSERT1 INSERT2 INSERT3 INSERT4 DELETED The New name will be DELETED_INSERT1 DELETED_INSERT2 DELETED_INSERT3 DELETED_INSERT4 With the helped I received before I am using this code. I will adap this code to do the copy of the cells. Sub InsertData() Dim f As Worksheet, t As Worksheet, i As Long, k As Long, j A Integer Application.ScreenUpdating = False Set t = Sheets("Master") k = 2 For j = 1 To 2 Set f = Sheets("UD1") i = f.Cells(65536, 1).End(xlUp).Row If i = 11 Then f.Range("A11:B" & i).Copy t.Range("A" & k) f.Range("C11:D" & i).Copy t.Range("H" & k) f.Range("E11:E" & i).Copy t.Range("G" & k) t.Range("D" & k & ":D" & k + i - 11).Value = f.Range("C1") t.Range("E" & k & ":E" & k + i - 11).Value = f.Range("B2") k = k + i - 10 End If Next j Application.ScreenUpdating = True End Sub So can anyone help me with this? I was trying to record a macro whic used Find to find the UD1 and then insert but it wasnt much luck. What I need most of all is help with the searching, inserting appending cell names, deleting? I have hit a wall now. If someone could just help me with UD1, I woul be able to adapt for the rest. I should call this thread calling all MVPs. Its assuming how muc they know. Cheers, Aussiexce -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting and Deleting Rows in Excel | Excel Worksheet Functions | |||
Problem inserting Excel data into Word | Excel Discussion (Misc queries) | |||
Appending data from one spreadsheet to another using a macro | Excel Discussion (Misc queries) | |||
Macro Help - Copying and appending data | Excel Worksheet Functions | |||
Excel Macro Problem Appending Data | Excel Programming |