Macro to copy the worksheet in excel 2007 to excel 2002

P

pat01

New Member
Joined
Aug 2, 2009
Messages
29
Hi,

I need your help please.... I have to create the macro to copy let say worksheet called "Main" - workbook "Data"..This worksheet is build in Excel 2007.. I have to copy that worksheet into the new worksheet called "Consolidation" in the workbook "Master"... This worksheet is build in Excel 2002... This macro has to be run on a daily basis so the new set of the data will replace the old one in the sheet "Consolidation"..
Thanks in advance,
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Be aware that if you have some data in Excel 2007 that is beyond sheet's rows and columns limits, it will be just cut out.
 
Upvote 0
Check the workbook and sheet names but see if something like this is any use. It assumes both workbooks are open when the code is run. If that is not the case, tell us where the workbooks reside as well as their full names (including extension).

Make sure you have made a backup copy of the workbooks before testing the code.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Copy_Main()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsM <SPAN style="color:#00007F">As</SPAN> Worksheet, wsC <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsM = Workbooks("Data.xlsx").Sheets("Main")<br>    <SPAN style="color:#00007F">Set</SPAN> wsC = Workbooks("Master.xls").Sheets("Consolidation")<br>    <br>    wsC.UsedRange.Clear<br>    <SPAN style="color:#00007F">With</SPAN> wsM.UsedRange<br>        .Copy Destination:=wsC.Range(.Address)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Hi,

Thanks for your response, however, I am getting the run time error '9' message : subscript out of range on row
Set wsC = Workbooks("Master.xls").Sheets("Consolidation")


My both workbooks are open when I am trying to run the macro...
Also, my excel 2007 workbook is Excel Macro Enabled Workbook so I changed the extension code in your macro to
Set wsM = Workbooks("Data.xlsm").Sheets("Main")

Anu suggestions?
Thanks
 
Upvote 0
One possibility is that you 'Hide file extensions for known file types' set in your windows folder options.

If you do not see the .xls or .xlsm in the title bar in Excel, try removing those from the code as well. So you would have
Code: 
Set wsM = Workbooks("Data").Sheets("Main")
Set wsC = Workbooks("Master").Sheets("Consolidation")
 
Upvote 0
OK, the error indicates that a workbook or worksheet name in the code is not the same as the actual name so try this:

1. Open those two workbooks

2. Open a new completely blank workbook.

3. Make sure the new blank workbook is the active workbook

4. Put this code in a standard Module in any of the workbooks (the new blank one would be fine)


Sub Test()
Dim wb As Workbook
Dim ws As Worksheet
Dim r As Long

For Each wb In Workbooks
If wb.Name Like "Master*" Or wb.Name Like "Data*" Then
r = r + 2
Cells(r, 1).Value = "(" & wb.Name & ")"
For Each ws In wb.Worksheets
r = r + 1
Cells(r, 1).Value = "(" & ws.Name & ")"
Next ws
End If
Next wb
Columns("A").AutoFit
End Sub


5. Run the code and report the results (unless either of the workbooks has too many sheets to report). For me it is

<TABLE style="WIDTH: 80pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=106><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 4522" width=106><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 80pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17 width=106> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Data.xlsm)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Sheet1)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Sheet2)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Main)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Master.xls)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Consolidation)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(abc d)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17> </TD></TR></TBODY></TABLE>

Note that the () are there so that if any of the names happen to have leading or trailing spaces, they should be able to be seen
 
Upvote 0
OK, the error indicates that a workbook or worksheet name in the code is not the same as the actual name so try this:

1. Open those two workbooks

2. Open a new completely blank workbook.

3. Make sure the new blank workbook is the active workbook

4. Put this code in a standard Module in any of the workbooks (the new blank one would be fine)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test()<br>    <SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wb <SPAN style="color:#00007F">In</SPAN> Workbooks<br>        <SPAN style="color:#00007F">If</SPAN> wb.Name <SPAN style="color:#00007F">Like</SPAN> "Master*" <SPAN style="color:#00007F">Or</SPAN> wb.Name <SPAN style="color:#00007F">Like</SPAN> "Data*" <SPAN style="color:#00007F">Then</SPAN><br>            r = r + 2<br>            Cells(r, 1).Value = "(" & wb.Name & ")"<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> wb.Worksheets<br>                r = r + 1<br>                Cells(r, 1).Value = "(" & ws.Name & ")"<br>            <SPAN style="color:#00007F">Next</SPAN> ws<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wb<br>    Columns("A").AutoFit<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


5. Run the code and report the results (unless either of the workbooks has too many sheets to report). For me it is

<TABLE style="WIDTH: 80pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=106><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 4522" width=106><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 80pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17 width=106> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Data.xlsm)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Sheet1)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Sheet2)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Main)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Master.xls)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(Consolidation)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>(abc d)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17> </TD></TR></TBODY></TABLE>

Note that the () are there so that if any of the names happen to have leading or trailing spaces, they should be able to be seen
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Cannot assign macro to button
2
Replies
10
Views
275
SPS41
S
J
  • Solved
Speeding up VBA
Replies
9
Views
246
jvoss
J
V
  • Question Question
Copy Range from Multiple Sheets and Paste Link into a Single Sheet
Replies
2
Views
324
vbascientist
V
P
  • Solved
New Worksheet Hyperlinks
2
Replies
12
Views
262
Peter_SSs
Peter_SSs
Y
  • Question Question
Consolidate all values from specific sheets into 1 master excel file
Replies
1
Views
242
Trebor76
T

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

Share this page

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