Removing specific columns!

[yorkie287]

hi everybody,

i have a raw data sheet that consists of 19 columns, when i recieve this data sheet i have to send it on to 3 others who need information from different columns, for example person 1 needs only columns, 1,2,3,15,17,
person 2 need 1,2,3,4,6,9,10,11,12,14,18,19, and person 3 needs 4,5,6,7,8,9,10, these are not the exact columns that they actually need but you get my point! these people need the same data every time, would it be possible for me to create a macro to create a new worksheet for each person giving only the information they need!

all help will be appreciated

thanks

 

[MickG]

Try something like this:-

[COLOR="Navy"]Sub[/COLOR] MG11Jul19
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] num         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Person(1 To 5)
'[COLOR="Green"][B]Alter array Columns to suit Person!!![/B][/COLOR]
Person(1) = Array(1, 2, 4, 5, 6)
Person(2) = Array(5, 7, 8, 9)
Person(3) = Array(8, 9, 10, 11, 12)
Person(4) = Array(15, 16, 18, 19)
Person(5) = Array(1, 3, 9, 15)
'[COLOR="Green"][B]add persons here!!![/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp).Resize(, 19))
  num = Application.InputBox(prompt:="Enter Person Number ", Title:="Persons Sheet", Type:=1)
    [COLOR="Navy"]If[/COLOR] num = "False" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]If[/COLOR] num <= UBound(Person) [COLOR="Navy"]Then[/COLOR]
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Person" & num
        ActiveSheet.Range("A1").Resize(Rng.Rows.Count, UBound(Person(num)) + 1) = _
           Application.Index(Rng, Evaluate("row(" & Rng.Address & ")"), Person(num))
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[yorkie287]

thanks very much for your help mick i am away from work for a few days, but will try this as soon as i get back in and let you know how i get on