Collect Info From Another Workbook

[charllie]

Hi Folks,

I have a bit of an issue which needs solving and i need some advice as to what is the best way to do it.

I have a "workbook1" that will be open at all times. It contains several worksheets but the main one for this execise is called "Schedule".

I then have a workbook called "DVD Schedule" which located in my c:drive in a folder called "Schedule" This workbook also contains a worksheet called "Schedule".This workbook will be closed and a new updated copy is placed here every day.

I want to be able to click a commandbutton in "workbook1" which when activated goes to workbook "DVD Schedule" copys the sheet "Schedule" and then replaces the sheet "Schedule" located in "workbook1".

It doesn't really have to replace the worksheet. All i really need to do is transfer the information from the one sheet iand place it in the other sheet.

I hope that makes sense and i am open to suggestions how to do this, i am also flexible regards the workshhets if need be.

I would appreciate anyones help and advice regards this.

Thanks

 

[BrianB]

With a straightforward job like this it is best to record a macro of your process and come back with any problems. Copy/Paste your code into the message.

 

[charllie]

Hi Brian,

Thanks for your reply. I didn't think it was that straight forward copying from c:drive.

Here is the code from a macro i just done.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/11/2005
'

'
    Application.WindowState = xlMinimized 'this is location c:drive, folder "schedule"
    Sheets("Schedule").Select ' This is located in workbook "DVD Schedule"
    Rows("3:174").Select 'Here i need it to select all rows from 3 that contain info
    Selection.Copy 'Copy all the information
    ActiveWindow.SmallScroll Down:=-171
    ActiveWorkbook.Close
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=2
    Sheets("Schedule").Select 'This is now the worksheet that it needs to be pasted to
    Rows("3:174").Select 'Here i need it to clearcontents from row 3
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-186
    Rows("2:2").Select
    ActiveSheet.Paste 'Paste all the inf into the rows from 3 down
    ActiveWindow.SmallScroll Down:=-18
    Range("F16").Select
End Sub

Thanks

 

[al_b_cnu]

Hi Mike,

This is your 'starter for 10':

Sub CopySchedule()
Dim wsSchedMast As Worksheet, wsSched As Worksheet

Set wsSchedMast = Sheets("Schedule")
Workbooks.Open Filename:="C:\Schedule\DVD Schedule.xls", ReadOnly:=True
wsSchedMast.Cells.ClearContents
Set wsSched = Sheets("Schedule")
wsSchedMast.Range(wsSched.UsedRange.Address).Value = wsSched.UsedRange.Value
ActiveWorkbook.Close
End Sub

 

[charllie]

Hi Alan,

That is awesome, thanks so much.

Can i ask, how do i make a slight adjustment.

I want it to collect/copy the information starting at row 2 and then insert/paste the information starting at row 3.

Sorry to be a pain.

Thanks

Mike

 

[al_b_cnu]

Sub CopySchedule()
Dim lRowFr As Long, lRowTo As Long
Dim sFrRange As String, sToRange As String
Dim wsSchedMast As Worksheet, wsSched As Worksheet

Set wsSchedMast = Sheets("Schedule")
Workbooks.Open Filename:="C:\Schedule\DVD Schedule.xls", ReadOnly:=True
lRowTo = wsSchedMast.UsedRange.Row + wsSchedMast.UsedRange.Rows.Count - 1
If lRowTo > 2 Then wsSchedMast.Rows("3:" & lRowTo).ClearContents
Set wsSched = Sheets("Schedule")
lRowFr = 2
lRowTo = wsSched.UsedRange.Row + wsSched.UsedRange.Rows.Count - 1
If lRowTo < lRowFr Then Exit Sub
wsSchedMast.Rows("3:" & lRowTo - 1).Value = wsSched.Rows("2:" & lRowTo).Value
ActiveWorkbook.Close
End Sub

 

[al_b_cnu]

Sub CopySchedule()
Dim lRowFr As Long, lRowTo As Long
Dim wsSchedMast As Worksheet, wsSched As Worksheet

Set wsSchedMast = Sheets("Schedule")
Workbooks.Open Filename:="C:\Schedule\DVD Schedule.xls", ReadOnly:=True
lRowTo = wsSchedMast.UsedRange.Row + wsSchedMast.UsedRange.Rows.Count - 1
If lRowTo > 2 Then wsSchedMast.Rows("3:" & lRowTo).ClearContents
Set wsSched = Sheets("Schedule")
lRowFr = 2
lRowTo = wsSched.UsedRange.Row + wsSched.UsedRange.Rows.Count - 1
If lRowTo < lRowFr Then Exit Sub
wsSchedMast.Rows("3:" & lRowTo - 1).Value = wsSched.Rows("2:" & lRowTo).Value
ActiveWorkbook.Close
End Sub

 

[charllie]

Hi Alan,

That works like a charm.

Thankyou for all your help.

Have a nice weekend.

Thanks

Mike