Copy Row(s) from Workbook Sheets to Other Workbook Sheets

[Lori Worthington]

I would like to copy rows from a sheet called "June" in the Assessment_RMHC Physician Coverage.xls workbook into the sheet called "June" in the MASTER_RMHC Physician Coverage System.xls

So say I need to copy the range E8:AI8 and row 10, E10:AI10 from the workbook Assessment_RMHC Physician Coverage.xls (sheet "June") into the range E8:AI8 and row 10, E10:AI10 (range could be on another row but for simplicity sake) into the sheet called "June" in the MASTER_RMHC Physician Coverage System.xls how can I do this with a macro?

The Assessment_RMHC Physician Coverage.xls will be open and be the updated live document, which I need to populate the closed MASTER_RMHC Physician Coverage System.xls that will only be used for an "at a glance" occassionally.

Please advise - thanks so much!

 

[MorganO]

I beleive the following will work for you. If you create a commandbutton on your 'Assessment' sheet and paste this code into the 'Click' event of the Command button it should copy and paste as you need:

Private Sub CommandButton1_Click()
Workbooks.Open "MASTER_RMHC Physician Coverage.xls"
Workbooks("MASTER_RMHC Physician Coverage.xls").Worksheets("Sheet1").Range("E8:AI8") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("Sheet1").Range("$A$1:AF$1").Value
Workbooks("MASTER_RMHC Physician Coverage.xls").Worksheets("Sheet1").Range("E10:AI10") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("Sheet1").Range("$A$1:AF$1").Value
Workbooks("MASTER_RMHC Physician Coverage.xls").Close savechanges:=True
End Sub

In the line:
Workbooks.Open "MASTER_RMHC Physician Coverage.xls"

You may need to specify the complete path to the file, ie:
Workbooks.Open "C:\Data\backup\MASTER_RMHC Physician Coverage.xls"

Take care.

Owen

 

[Lori Worthington]

Thank you so much! Okay, I hope I'm getting closer.. I got a run time error '9': Subscript out of range. I am very grateful for your time/skills. I would like to attach the files but not sure how, will look into that.

Lori

 

[Lori Worthington]

I used the following code in a module that is linked to a command button. When you click the command button you get the run time error '9': Subscript out of range.

Sub Button1_Click()
Workbooks.Open "C:\Documents and Settings\Admin\My Documents\St. Josephs\MASTER_RMHC Physician Coverage System.xls"
Workbooks("MASTER_RMHC Physician Coverage System.xls").Worksheets("Sheet1").Range("E8:AI8") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("Sheet1").Range("$A$1:AF$1").Value
Workbooks("MASTER_RMHC Physician Coverage System.xls").Worksheets("Sheet1").Range("E10:AI10") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("Sheet1").Range("$A$1:AF$1").Value
Workbooks("MASTER_RMHC Physician Coverage System.xls").Close savechanges:=True
End Sub

 

[Lori Worthington]

Wait, I got it doing something...I used:

Sub Button1_Click()
Workbooks.Open "C:\Documents and Settings\Admin\My Documents\St. Josephs\MASTER_RMHC Physician Coverage System.xls"
Workbooks("MASTER_RMHC Physician Coverage System.xls").Worksheets("June").Range("E8:AI8") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("June").Range("$A$1:AF$1").Value
Workbooks("MASTER_RMHC Physician Coverage System.xls").Worksheets("June").Range("E10:AI10") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("June").Range("$A$1:AF$1").Value
Workbooks("MASTER_RMHC Physician Coverage System.xls").Close savechanges:=True
End Sub

ANd now I just have to figure out how to get the right data into the right place. It's working, just not the right stuff...more soon..I'll keep you posted!

 

[MorganO]

Glad to see it working for you! If you need further assistance in understanding how to do something in the code let us know. Also, keep up the 'playing with code.' That is exactly how I got started doing VBA. Kept trying new things until I got something working - didn't always work right but hey I was learning!

Take care.

Owen

 

[Lori Worthington]

I got it!!!! THANK YOU!!

I used:

Sub Button1_Click()
Workbooks.Open "C:\Documents and Settings\Admin\My Documents\St. Josephs\MASTER_RMHC Physician Coverage System.xls"
Workbooks("MASTER_RMHC Physician Coverage System.xls").Worksheets("June").Range("E8:AI8") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("June").Range("$E$8:AI$8").Value
Workbooks("MASTER_RMHC Physician Coverage System.xls").Worksheets("June").Range("E9:AI9") = Workbooks("Assessment_RMHC Physician Coverage.xls").Worksheets("June").Range("$E$13:AI$13").Value
Workbooks("MASTER_RMHC Physician Coverage System.xls").Close savechanges:=True
End Sub


I had to rename the work sheets, change the ranges, and that's it. GOsh I'm dumb. Thanks again, I am so relieved!