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.

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.