Copy & Paste
 

I'am using the input from a dialog box to open a particular worksheet, lets
say user selects sheet 2. If sheet one is the current open sheet, and the
code copys from sheet 1, and pastes to sheet 2, then .Visible=False for
sheet 2. How do I select a cell on sheet 1 so that the copied area doesn't
remain highlighted? Also considering that the original sheet (sheet 1) was
also selected from a drop down box. So, I can't just say to select or
activate a certain sheet since I don't know what the original user selected
sheet was.

CopyMonthSheet:
Dim a As Integer, rngAddress
rngAddress = "A4:AG75"
Range(rngAddress).Select
Range(rngAddress).Copy
ActiveSheet.Select
With Worksheets(CStr(Worksheets("Staffing Data").Cells(2, 1).Value))
.Select
End With
Range("A4").Select
ActiveSheet.Paste
ThisWorkbook.DialogSheets("Dialog1").Hide
ActiveWindow.SelectedSheets.Visible = False
Sheets("Daily Staffing").Visible = False
Range("B4").Select
Sheets("Daily Staffing").Visible = True
Thanks

Copy & Paste
 

Your first question was ... How do I select a cell on sheet 1 so tha
the copied area doesn't remain highlighted?

The answer to that question is ... don't "select" it. Just use th
copy command. Try it, you'll like it.

You also wanted to know how to get back to the original sheet that th
user was on. Two different ways to accomplish this:
(1) don't ever leave the original sheet ... you can do the copy an
paste commands from Sheet1 to Sheet2 without ever changing the activ
sheet! Example:
Worksheets("Sheet1").Range("A1:D4").Copy _
destination:=Worksheets("Sheet2").Range("E5")

(2) note the original sheet before you do anything else. Example:
Set origSheet = ActiveSheet
Worksheets("Sheet1").Range("A1:D4").Copy _
destination:=Worksheets("Sheet2").Range("E5")
origSheet.Activat

--
Message posted from http://www.ExcelForum.com