VBA sheet name query

Katie152

New Member
Joined
Nov 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
Yes. You just use Sheet1.Range etc if you want to use the code name. For example:

VBA Code:
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
 
Solution

Forum statistics

Threads
1,140,923
Messages
5,703,178
Members
421,280
Latest member
Jaycee01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top