Excel – Macro Problem – Inserting, Appending Data, Deleting

[Aussiexcel]

Gday All

I need help with creating a macro that can insert data from one worksheet to another in the same workbook. I will list all the details now.

Workbook name Test.xls
Worksheets name in order of tabs 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 columns 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 starting address). In B it contains information about the types of data (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 and when I find UD1,UD2,UD3,UD4,UD5 . I want to insert X number of rows before (X being the number of columns in the UD1,UD2,UD3,UD4,UD5 worksheets that I want to copy). Then Copy the UD1 into the rows. The UD worksheets can be a variable length.

Now that I have inserted the UD1-5 from the worksheet. I have to delete the row which contained the UD1-5 but before I can do that I need to exact a unique number in column D and paste that down column D with the X amount of rows I insert. Also before I delete the row containing UDT1-5 in B, I need to append its column A’s string to the A of the inserted rows.

Column A contains a name for both the rows. What I need to do is append 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 adapt 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 As 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 which used Find to find the UD1 and then insert but it wasn’t 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 would be able to adapt for the rest.

I should call this thread calling all MVP’s. It’s assuming how much they know.

Cheers,

Aussiexcel

 

[BrianB]

Re: Excel – Macro Problem – Inserting, A

In the absence of other replies ....

I am not making a specific reply because in my considered opinion you are on the wrong track with your method. You are already experiencing the utter confusion and complication that can occur.

Rather than breaking down data into separate sheets you should be consolidating it as far as possible into a single table. This then enables the more powerful features of Excel to be used to perform the analysis - such as formulas, pivot tables and filters.

Your message too is a sample of this. You do not seem really clear about what you are ultimately trying to achieve. Think in terms of INPUTS - PROCESSES - OUPUTS with OUPUTS being the main focus.

Keep it simple. You should be able to add new data to the bottom of your table and see the results immediately. One of my most recent efforts reduced a day's work for a senior empoyee to ten minutes.