Copy Sheet1 from Workbook1 to Sheet2 Workbook2

[RyanH]

I am learning VBA Excel and have attempted this, but can't seem to get it to
all work together. I have a production schedule that I like to update at the
end of each day. I would like to Copy Sheet1(Values Only) from
Workbook1(fixed file name) to Sheet2 Workbook2(which file name changes once a
day). I would like the user to be prompted with the dialog box so they can
find the file(Workbook2) they want to paste the Cell Values,because the
Workbook2 file name changes everyday.

**NOTE: I think this is important - I will be using a command button to call
this macro from Sheet3 of the new and old Workbook1 and 2. I believe I can
take it from here. Any response would be greatly appreciated!

 

[Dan R.]

Sub test()
Dim ws As Worksheet
Dim wb As Workbook
Dim fName As Variant

Set ws = ThisWorkbook.Sheets(1)

fName = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls), *.xls")

If fName = False Then
Exit Sub
Else
Set wb = Workbooks.Open(fName)
End If

ws.UsedRange.Copy
wb.Sheets(2).Range("A1") _
.PasteSpecial (xlPasteValues)

wb.Close SaveChanges:=True

End Sub

 

[RyanH]

Thanks for getting back to me Dan! I did come across a problem running this
though:
What if the workbook I want to update (Workbook2) is alreadly open when I
copy Workbook1.Sheet1 (wbCrystal.wsCrystal) and paste to Workbook2.Sheet2
(wbGlobal.wsGlobal)? A window pops up saying it is already open forcing me
to save or continue which the user will loose any changes they have made.

Is there a way to get the new data from another workbook without physically
opening it?

I attempted to make some edits that will not work, but this will demonstrate
the basic goal.

Sub ImportCrystalReport()

Dim wsCrystal As Worksheet, wsGlobal As Worksheet
Dim wbCrystal As Workbook, wbGlobal As Workbook
Dim fName1 As Variant, fName2 As Variant

Msg1 = MsgBox("Click OK to select the file with the new data.", vbOKCancel)
If Msg1 <> vbOK Then Exit Sub

fName1 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls;*.csv),
*.xls;*.csv")
If fName1 = False Then Exit Sub
Set wbCrystal = Workbooks.Open(fName1)

wsCrystal = wbCrystal.Sheets("production schedule 001")
wsCrystal.Cells.Copy

Msg2 = MsgBox("Click OK to select the file you want to update.", vbOKCancel)
If Msg2 <> vbOK Then Exit Sub

fName2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls")
If fName2 = False Then Exit Sub
Set wbGlobal = Workbooks.Open(fName2)
wbGlobal.Sheets("Crystal Reports").Range("A1").PasteSpecial (xlPasteValues)

wbCrystal.Close SaveChanges:=False

End Sub