VBA sheet name query

[Katie152]

Hello,

I have a workbook in excel which I will fill in the details on one worksheet and then copy and paste the information into a data set. Due to the nature of the data I have several copies of the worksheet for different people so have used VBA to change the name of the tab to reference a particular cell. The name of the worksheet will therefore change once that sheet is re-used for another person

I have the following code which works perfectly for copying across the data. However, I'm struggling to reference the worksheet as I need to be able to change the tab name on a regular basis. Is it possible to reference "Leavers Checklist" as active worksheet or to reference it as its code name Sheet1 rather than using the tab name which will change?

Sub Submit()
Dim i As Integer
i = 1
While ThisWorkbook.Worksheets("Audit Checklist").Range("A" & i).Value <> ""
i = i + 1
Wend
'Backup Data Form
Worksheets("Audit Checklist").Range("a" & i).Value = Worksheets("Leavers Checklist").Range("D6").Value
Worksheets("Audit Checklist").Range("b" & i).Value = Worksheets("Leavers Checklist").Range("k6").Value
Worksheets("Audit Checklist").Range("c" & i).Value = Worksheets("Leavers Checklist").Range("E8").Value
Worksheets("Audit Checklist").Range("d" & i).Value = Worksheets("Leavers Checklist").Range("k13").Value
Worksheets("Audit Checklist").Range("e" & i).Value = Worksheets("Leavers Checklist").Range("F27").Value
Worksheets("Audit Checklist").Range("f" & i).Value = Worksheets("Leavers Checklist").Range("G25").Value
Worksheets("Audit Checklist").Range("g" & i).Value = Worksheets("Leavers Checklist").Range("G31").Value

 

[steve the fish]

Yes. You just use Sheet1.Range etc if you want to use the code name. For example:

Sub Submit()

Dim lr As Long

With Sheets("Audit Checklist")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & lr).Value = Sheet1.Range("D6").Value
    .Range("B" & lr).Value = Sheet1.Range("K6").Value
    .Range("C" & lr).Value = Sheet1.Range("E8").Value
    .Range("D" & lr).Value = Sheet1.Range("K13").Value
    .Range("E" & lr).Value = Sheet1.Range("F27").Value
    .Range("F" & lr).Value = Sheet1.Range("G25").Value
    .Range("G" & lr).Value = Sheet1.Range("G31").Value
End With

End Sub

 

[Katie152]

That's perfect thank you for your help