Copy Row By Refer To Range, And Suppress “cannot Edit Links in Excel

[kopja]

Hello all

I have 10 workbooks which have various calculations and have several links to each other. We use Excel 2003.
For each of these files, I have the following code in the Autpen module
Range Range(“TodayComp”) is a date taken form a link in another spreadsheet. It is on Column 3, but the row changes every day as more rows are inserted before it
This sub looks at Range(“TodayComp”) and checks the date on the cell directly above.
If say Range(“TodayComp”) is 6/25/2008, and the cell above is 6/22/2008, it will insert three rows right above Range(“TodayComp”). These rows need to be copied with formulas from the current row above Range(“TodayComp”)

Issue One:
The following code works (it copies the rows), but it takes forever to run, as you can see in bold, I copy and paste each cell, instead of the entire row.
Is there a way to copy and paste the entire row? Remember that I have only a named range to refer to, no cell address as it changes every time. Also, the formulas need to be copied over.
Public Sub Auto_open()
Update_Dates()
End Sub

Public Sub Update_Dates()
Dim tdy, prev As Date
Dim index, i, j, yr, no_inserts As Integer

Sheets("Comparison Computation").Activate

tdy = Range("TodayComp").Value
prev = Range("TodayComp").Offset(-1, 0).Value
yr = Range("TodayComp").Offset(-1, -1).Value

no_inserts = DateDiff("d", prev, tdy) - 1
If no_inserts > 0 Then
Application. ScreenUpdating = False
For i = 1 To no_inserts
Range("TodayComp").Select
Selection.EntireRow.Insert Shift:=xlDown

For j = 0 To 176
Range("TodayComp").Offset(-2, j - 1).Select
Selection.Copy
Range("TodayComp").Offset(-1, j - 1).Select
ActiveSheet.Paste
Range("TodayComp").Select
Next j
Next i
Application.ScreenUpdating = True
End If
End Sub
[/color red]

Issue Two:
As I mentioned, we need to open these workbooks everyday, and run the Autpen macro so that it adds the rows.
I wanted to create a script that updates this automatically every night without user intervention, so I thought I would create another Excel file, and in its AutoOpen put the code below.
Now, I do need to update the links, so I put the UpdateLinks:=True.
However, some links cannot be updated, due to various files being unavailable depending on the time of day, so I also get the message “Some links cannot be updated, Hit Continue, or Edit Links”.
Is there a way to suppress the above message (and have the equivalent of Hitting Continue), as that stops the original script from finishing until user intervention?
What about any other error messages (Sometimes, due to the files being large, I get the message “Excel cannot continue with the available resources, click OK”)
Oh, and the workbooks have to be open in a certain order by filename for the links to be updated correctly.


Public Sub Auto_open()
Workbooks.Open Filename:="Y:\Filename.xls", UpdateLinks:=True
With ActiveWorkbook
.RunAutoMacros xlAutoOpen
.Save
End With
Workbooks.Open Filename:="Y:\Filename2.xls", UpdateLinks:=True
With ActiveWorkbook
.RunAutoMacros xlAutoOpen
.Save
End With
…………
Workbooks.Open Filename:="Y:\Filename10.xls", UpdateLinks:=True
With ActiveWorkbook
.RunAutoMacros xlAutoOpen
.Save
End With

Workbooks.Close ‘this should Close every Open workbook

End Sub
[/color red]
Sorry for the long post, but I wasn’t sure how to best explain the issues.

Thanks in advance.

 

[SkipVought]

Hi,

replace your bolded for ... next with...

  with Range("TodayComp")
    Range(.offset(-2,-1), .offset(-2,175)).copy Destination:=Range(.offset(-1,-1), .offset(-1,175))
  end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kopja]

Thank you that worked. It is still rather slow, but definitely faster than going through the loop.

Anyone has any hints on issue #2?

Thanks,
kopja

 

[kopja]

I was able to find the solution to #2 in another forum, basically, just added "Application.DisplayAlerts = False"

Thanks,
kopja