Want to add additional columns to attached macro please

007juk

Board Regular
Joined
May 16, 2007
Messages
95
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.

Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
Add the following after the line s
Code:
Set OSh = Sheets("Need Training")
OSh.Cells(1, 1).Value = "Training overdue for Manual Handling"

Code:
OSh.Cells(1, 2).Value = "heading2"
OSh.Cells(1, 3).Value = "heading3"
OSh.Cells(1, 4).Value = "heading4"
OSh.Cells(1, 5).Value = "heading5"
 
Joined
Jul 30, 2006
Messages
3,656
007juk,

I answered you duplicate post earlier today.

Here you go:

Code:
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

Board Regular
Joined
May 16, 2007
Messages
95
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,
 
Joined
Jul 30, 2006
Messages
3,656

ADVERTISEMENT

007juk,

This code is un-tested.

Code:
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

Board Regular
Joined
May 16, 2007
Messages
95
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
May 16, 2007
Messages
95
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 16, 2007
Messages
95
hope this helps -
EDC trainingv2.xls
ABCD
1ManualHandlingH&SAwareness
2emp3
3emp7
4emp9
5emp10
6emp15
7emp16
8emp21
9emp22
10emp23
11emp35
12emp36
13emp39
14emp42
15emp43
16emp45
17emp46
18emp47
19emp54
20emp56
21emp57
22emp58
23emp60
24emp65
25emp71
26emp76
27emp77
28emp81
29emp85
30emp89
Need Training
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,151
Members
417,010
Latest member
jnuss03

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
Top