• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 30
  • Last Modified:

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

0
Flora
Asked:
Flora
  • 2
1 Solution
 
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
 
FloraAuthor Commented:
thanks alot
0
 
FloraAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now