Want to add additional columns to attached macro please

[007juk]

Hi,

Can someone help please. I don't know how to add addtional columns to the attached macro. At the moment when I run macro it provides me with one column of data with a heading. Want to add an extra four columns. The titles of columns can be col2, col3, col4 and col6.

Many thanks.

Sub ListTraining()
Dim LastRow As Long, i As Long, StartRow As Long, NameCol As Integer
Dim ISh As Worksheet, OSh As Worksheet, j As Long
StartRow = 2
NameCol = 1
Set ISh = ActiveSheet
LastRow = ISh.Cells(Rows.Count, NameCol).End(xlUp).Row
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Need Training").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Need Training"
Set OSh = Sheets("Need Training")
OSh.Cells(1, 1).Value = "Training overdue for Manual Handling"
j = 1
For i = StartRow To LastRow
If IsEmpty(ISh.Cells(i, NameCol + 1).Value) Then
j = j + 1
OSh.Cells(j, 1).Value = ISh.Cells(i, NameCol).Value
End If
Next i
OSh.Columns(1).EntireColumn.AutoFit
End Sub

 

[zzjasonzz]

Add the following after the line s

Set OSh = Sheets("Need Training")
OSh.Cells(1, 1).Value = "Training overdue for Manual Handling"

OSh.Cells(1, 2).Value = "heading2"
OSh.Cells(1, 3).Value = "heading3"
OSh.Cells(1, 4).Value = "heading4"
OSh.Cells(1, 5).Value = "heading5"

 

[stanleydgromjr]

007juk,

I answered you duplicate post earlier today.

Here you go:

Option Explicit 
Sub ListTraining() 
    Dim LastRow As Long, i As Long, StartRow As Long, NameCol As Integer 
    Dim ISh As Worksheet, OSh As Worksheet, j As Long 
    StartRow = 2 
    NameCol = 1 
    Set ISh = ActiveSheet 
    LastRow = ISh.Cells(Rows.Count, NameCol).End(xlUp).Row 
    Application.DisplayAlerts = False 
    On Error Resume Next 
    Sheets("Need Training").Delete 
    On Error GoTo 0 
    Application.DisplayAlerts = True 
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Need Training" 
    Set OSh = Sheets("Need Training") 
    OSh.Cells(1, 1).Value = "Training overdue for Manual Handling" 
    'Want to add an extra four columns. The titles of columns can be col2, col3, col4 and col6. 
    OSh.Cells(1, 2).Value = "col2" 
    OSh.Cells(1, 3).Value = "col3" 
    OSh.Cells(1, 4).Value = "col4" 
    OSh.Cells(1, 5).Value = "col6" 
    j = 1 
    For i = StartRow To LastRow 
        If IsEmpty(ISh.Cells(i, NameCol + 1).Value) Then 
            j = j + 1 
            OSh.Cells(j, 1).Value = ISh.Cells(i, NameCol).Value 
        End If 
    Next i 
    OSh.Columns(1).EntireColumn.AutoFit 
End Sub

Have a great day,
Stan

 

[007juk]

Stanley, thanks for your help, apologies to yourself and board for duplivation.

I forgot to mention. Under these new headings I want to populate with data from another sheet within the workbook. At the moment it is programmed to look up column 2 (manual handling). Would also like the prg to look up columns C, E, F and L.

Regards,

 

[stanleydgromjr]

007juk,

This code is un-tested.

Option Explicit
Sub ListTraining()
    Dim LastRow As Long, i As Long, StartRow As Long, NameCol As Integer
    Dim ISh As Worksheet, OSh As Worksheet, j As Long
    StartRow = 2
    NameCol = 1
    Set ISh = ActiveSheet
    LastRow = ISh.Cells(Rows.Count, NameCol).End(xlUp).Row
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Need Training").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Need Training"
    Set OSh = Sheets("Need Training")
    OSh.Cells(1, 1).Value = "Training overdue for Manual Handling"
    'Want to add an extra four columns. The titles of columns can be col2, col3, col4 and col6.
    OSh.Cells(1, 2).Value = "col2"
    OSh.Cells(1, 3).Value = "col3"
    OSh.Cells(1, 4).Value = "col4"
    OSh.Cells(1, 5).Value = "col6"
    j = 1
    For i = StartRow To LastRow
        If IsEmpty(ISh.Cells(i, NameCol + 1).Value) Then    'column B
            j = j + 1
            OSh.Cells(j, 1).Value = ISh.Cells(i, NameCol).Value
            'I forgot to mention. Under these new headings I want to _
                populate with data from another sheet within the workbook. _
                At the moment it is programmed to look up column 2 (manual handling). _
                Would also like the prg to look up columns C, E, F and L.
            'If I understand your requirements
            OSh.Cells(j, 2).Value = ISh.Cells(i, NameCol + 2).Value     'from column C
            OSh.Cells(j, 3).Value = ISh.Cells(i, NameCol + 4).Value     'from column E
            OSh.Cells(j, 4).Value = ISh.Cells(i, NameCol + 5).Value     'from column F
            OSh.Cells(j, 5).Value = ISh.Cells(i, NameCol + 11).Value    'from column L
        End If
    Next i
    'OSh.Columns(1).EntireColumn.AutoFit
    OSh.Columns("A:E").Columns.AutoFit
End Sub

Have a great day,
Stan

 

[007juk]

Stan,

Thanks for the code, unfortunately it has populated the columns with dates rather than employee names. The first code I published generated a list of employees who require training. Also for some reason column 'e' has no data listed.

The data sheet has employees names on the first column and then each column has either dates (training completed) or (blanks) training required) - what I'm interested in.

Many thanks,

 

[Norie]

007juk

Can you post some sample data, with expected results?

Also an explanation of the original code and what you mean by 'add additional columns' would help.

 

[007juk]

Norie,

The original macro I posted generated a list of names with a header. The data was extracted from two columns the first column on the sheet contains all the names and the next five columns contain dates for training completed. The macro looked yp the 1st column and then looked for Blanks in column 2. Once it found the blanks it genearted a list of staff who still require that particular type of training. Is a macro the best way of doing it as I have other results that I would like to obtain too.

Many thanks,

Is it possible to post a sample sheet or would it have to be sent by email, would like others to see what I'm working on mau help too.

 

[Norie]

To post a sample see the link to Colo's HTML Maker in my sig.

As to using code being the best way to do it, well that's a moot point.

It might actually be the only way to do it, but then again who knows what some formula guru could come up with.

By the way is this what you want/have?

Columns with headers for each type of training.

The names in column A.

Below the headers is the data training was completed.

Now you want a list of all the names who haven't completed a type of training and what that training was.

PS This would be far easier to deal with if you restructured your data.

 

[007juk]

hope this helps -

EDC trainingv2.xls
	A	B	C	D
1	ManualHandling	H&SAwareness
2	emp3
3	emp7
4	emp9
5	emp10
6	emp15
7	emp16
8	emp21
9	emp22
10	emp23
11	emp35
12	emp36
13	emp39
14	emp42
15	emp43
16	emp45
17	emp46
18	emp47
19	emp54
20	emp56
21	emp57
22	emp58
23	emp60
24	emp65
25	emp71
26	emp76
27	emp77
28	emp81
29	emp85
30	emp89
Need Training