PDA

View Full Version : Gathering sheet names and displaying them in a specific sheet column



LePig
09-01-2016, 03:33 AM
Hi,

I want to display my results in a Cell/column in a specific sheet(test) on a button click.

I have this piece of code below. Which is giving the names of all visible sheets but i need them to go to a specific place in that sheet.



Dim ws As Worksheet, ws1 As Worksheet
Set ws1 = Sheets("Test")
i = 1


ws1.Columns(1).Insert
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws1.Cells(i, 1) = ws.Name
i = i + 1




End If
Next ws


Any help would be much appreciated.

Many thanks

mancubus
09-01-2016, 05:47 AM
specify 'specific place' specifically please.

p45cal
09-01-2016, 06:13 AM
They already go in a specific place (the top of an newly inserted column on a sheet called "Test").
It's quite hard to give you a solution if we don't know where that specific place is.
Let's say it's cell C33 and below on sheet "Test", then:
Dim ws As Worksheet, destn As Range
Set destn = Sheets("Test").Range("C33")
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
destn.Value = ws.Name
Set destn = destn.Offset(1)
End If
Next ws

Paul_Hossler
09-01-2016, 08:34 AM
I looks like they start to fill in A1 on "Test", then A2, A3, etc.




ws1.Cells(i, 1) = ws.Name


That's a 'specific place'

Change the line if you want them to fill in somewhere else