Collect Info From Another Workbook

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
986
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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.

Code:
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
 
Upvote 0
Hi Mike,

This is your 'starter for 10':
Code:
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
 
Upvote 0
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. :oops:

Thanks

Mike
 
Upvote 0
Code:
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
 
Upvote 0
Code:
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
 
Upvote 0
Hi Alan,

That works like a charm.

Thankyou for all your help.

Have a nice weekend.

Thanks

Mike :-D
 
Upvote 0

Forum statistics

Threads
1,224,427
Messages
6,178,587
Members
452,860
Latest member
jroberts02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top