Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet1 from Workbook1 to Sheet2 Workbook2
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet1 from Workbook1 to Sheet2 Workbook2
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 -- Dan Oakes |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet1 from Workbook1 to Sheet2 Workbook2
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 "Dan R." wrote: 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 -- Dan Oakes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
Extracting data from workbook1 to workbook2 | Excel Worksheet Functions | |||
Copying Selected Rows From Workbook1 to WorkBook2 | Excel Discussion (Misc queries) | |||
Can we copy a sheet from workbook1 to workbook2 with out open workbook1? | Excel Programming | |||
How to save the workbook1 from workbook2 | Excel Programming |