creating tabs from list and populating

[kemperohlmeyer]

Hi,

I am trying to take data from one spreadsheet and do a couple of things. First, i need to create a tab for each column and name the tab after the name in row 1. (this code i already have, it is below)

Second, and here is where i need help, i want to put the tab name and corresponding data (all in the same column on the first sheet) onto each tab. Essentially i am just divying up each column on my master sheet into its own tab so that i can do further data analysis.

Please help.

KO

Sub GenWStabnames2()
'Kemper Ohlmeyer based on code from David McRitchie
Dim cell As Range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Worksheets("Manager list").Range("fund.names")
Sheets.Add After:=Sheets(Sheets.Count)
If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Text
ActiveSheet.Name = newName
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.Number & " " & Err.Description, vbOKCancel, _
"Failed to Rename Worksheet, it will be deleted:")
'--eliminate already created sheet that failed to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediate cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
End Sub

 

[Andrew Poulsom]

This:

cell.EntireColumn.Copy ActiveSheet.Range("A1")

should copy the relevant column to the new sheet.

 

[kemperohlmeyer]

will it now which column to reference for each tab?

 

[Andrew Poulsom]

I assume that:

For Each cell In Worksheets("Manager list").Range("fund.names")

loops through the columns. So:

cell.EntireColumn

will be the correct column.

 

[kemperohlmeyer]

Thanks. Last question: how do i offset the data when it is pasted so that it is in cell A3?

Thanks again.

 

[Andrew Poulsom]

Like this?

cell.EntireColumn.Copy ActiveSheet.Range("A3")

 

[kemperohlmeyer]

If i change it to A3 the macro doesnt run. It just sits after creating the first tab (blank).

 

[kemperohlmeyer]

Actually now it just sits there no matter what. I havent changed any code either.

Here is what i currently have...
Dim cell As Range
Dim newName As String, xx As String

Sub GenWStabnames2()
'Kemper Ohlmeyer based on previous code by David McRitchie
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Worksheets("Manager list").Range("fund.names")
Sheets.Add After:=Sheets(Sheets.Count)
If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Text
ActiveSheet.Name = newName
cell.EntireColumn.Copy ActiveSheet.Range("A1")
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.Number & " " & Err.Description, vbOKCancel, _
"Failed to Rename Worksheet, it will be deleted:")
'--eliminate already created sheet that failed to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediate cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
End Sub

 

[Andrew Poulsom]

Please check that you have a range named fund.names on a worksheet named Manager list. If you don't the code will end on this line:

If Err.Description <> "" Then Exit Sub

 

[Wookie]

I have used the above code and it works well but have a small tweak that i would like to make. i have created a range which references 10 cells A1 to A10 however depending on what region i send too they may only populate two cells or maybe all ten. How can i make the macro recognise the amount entries that have been made and therefore only try to create that many sheets.