get sheet name into a vba line of code

Hattie

New Member
Joined
Oct 7, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
the following line of code works but copies from sheet4 to sheet2
i want to specify different source sheets from time to time.

How do i replace the static sheet4 in the line below with a cell reference
so i can just type a sheet name of the source sheet into cell a1of the target sheet?

how do i replace sheet2 with the current sheet i am on (the target sheet)

'Copy cell to another cell

Sheets("Sheet4").Range("A1").Copy Sheets("Sheet2").Range("B11")

thanks
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
as an example, you can use a variable to define a range value then use the variable in the code. You only need to change the value in the cells to chnge the sheet name in the code. Be sure you change all occurences of the sheet name wihin the code or you could get undesired results.

VBA Code:
Dim shNm1 As String, shNm2 As String
 shNm1 = Range("E1").Value 'Change range to suit
shNm2 = Range("E2").Value 'Change range to suit
Sheets(shNm1).Range("A1").Copy Sheets(shNm2).Range("B11")
 
Solution

Hattie

New Member
Joined
Oct 7, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
thank you, that works a treat.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,940
Messages
5,621,728
Members
415,853
Latest member
Newlife72

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