Solved: Macro.

[Pinokkio]

Hi,

How do i change from:
Set ws = ActiveSheet
to
Set ws = Sheets("Klanten")

I want to select a sheet, not the active one.


Thanks in advance.

P.



Sub test()
Dim ws As Worksheet
Dim r As Long, laatste As Long
Dim blad As String, rij As Long
Dim wb As Excel.Workbook
Application.ScreenUpdating = False

'Set ws = ActiveSheet
Set ws = Sheets("Klanten")
'Set ws = wb.Worksheets(6)
laatste = Range("A65536").End(xlUp).Row
For r = 2 To laatste
blad = Cells(r, 1)

[lucas]

Hi pinokkio,
Set ws = Sheets("Klanten")


should work. Do you want to then do something with that sheet?

set blad to something on that sheet?

Can you say what you are trying to do?

[Pinokkio]

This macro make sheets from sheet1 or the active sheet in kolom A.

I'm testing now from the sheet "Klanten".



Sub test()
Dim ws As Worksheet
Dim r As Long, laatste As Long
Dim blad As String, rij As Long
Dim wb As Excel.Workbook
Application.ScreenUpdating = False

Set ws = ActiveSheet

laatste = Range("A65536").End(xlUp).Row
For r = 2 To laatste
blad = Cells(r, 1)
On Error Resume Next
rij = Sheets(blad).Range("A65536").End(xlUp).Row + 1
If Err.Number <> 0 Then
On Error GoTo 0
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = blad
rij = 1
ws.Select
End If
Rows(r).Copy Sheets(blad).Rows(rij)
Next r
End Sub


Thanks the macro works.

P.

[lucas]

P, there's still something wrong with your code. comment out the line that says


on error resume next

That line of code will actually mask errors that you should try to fix.

I see you are adding new sheets and naming them according to column a data starting in A2 down.

The part I don't understand is


Rows(r).Copy Sheets(blad).Rows(rij)


I am probably missing something but why don't you just copy the sheet?

[Pinokkio]

Rows(r).Copy Sheets(blad).Rows(rij)

This was a test, but I don't need it any more.

I need the macro to make sheets from data in colum A from the sheet Klanten.

[lucas]

Sub test()
Dim ws As Worksheet
Dim r As Long, laatste As Long
Dim blad As String, rij As Long
Dim wb As Workbook
Application.ScreenUpdating = False

Set ws = Sheets("Klanten")
With ws
laatste = .Range("A65536").End(xlUp).Row
For r = 2 To laatste
blad = .Cells(r, 1)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = blad
Next r
.Activate
End With
Application.ScreenUpdating = True
End Sub

Edit: Fully qualified which sheet we are working with. Thanks Malcolm.

[Pinokkio]

Lucas,

If I use the macro form #3 I can add names,
if I use the macro form #6 I cannot add names and the run the macro ?

[lucas]

Try this P:
Option Explicit
Sub test()
Dim WS As Worksheet
Dim r As Long, laatste As Long
Dim blad As String
Dim sh As Worksheet
Application.ScreenUpdating = False
Set WS = Sheets("Klanten")
With WS
laatste = .Range("A65536").End(xlUp).Row
For r = 2 To laatste
blad = .Cells(r, 1)
If SheetExists(blad) Then
Set sh = Worksheets(blad)
Else
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = blad
End If
Next r
.Activate
End With
Application.ScreenUpdating = True
End Sub
Function SheetExists(sh As String, _
Optional wb As Workbook) As Boolean
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing)
On Error GoTo 0
End Function

[Pinokkio]

Works great now.

Thanks,

P.