How Place certain data and merge cells dynamically for different cells data

[Dave Smith]

Hi Excel experts,
I am got stuck in my code so i need help in resolving this.

I have a userform and in that it contains combobox ( name of combobox is combobox1) where the user will select the machine type and the data inside the combobox is called from one of the excel sheet so that it remains dynamic means one just need to add the name of the machine and gets reflected in to the combobox.

When the user select the type of machine in the combobox then hits the button "done" then the number specific data needs to copy paste from one sheet to another and to with dynamic range selection (so that in future if any wants to add data it works easily no one need to edit in the code) for the copy paste data I have used this code the copy paste works good but where i got stuck is that i need to place "Output" Heading after leaving 2 rows

Just for eg : 1) if user selects "Machine A" then it will copy data from row 2 to row 7 after that leaving two rows (for this case it will be row 11) , then I need to merge columns from "A:C" and place heading of "Output" and after placing that heading in merge cells certain 2 to 4 rows, columns from "A:C" needs to be merged.

eg: 2) if user selects " Meter"25226"" then it will copy data from row 2 to row 4 after that leaving two rows (for this case it will be row 7) , then I need to merge columns from "A:C" and place heading of "Output" and after placing that heading in merge cells certain 2 to 4 rows, columns from "A:C" needs to be merged.

I hope I have cleared my problems which i am facing currently.
Pl. have a look in attached images.


Dim aLastRow, aLastRow_0 As Long
Dim aLastRow_a, aLastRow_b As Long
Dim t, y, u, i, o As Range
Dim qa, qb, qc, qd, qe As Range
Dim wsIn, wsOut As Worksheet
Dim ty, tu, ta As Variant

aLastRow = inputdata.Range("B" & Rows.Count).End(xlUp).Row
aLastRow_0 = inputdata.Range("L" & Rows.Count).End(xlUp).Row

aLastRow_a = inputdata.Range("C" & Rows.Count).End(xlUp).Row
aLastRow_b = inputdata.Range("M" & Rows.Count).End(xlUp).Row


Set wsIn = Application.Worksheets("inputdata")
Set wsOut = Application.Worksheets("Datasheet")
wsIn.Activate

'Datasheet.Range("A12 :A25").ColumnWidth = 25


Datasheet.Range("A10:C20").ClearContents
ty = 9 + alastrow_1 + 1
tu = 9 + aLastRow + 1
ta = 9 + aLastRow + 1


If GUI_2.ComboBox1.Value = productlist.Range("B3").Value Then
Set t = wsIn.Range(Cells(2, 2), Cells(aLastRow, 2))
t.Copy
wsOut.Range("A10").PasteSpecial xlPasteValues
wsOut.Range("A10:A20").ColumnWidth = 23
wsOut.Range("A10:A20").WrapText = True

Set qa = wsIn.Range(Cells(2, 3), Cells(aLastRow_a, 3))
qa.Copy
wsOut.Range("C10").PasteSpecial xlPasteValues
wsOut.Range("C10:C20").ColumnWidth = 15

wsOut.Range("G9:H21").merge
wsOut.Range(Cells(tu, 1), Cells(ta, 8)).merge

'wsOut.Range("A23:H23").merge
'wsOut.Range("A23").Value = "Required Output"
wsOut.Range(Cells(tu, 1), Cells(ta, 8)).Value = "Output"

 

[Dave Smith]

Hi Excel experts,
I am got stuck in my code so i need help in resolving this.

I have a userform and in that it contains combobox ( name of combobox is combobox1) where the user will select the machine type and the data inside the combobox is called from one of the excel sheet so that it remains dynamic means one just need to add the name of the machine and gets reflected in to the combobox.

When the user select the type of machine in the combobox then hits the button "done" then the number specific data needs to copy paste from one sheet to another and to with dynamic range selection (so that in future if any wants to add data it works easily no one need to edit in the code) for the copy paste data I have used this code the copy paste works good but where i got stuck is that i need to place "Output" Heading after leaving 2 rows

Just for eg : 1) if user selects "Machine A" then it will copy data from row 2 to row 7 after that leaving two rows (for this case it will be row 11) , then I need to merge columns from "A:C" and place heading of "Output" and after placing that heading in merge cells certain 2 to 4 rows, columns from "A:C" needs to be merged.

eg: 2) if user selects " Meter"25226"" then it will copy data from row 2 to row 4 after that leaving two rows (for this case it will be row 7) , then I need to merge columns from "A:C" and place heading of "Output" and after placing that heading in merge cells certain 2 to 4 rows, columns from "A:C" needs to be merged.

I hope I have cleared my problems which i am facing currently.
Pl. have a look in attached images.


Dim aLastRow, aLastRow_0 As Long
Dim aLastRow_a, aLastRow_b As Long
Dim t, y, u, i, o As Range
Dim qa, qb, qc, qd, qe As Range
Dim wsIn, wsOut As Worksheet
Dim ty, tu, ta As Variant

aLastRow = inputdata.Range("B" & Rows.Count).End(xlUp).Row
aLastRow_0 = inputdata.Range("L" & Rows.Count).End(xlUp).Row

aLastRow_a = inputdata.Range("C" & Rows.Count).End(xlUp).Row
aLastRow_b = inputdata.Range("M" & Rows.Count).End(xlUp).Row


Set wsIn = Application.Worksheets("inputdata")
Set wsOut = Application.Worksheets("Datasheet")
wsIn.Activate

'Datasheet.Range("A12 :A25").ColumnWidth = 25


Datasheet.Range("A10:C20").ClearContents
ty = 9 + alastrow_1 + 1
tu = 9 + aLastRow + 1
ta = 9 + aLastRow + 1


If GUI_2.ComboBox1.Value = productlist.Range("B3").Value Then
Set t = wsIn.Range(Cells(2, 2), Cells(aLastRow, 2))
t.Copy
wsOut.Range("A10").PasteSpecial xlPasteValues
wsOut.Range("A10:A20").ColumnWidth = 23
wsOut.Range("A10:A20").WrapText = True

Set qa = wsIn.Range(Cells(2, 3), Cells(aLastRow_a, 3))
qa.Copy
wsOut.Range("C10").PasteSpecial xlPasteValues
wsOut.Range("C10:C20").ColumnWidth = 15

wsOut.Range("G9:H21").merge
wsOut.Range(Cells(tu, 1), Cells(ta, 8)).merge

'wsOut.Range("A23:H23").merge
'wsOut.Range("A23").Value = "Required Output"
wsOut.Range(Cells(tu, 1), Cells(ta, 8)).Value = "Output"

Can any one pl. help me in solving this ?