THANKS A BUNCH!!!!!! Spreadsheet is doing everything I was hoping it could do.

"Dave Peterson" wrote:

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Could become:
Wkbk.Worksheets("sheet3").Range("o4:o22").Copy
destcell.pastespecial paste:=xlpastevalues


Louisville Cardinals wrote:

Figured out the macro so previous post can be ignored. How ever my new
problem is that the information that is being copied is the result of a
formula; therefore I get
the #REF! error. I need to just paste the values. What line do I need to
add or edit inorder to just paste the values? Once again thanks for all your
help!!
"Dave Peterson" wrote:

I'd use something like:

Option Explicit
Sub CommandButton1_Click()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

Set ActSheet = activesheet

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With Me
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub

By opening, copying, then closing, you should be back where you started.

And I'm assuming you used a commandbutton from the control toolbar toolbox.
(The Me keyword refers to the sheet that owns that commandbutton.

DestCell is where the copied range is pasted. I used the first open cell in
column A (working from the bottom up.)

But you could set that destcell to anything you want.

Louisville Cardinals wrote:

Ok. Here is a new twist. I need to be able to copy the info from the daily
spreadsheets into the spreadsheet that activated the macro. I have the
following code so far but I am stuck. When I click on the command button I
am prompted for the file to pull the information out of but I need to switch
back to the file with the command button and paste the info that was copied.

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
Worksheets("sheet3").Range("O4:O22").COPY

End If
End Sub

Thanks for your help....... again

--

Dave Peterson


--

Dave Peterson