Simple VBA issue!! Please Help!

[Leyton]

Hi,

I'm very new to VBA and I'm trying to write a code that simply takes the data from one tab and copies it into a new tab then changes the name of the newly created tab to the value of cell C9, I'm sure this is very simple but i can't link the 2 steps and i keep getting error messages!!

Here is the code i have but it doesn't work -
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Change name of tab
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C9" Then
If Len(Trim(Target.Value)) > 0 Then
On Error Resume Next
Me.Name = Target.Value
On Error GoTo 0
End If
End If
End Sub

Any help would be greatly appreciated!

 

[njimack]

Welcome to the board. You don't need to select cells/sheets to perform actions on them. Give this a whirl...

Sub TEST()

Application.ScreenUpdating = False

Sheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible).Copy  'change sheet name as required

Sheets.Add After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
    .Cells.PasteSpecial xlPasteValues
    .Cells.PasteSpecial xlPasteFormats
    .Name = .Range("C9").Value
End With

Application.ScreenUpdating = True

End Sub

 

[Leyton]

Hi,

Thanks for the quick reply, I have just tried adding in the code but it comes up with a runtime error saying the subscript is out of range? I'm adding the code to a button in the workbook if that makes any difference?

Sub Button1_Click()
Application.ScreenUpdating = False
Sheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible).Copy 'change sheet name as required
Sheets.Add After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Cells.PasteSpecial xlPasteValues
.Cells.PasteSpecial xlPasteFormats
.Name = .Range("C9").Value
End With
Application.ScreenUpdating = True
End Sub

 

[njimack]

The code ran fine for me. Which line is highlighted when you go into debug?

 

[Leyton]

Hi,

This was the line that is highlighted -

Sheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible).Copy 'change sheet name as required


I can't attach the file I'm working on otherwise i would send it through?

 

[njimack]

If you don't have a sheet called Sheet1 then the code will fail - that's why I included a comment to change the sheet name as required.

 

[Leyton]

Oops sorry, missed that! yes it works perfectly sorry for being slow!

Thank you

 

[njimack]

No worries, glad it's working now.

 

[Leyton]

Hi again,

Not sure if i should start a new thread or just carry on with this one but the macro is working perfectly however i have a issue because when the tab is copied across when i try to save the file after creating one new tab the file size is over 5MB and i need to create around 20 tabs so the file will be far too big! is there another way of doing this so the file size doesn't get excessive?!

 

[njimack]

Hi again,

Not sure if i should start a new thread or just carry on with this one but the macro is working perfectly however i have a issue because when the tab is copied across when i try to save the file after creating one new tab the file size is over 5MB and i need to create around 20 tabs so the file will be far too big! is there another way of doing this so the file size doesn't get excessive?!

It's probably because you're copying every cell onto a new sheet - if you post a sample of your data we could probably restrict the range that gets copied