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

Lori Worthington

New Member
Joined
Jun 24, 2008
Messages
5
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. :eek:

Please advise - thanks so much!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
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:

Code:
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

New Member
Joined
Jun 24, 2008
Messages
5
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

New Member
Joined
Jun 24, 2008
Messages
5
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

New Member
Joined
Jun 24, 2008
Messages
5

ADVERTISEMENT

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

Active Member
Joined
Nov 21, 2006
Messages
483
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

New Member
Joined
Jun 24, 2008
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

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
Top