It sounds like you want to have an identity in your lead table, but prepopulate it with existing values.

Since you were kind enough not to provide any sample data or table definition, I'm going to wing it with some dummy tables:

create table Sheet (id INT, someOtherData VARCHAR(30))

create table Lead (id INT IDENTITY, someOtherData VARCHAR(30))

-- Some test data
INSERT INTO Sheet
VALUES  (1, 'Test1')
,   (2, 'Test2')
,   (3, 'Test3')

-- Now the actual insert...

SET IDENTITY_INSERT Lead ON

INSERT INTO Lead (
    id, someOtherData
)
SELECT  ROW_NUMBER() OVER(ORDER BY id DESC), someOtherData
FROM    Sheet

SET IDENTITY_INSERT Lead OFF

-- This fixes the current identity value
DBCC CHECKIDENT(Lead, RESEED);

-- As a test, add another Lead
INSERT INTO Lead (someOtherData)
SELECT  'TestTest'

SELECT  *
FROM    Lead

One thing I don't like with this solution is that you're losing the connection between the original Sheet and Lead data. I'm not sure why you want to "first ID of the table sheet to become the last ID of the lead table", but if you don't, you can just change ROW_NUMBER thing to "id".