A
Aussiexcel
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 A’s 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
" & i).Copy t.Range("H" & k)
f.Range("E11:E" & i).Copy t.Range("G" & k)
t.Range("D" & k & "" & 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 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 woul
be able to adapt for the rest.
I should call this thread calling all MVP’s. It’s assuming how muc
they know.
Cheers,
Aussiexce
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 A’s 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
" & i).Copy t.Range("H" & k)f.Range("E11:E" & i).Copy t.Range("G" & k)
t.Range("D" & k & "
" & 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 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 woul
be able to adapt for the rest.
I should call this thread calling all MVP’s. It’s assuming how muc
they know.
Cheers,
Aussiexce