Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste - two workbooks
Hi there,
I have this problem - I know nothing about VB for excel but need a script doing. I have two files, one called CW Tracker and another called cw_tracker_data. When I open the CW Tracker file I have a worksheet that has a button. When the button is clicked I want it to go to a worksheet called CW Tracker and delete the contents. Then I want it to open the file called cw_tracker_data and copy the data. (There is only one worksheet). Then I want it to go back to the CW Tracker file and paste the data to the worksheet called CW Tracker. Then I want it to hide the two worksheets not needed to view a summary worksheet. So far this is what I have. Private Sub Update_Worksheet_Click() 'Selects the CW Tracker worksheet Sheets("CW Tracker").Select 'Selects range A1:F25000 Sheet3.Range("A1:F25000").Select 'Deletes the contents of those cells Selection.ClearContents 'Opens the cw_tracker_data file Workbooks.Open Filename:="C:\cw_tracker_data.xls" 'Selects the cw_tracker_data worksheet 'Copies the data 'Selects the CW Tracker worksheet 'Pastes the data 'Hides the CW Tracker worksheet 'Hides the Update File worksheet 'Closes the cw_tracker_data file Any help is much appreciated. Many thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste - two workbooks
Private Sub Update_Worksheet_Click()
Dim bk as workbook, sh as Worksheet Dim sh1 as Worksheet, bOpen as Boolean Application.ScreenUpdating = False bOpen = True Set sh1 = Thisworkbook.Worksheets("cw_tracker") sh1.UsedRange.EntireRow.Delete On Error Resume Next set bk = Workbooks("CW_Tracker_Data.xls") On Error goto 0 if bk is nothing then bOpen = False set bk = workbooks.Open(Filename:="C:\cw_tracker_data.xls") end if set sh = bk.worksheets("cw_tracer_data") sh.UsedRange.copy Destination:=sh1.Range("A1") sh1.Visible = xlSheetHidden me.Visible = xlSheetHidden if not bOpen then bk.close Savechanges:=False end if Application.ScreenUpdating = True End Sub code is untested, but give it a try. -- Regards, Tom Ogilvy "M Shannon" wrote in message ... Hi there, I have this problem - I know nothing about VB for excel but need a script doing. I have two files, one called CW Tracker and another called cw_tracker_data. When I open the CW Tracker file I have a worksheet that has a button. When the button is clicked I want it to go to a worksheet called CW Tracker and delete the contents. Then I want it to open the file called cw_tracker_data and copy the data. (There is only one worksheet). Then I want it to go back to the CW Tracker file and paste the data to the worksheet called CW Tracker. Then I want it to hide the two worksheets not needed to view a summary worksheet. So far this is what I have. Private Sub Update_Worksheet_Click() 'Selects the CW Tracker worksheet Sheets("CW Tracker").Select 'Selects range A1:F25000 Sheet3.Range("A1:F25000").Select 'Deletes the contents of those cells Selection.ClearContents 'Opens the cw_tracker_data file Workbooks.Open Filename:="C:\cw_tracker_data.xls" 'Selects the cw_tracker_data worksheet 'Copies the data 'Selects the CW Tracker worksheet 'Pastes the data 'Hides the CW Tracker worksheet 'Hides the Update File worksheet 'Closes the cw_tracker_data file Any help is much appreciated. Many thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy and paste between workbooks | Excel Discussion (Misc queries) | |||
Can't Copy and Paste between Workbooks | Excel Discussion (Misc queries) | |||
Copy and paste between workbooks | Excel Discussion (Misc queries) | |||
Copy and Paste Between Workbooks | Excel Programming |