VBA: selecting data to be outputted to new sheet

[jlkamikaze]

hi guys so im basically still a n00b at VBA

i was wondering if anyone could point me in the right direction to creating such a program...

choosing a specific row and then choosing which pieces of data [which columns in that row] will be outputted to a new Sheet

for example, in row 7, there are 20 columns, i wanted to choose columns A, D, G, I but can actually choose or click on or select any number of columns i want to then be outputted to a new sheet with only the selected columns

any advice help or suggsetions would be greatly appreciated!
thank you for your time and consideration!

[Kenneth Hobs]

Welcome to the forum!

In a Module, paste:
[vba]Sub AddSelectionToNewSheet()
Dim r As Range, a As Range, ra As Range
On Error GoTo EndNow
Set r = Application.InputBox("Hold CTRL key down to select cells.", "Select Range", Type:=8)
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=1
For Each a In r.Areas
Set ra = Cells(1, Columns.Count).End(xlToLeft)
If IsEmpty(ra) Then
a.Copy ra
Else
a.Copy ra.Offset(0, 1)
End If
Next a
EndNow:
End Sub[/vba]

[jlkamikaze]

it works perfectly! thank you! ive just finished decoding it and i understand all of it except that each time i run the VBA program, it outputs it into a new sheet each time [for example, it first outputs to sheet 4, then sheet 5, then sheet 6, etc]

is there any portion of the code that i can change to set into to one specific sheet? if not can you please explain why? thank you!

[Kenneth Hobs]

Sure, I just did what you asked:

..will be outputted to a new Sheet

What sheet did you want it to go to? Where did you want it to go, the next blank row, etc.?

It can be shortened to not prompt for a range and just copy the Selection.

[jlkamikaze]

i wanted to see if i could pick a row and then from like a checklist of sorts or something more organized pick which columns would be outputted to the next blank sheet over with the columns side by side and with headings already above

[jlkamikaze]

what im basically trying to combine your program with is a Checklist [or some other organized feature] that allows the user to check off or select which columns of the selected rows should be outputted to the new worksheet

[Kenneth Hobs]

Search google for "VBA Userform Tutorial ListBox" to see how if you don't know. An example workbook is the best way to help you.

[jlkamikaze]

got it! thank you!