CODEWITHSUNDEEP
excelvba
Saverio
Saverio

Reputation: 111

Copy paste entire worksheet with InputBox

I have this code and I am trying to do a simple task, which apparently it is not so simple to me. I want to say to vba which sheet to copy (here the functionality of the InputBox, where I insert the sheet name) and then, if exists (i.e. if the name is correct) perform the copy paste in the sheet20, if it does not exist, go to exitmysub.

Now, I have two problems:

1) It does not copy paste. Or at least, not always. Sometimes yes, sometimes not. And I really dont understand why (I always put the correct sheet names)

2) Even if the name is correct, the code runs the msgbox ("Invalid Sheet Name"). While I wantthat it triggers only if the sheet name I put does not exist.

Thank you for you help!

Option Explicit

Dim text As String, ws As Worksheet


Sub copyentiresheet()

text = InputBox("Write here the Local Deposit Sheet you want to update", "Update Local Deposit Monitoring")

On Error GoTo exitmysub
Sheet20.Cells.Clear
Set ws = Sheets(text)
    ws.Cells.Copy

Sheets20.Paste


exitmysub:
MsgBox ("Invalid Sheet Name")

End Sub

Upvotes: 3

Views: 97

Answers (2)

VBasic2008
VBasic2008

Reputation: 54797

Copy Entire Sheet

Highlights

  • InputBox VBA Help: If the user clicks Cancel, the function returns a zero-length string ("").
  • If the sheet's CodeName is wrong, the code won't compile. No error handling necessary.
  • Use the With Statement to avoid declaring unnecessary object references.
  • Refer to a workbook using CodeName.Parent to avoid wrecking yourself about choosing between ActiveWorkbook, ThisWorkbook or workbook by name.
  • Exit Sub before (in between) error handlers.

The Code

Option Explicit

Sub CopyEntireSheet()

  Dim text As String

  text = InputBox("Write here the Local Deposit Sheet you want to update", _
      "Update Local Deposit Monitoring")

  If text = "" Then Exit Sub ' If the user clicks Cancel.

  Sheet20.Cells.Clear ' If sheet's code name is wrong, code won't compile.

  On Error GoTo SourceSheetErr

  With Sheet20.Parent.Worksheets(text)

    On Error GoTo RangeErr
    .Cells.Copy Sheet20.Cells

  End With

Exit Sub

SourceSheetErr:
  MsgBox "Invalid Source Sheet Name."
Exit Sub

RangeErr:
  MsgBox "(Copy) Range Error."

End Sub

Upvotes: 1

StoneGiant
StoneGiant

Reputation: 1497

Try this...

Option Explicit

Dim text As String, ws As Worksheet

Sub copyentiresheet()

text = InputBox("Write here the Local Deposit Sheet you want to update", "Update Local Deposit Monitoring")

On Error GoTo ErrorMySub
Sheet20.Cells.Clear
Set ws = Sheets(text)
ws.Cells.Copy Sheet20.Range("A1")

ExitMySub:
Exit Sub

ErrorMySub:
MsgBox ("Invalid Sheet Name")

End Sub

Upvotes: 2

Related Questions