Copy areas with formulas and references to other sheets

[Son]

i am wondering if i can do the following with a macro.

i have 10 workbooks in a directory and i need them to be copied to a master workbook. This should be done once a month.

the format is like this:

<TABLE style="WIDTH: 368pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=490 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6217" width=170><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 width=79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 118pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=157>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 128pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=170>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=84>C</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: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:str="'=SUM(A3:A5)">=SUM(A3:A5)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:str="'=SUM(B3:B5)">=SUM(B3:B5)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 x:str="'=SUM(A1:B1)">=SUM(A1:B1)</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: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29></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: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:str="'=C:\[wb1.xls]sheet1'!B22">=C:\[wb1.xls]sheet1'!B22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:str="'=C:\[wb1.xls]sheet2'!F42">=C:\[wb1.xls]sheet2'!F42</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 x:str="'=SUM(A3:B3)">=SUM(A3:B3)</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: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29></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: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:str="'=C:\[wb1.xls]sheet1'!C53">=C:\[wb1.xls]sheet1'!C53</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:str="'=C:\[wb1.xls]sheet3'!$D$43">=C:\[wb1.xls]sheet3'!$D$43</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 x:str="'=SUM(A5:B5)">=SUM(A5:B5)</TD></TR></TBODY></TABLE>

The sum formulas are always the same, but the formulas in cells A3, A5, B3 all refer to other worksheets and the formula in cell B5 has absolute reference to other sheets.

i have tried copying the whole range A1:C5 to the master workbook, for all workbooks (one table at range A1, the second at A7 etc), but i find that the cells that refer to other wbks change their content.

i tried converting all formulas to absolute references, but then i dont get the correct result for the formulas with sum().

do you have any suggestion on how i should solve this problem? I'd rather not change the original 10 sheets, because they are alrready being used by the users and i would prefer not to take them back and change them.

the workbboks name changes each month, so i dont think i could just link all the cells to the wbks, except if i do it using a macro.

any ideas would be most welcomed!

 

[Son]

ok, here's what i've come up: what it i can set the absolute reference to be applied to any formula that's longer than 25 digits? this way, the sum formulas will not be affected. But, i don't know how to do that in the code i'm using.

Here's the code that turns the cells to absolute references:

Sub MakeAbsoluteAll()
 
Dim RdoRange As Range
Dim i As Integer
 
On Error Resume Next
 
Set RdoRange = Selection.SpecialCells(Type:=xlFormulas) 'Set Range variable to formula cells only
 
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next i
 
 
Set RdoRange = Nothing 'Clear memory
End Sub

Do you have any ideas on how to deal with this, or am i approaching this in the wrong direction?

 

[Son]

so, here's the problem again:

i have 10 workbooks which have a sheet called "A". In this sheet, there is a table which contains: a) references to other sheets of each workbook, b) sum functions to sum the data in the table.

I need each of these "A" sheets to be copied on a master workbook in a sheet called "B". One table should be copied after the other, downwards, ie one in cell A1, the second in cell A10 etc.

If i copied all tables with paste special, only values, then i would have the correct values for the cells containing references, which would be ok, but i would lose the sum function for each of the 10 tables.

So, i thought to make absolute the cell references in the original tables before i copy them to the master worksheet "B", but this works for the references to other sheets, but not to the summing cells.

of course one solution would be to copy only the values and then create a macro to copy all the sum formulas into the 10 tables, but there are many different cells that contain the sum function and i'm not sure this is the correct way.

another solution would perhaps be to make absolute only the cells that contain references to other sheets, ie that are longer than, say 25 digits. Or if they contain a special character, such as ! . But i don't know how to do this.

Any ideas on how i could solve this problem?