how to set worksheet that gets returned from Inputbox

i have this code below helped by Rgonzo1971

i want to replace the Activesheet with the name of sheet from where i select .

for example.  an inputbox to tell me select the workbook and worksheet based on which the sheet name to be assigned and this activesheet should be replaced with the name of that sheet from another opened workbook

If ActiveSheet.ListObjects.Count > 0 Then
    bFoundLo = True
    Set lo = ActiveSheet.ListObjects(1)
    loCol = Split(lo.Range.Resize(1, 1).Address, "$")(1)
       loRow = lo.Range.Resize(1, 1).Row
    If loCol <> "A" Then Range("A1:" & loCol & "1").Resize(, Range("A1:" & loCol & "1").Columns.Count - 1).EntireColumn.Delete
    If loRow > 1 Then Range("A1:A" & loRow - 1).EntireRow.Delete
    
End If

Open in new window

LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Sub macro()
Set Rng = Nothing
On Error Resume Next
Set Rng = Application.InputBox("Select a range in the right worksheet:","Select sheet" , , , , , , 8)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
Set sh = Rng.Parent
If sh.ListObjects.Count > 0 Then
    bFoundLo = True
    Set lo = sh.ListObjects(1)
    loCol = Split(lo.Range.Resize(1, 1).Address, "$")(1)
       loRow = lo.Range.Resize(1, 1).Row
    If loCol <> "A" Then Range("A1:" & loCol & "1").Resize(, Range("A1:" & loCol & "1").Columns.Count - 1).EntireColumn.Delete
    If loRow > 1 Then Range("A1:A" & loRow - 1).EntireRow.Delete
    
End If
End Sub

Open in new window

you could change the text for comprehension to

"Select A1 in the right worksheet:"

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraAuthor Commented:
thanks alot
0
FloraAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.