VBA Copy range from one worksheet to another and rename new worksheet

[weefatb0b]

Hi

Working on VBA to enable me to copy a worksheet to a new worksheet within the same Workbook. I have got to here, and it does it all fine, however rather than copy the whole worksheet I only want to Copy Columns A to I, as columns J onwards contain Control Buttons that are not required on the copy

'Sub FinanceDirectorCopy()
'Dim sh As Worksheet

'Set sh = Sheets("Position")

'sh.Copy After:=Sheets(Sheets.Count)

'If sh.Range("A1").Value <> "" Then

'ActiveSheet.Name = "Finance Director" 'sh.Range("A1").Value

'End If

'sh.Activate

'End Sub


I have tried changing If sh.Range("A1") to If sh.Range("A1:I600") and to If sh.Range("A:I") but I keep getting debug errors

A1 to I600 is all I want to copy to the new named sheet.

Any help would be appreciated?

Thanks

 

[davesexcel]

Sub CopyAtoI()
Dim sh As Worksheet, ws As Worksheet, rng As Range

Set sh = Sheets("Position")

With sh
Set rng = .Range("A1:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
Set ws = Sheets.Add
ws.Name = sh.Range("A1").Value
rng.Copy ws.Range("A1")


End Sub

 

[weefatb0b]

Dave, you sir are a star, thank you very much