User Form To Copy Rows of Data And Transpose into Grid

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi, can anyone help with User Form VBA?

I have Data on Sheets 7, 8 & 9 that I want to copy into a grid on Sheet2 when the User Form meets a certain criteria

I have built the User Form with the toolbox functions as follows

CommandButton1

ComboBox1 with
Sheet7
Sheet8
Sheet9

ListBox1 with
0
1
2
3
4
5

CheckBox1 Labelled A
CheckBox2 Labelled B
CheckBox3 Labelled x5 (LabelBox1 'Rng' as a Header)
CheckBox4 Labelled x10

On Sheet2 the Grid Range is ("AM17:AS23") with Headers A,B,C,D,E,F,G ("AM16:AS16")
and has Data Manually added to ("AM17:AS17") As and when Required

On Sheet7 the Data Range to be copied from is ("BC17:BL28") and the same for Sheets 8 & 9
with Headers "Ref" "Rng" "Seq" A,B,C,D,E,F,G

eg

AJAKALAMANAOAPAQARASATAUAVBABBBCBDBEBFBGBHBIBJBKBLBMBNBO
15Sheet215Sheet7
16ABCDEFG16RefRngSeqABCDEFG
1700122451705A0000111
1818010B0000222
191915A1111222
2020110B1111333
212125A2222333
2222210B2222444
232335A3333555
2424310B3333666
252545A4444777
262645B4444888
272755A5555999
2828510A5555101010
2929
3030

<tbody>
</tbody>
<strike></strike>

With the User Form I want to be able to Select different Data from Sheets 7, 8 & 9

eg if I Select 'Sheet7' from ComboBox1, '0' from ListBox1, CheckBox1 = 'True' CheckBox3 = 'True' and click the Command Button, Data from Sheet7 ("BF17:BL17") should be copied to the Grid on Sheet2 and Transposed to ("AM17:AM23")
Then if I Selected 'Sheet7' again from ComboBox1, '0' from ListBox1, CheckBox2 = 'True' CheckBox4 = 'True' and click
the Command Button, Data from Sheet7 ("BF18:BL18") should be copied to the Grid on Sheet2 and Transposed to the next available column ("AN17:AN23") as the first column in the Grid ("AM17:AM23") would already have Data and so on...
Then continue filling the Grid as required
ie

'Sheet7' from ComboBox1, '1' from ListBox1, CheckBox1 = 'True' CheckBox3 = 'True'
and click the Command Button
'Sheet7' from ComboBox1, '2' from ListBox1, CheckBox1 = 'True' CheckBox3 = 'True'
and click the Command Button
<strike></strike><strike>
</strike>
'Sheet7' from ComboBox1, '2' from ListBox1, CheckBox2 = 'True' CheckBox4 = 'True'
and click the Command Button
'Sheet7' from ComboBox1, '4' from ListBox1, CheckBox2 = 'True' CheckBox3 = 'True'
and click the Command Button
<strike>
</strike>
<strike></strike><strike></strike><strike></strike>
'Sheet7' from ComboBox1, '5' from ListBox1, CheckBox1 = 'True' CheckBox4 = 'True'
and click the Command Button

The Grid would have the following Data

AJAKALAMANAOAPAQARASATAUAV
15Sheet2
16ABCDEFG
170012245
180012245
190012245
200012245
2112234810
2212234810
2312234810
24
25
26
27
28
29
30

<tbody>
</tbody>
<strike>
</strike>
<strike></strike><strike></strike><strike style="background-color: transparent; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>
Any help would be appreciated

Regards

pwill
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Just thought I would mention as i know it is a very compicated procedure for someone to tackle. The Form is UserForm1, the combobox is a drop down list with the 3 sheets as the list to select from and is added from sheet2 ("A2:A4") and the ref list for the listbox is added from sheet2("B2:B10")

Regards

pwill
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top