Copy Sheet Names to a Column
 

Disregard 1st question, I found the solution in another post.

New question and pretty simple one (but not for me... Krikee). How do I
jump to or select a specific sheet in my workbook so my macro will always run
on that one sheet.

Sheets("Code Master").Activate
Sheets("Code Master").Select

Any help would be appreciated.

Thanks!

-Wesley


"Wester" wrote:

Hi,

Struggling with the command/macro to copy all sheet names in my workbook to
single column on a 'master' sheet.

Any help would be apprecieted.

-Wesley

Copy Sheet Names to a Column
 

Wester

Worksheets("Sheet1").Activate

But bear in mind in most cases to work on a sheet you don't need to select
it, I always assign a variable to it first to stop having to use the full
qualifier each time. This is also safer to ensure your code works in the
correct range. (By default it also ensures you get an intellisense list of
objects and methods, which is handy

So

Sub test()
Dim wks As Worksheet
Set wks = Worksheets("Sheet3")
With wks
.Range("A1").Value = "Nick did this"
.Columns(1).AutoFit
.Name = "Nicks Sheet"
.PageSetup.LeftFooter = "My Left Foot"
End With
End Sub

Does all you want without selecting or activating. (Any selection or
activation takes time and needs you to stop the screen flashing with
application.screenupdating=false)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Wester" wrote in message
...
Disregard 1st question, I found the solution in another post.

New question and pretty simple one (but not for me... Krikee). How do I
jump to or select a specific sheet in my workbook so my macro will always
run
on that one sheet.

Sheets("Code Master").Activate
Sheets("Code Master").Select

Any help would be appreciated.

Thanks!

-Wesley


"Wester" wrote:

Hi,

Struggling with the command/macro to copy all sheet names in my workbook
to
single column on a 'master' sheet.

Any help would be apprecieted.

-Wesley

Copy Sheet Names to a Column
 

Wester

This works

Sub List_Sheets()
Dim wks As Worksheet
Dim rng As Range
Dim s As Integer, c As Integer
Dim r As Long, i As Integer
Set wks = Worksheets("Code")
Set rng = wks.Range("A2")
s = Sheets.Count
r = rng.Row: c = rng.Column
With wks
For i = 1 To s
.Cells(r, c) = Sheets(i).Name
.Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next i
End With
End Sub

Some notes to follow

1) Always dimension you variables, (You can force this in ToolsOptions in
the VBE)
2) I moved the assignment of your row and column variables, else you just
reset it on each iteration of the loop to the same number
3) You should qualify the return of your loop Next i
4) When you are using an object variable, e.g wks, within a With...End With
structure, you prefix the further objects or properties, with a period
..Cells(......

If you get any further questions, post back

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Wester" wrote in message
...
Thanks Nick! Krikee I was just slightly off.

OK I am trying to understand what you recommended. I get what you are
saying but I am missing something? It is not putting the data to my
"Code"
sheet.

Sub List_Sheets()
Worksheets("Code").Activate <= Works "U Da Man!"
'Dim wks As Worksheet <= Doesn't work
'Set wks = Worksheets("Code") <= Doesn't work
'With wks <= Doesn't work
Range("A2").Select
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
'End With
End Sub


-Wesley





"Nick Hodge" wrote:

Wester

Worksheets("Sheet1").Activate

But bear in mind in most cases to work on a sheet you don't need to
select
it, I always assign a variable to it first to stop having to use the full
qualifier each time. This is also safer to ensure your code works in the
correct range. (By default it also ensures you get an intellisense list
of
objects and methods, which is handy

So

Sub test()
Dim wks As Worksheet
Set wks = Worksheets("Sheet3")
With wks
.Range("A1").Value = "Nick did this"
.Columns(1).AutoFit
.Name = "Nicks Sheet"
.PageSetup.LeftFooter = "My Left Foot"
End With
End Sub

Does all you want without selecting or activating. (Any selection or
activation takes time and needs you to stop the screen flashing with
application.screenupdating=false)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Wester" wrote in message
...
Disregard 1st question, I found the solution in another post.

New question and pretty simple one (but not for me... Krikee). How do
I
jump to or select a specific sheet in my workbook so my macro will
always
run
on that one sheet.

Sheets("Code Master").Activate
Sheets("Code Master").Select

Any help would be appreciated.

Thanks!

-Wesley


"Wester" wrote:

Hi,

Struggling with the command/macro to copy all sheet names in my
workbook
to
single column on a 'master' sheet.

Any help would be apprecieted.

-Wesley

Copy Sheet Names to a Column
 

Wester

There are a ton of ways to do this.

I would take the row number from the CurrentCell range, so

Dim RowNumber as Long
RowNumber=CurrentCell.Row

and then use this to get the cells B:G of that row

Sub test()
Dim SourceRow As Range
Dim CurrentCell As Range
Dim RowNumber As Long
Set CurrentCell = ActiveCell
RowNumber = CurrentCell.Row
Set SourceRow = Range(Cells(RowNumber, 2), Cells(RowNumber, 7))
SourceRow.Copy
'do what you need with the copied data
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Wester" wrote in message
...
Hi Nick,

I was wondering if you can help me with one line in the this macro I am
borrowing. It works great but I am wanting to change it so it copies only
cells B through G of the row instead of the entire row. I can't seem to
get
the right parameter down and probably going in a wrong direction.

'Set SourceRow = CurrentCell.EntireRow <==== This Works
Set SourceRow = CurrentCell.Range(Cells(Selection.Row, 2),
Cells(Selection.Row, 3)) <==== Not working

Thanks!

-Wesley


Sub CopyRowsToSheets()

'copy rows to worksheets based on value in column A

'assume the worksheet name to paste to is the value in Col A

Dim CurrentCell As Range
Dim SourceRow As Range
Dim Targetsht As Worksheet
Dim TargetRow As Long
Dim CurrentCellValue As String

'start with cell A2 on "Master" sheet
Set CurrentCell = Worksheets("Master").Cells(2, 1) 'row ... column ...

Do While Not IsEmpty(CurrentCell)
CurrentCellValue = CurrentCell.Value
'Set SourceRow = CurrentCell.EntireRow <==== This Works
Set SourceRow = CurrentCell.Range(Cells(Selection.Row, 2),
Cells(Selection.Row, 3)) <===== Not working

'Check if worksheet exists
On Error Resume Next

Testwksht = Worksheets(CurrentCellValue).Name

If Err.Number = 0 Then

'MsgBox CurrentCellValue & " worksheet Exists"
Else

MsgBox "Adding a new worksheet for " & CurrentCellValue

Worksheets.Add.Name = CurrentCellValue

End If

On Error GoTo 0 'reset on error to trap errors again

Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)

' Find next blank row in Targetsht - check using Column A
TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1
SourceRow.Copy Destination:=Targetsht.Cells(TargetRow, 1)
'SourceRow.Delete

'do the next cell
Set CurrentCell = CurrentCell.Offset(1, 0)

Loop

End Sub