Replies

Thanks Hans and Legare.

I tried both sets of code and had better luck with Hans’. I only made one modification to the code and it worked flawlessly. Pasted in below for reference:

‘ BrowseFolder from Don Ceraso:

Public Function BrowseFolder(Optional Title As String = “Select a Folder”, _
Optional RootFolder As Variant) As String
On Error Resume Next
BrowseFolder = CreateObject(“Shell.Application”).BrowseForFolder _
(0, Title, 0, RootFolder).Items.Item.Path
End Function

Sub ProcessWorkbooks()
Dim strFolder As String
Dim strFile As String
Dim wbk As Workbook

On Error GoTo ErrHandler

strFolder = BrowseFolder
If strFolder = “” Then Exit Sub

If Not Right(strFolder, 1) = “” Then
strFolder = strFolder & “”
End If

strFile = Dir(strFolder & “*.xls”)
Do While Not strFile = “”
Set wbk = Workbooks.Open(strFolder & strFile)
wbk.ActiveSheet.Name = “data”
wbk.Close SaveChanges:=True
strFile = Dir
Loop

ExitHandler:
Set wbk = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler

Thanks Steve.

While this wasn’t the formula that I remembered it works even better.

Thanks Jan.

This solution was a bit involved as I had to perform the procedure for about 40 files. However, I am happy with the result.

Was this the only way that the corruption issue can be resolved?

I’m not exactly sure how the file got corrupted in the first place but suspect that, by copying back and forth to a memory stick and working on a different computer repeatedly may have resulted in the introduction of garbage into the files.

Thank you for your help.

Thank you Hans and Legare.

I tried both of your responses and was able to work Legare’s code in a little easier. I will save both responses for future reference.

Thank you both again for your valuable assistance.

Thanks so much John and Rudi.

I have code that does exactly what I needed as a result of your valuable input. I still need to tweak a few things (Unprotect the sheets in the destination workbook that I am modifying and add a prompt when I change the source of the links) but it works.

I am including the code in this thread for the benefit of other users who seek a similar solution.

Dim DWB As Workbook
Dim DestinationBook As String
Set CWB = ActiveWorkbook
DestinationBook = Application.GetOpenFilename(“All Excel Files,*.xls”)
Workbooks.Open DestinationBook
Set DWB = ActiveWorkbook
CWB.Activate
Sheets(“Inter-Branch Allocation”).Range(“A1216:BI1251”).Copy DWB.Sheets(“Inter-Branch Allocation”).Range(“A1216”)
Sheets(“Detailed Financials”).Range(“AA82:BT82”).Copy DWB.Sheets(“Detailed Financials”).Range(“AA82”)
Sheets(“Detailed Financials”).Range(“AA95:BT95”).Copy DWB.Sheets(“Detailed Financials”).Range(“AA95”)
Sheets(“Monthly Plan Summary”).Range(“Y29:BR29”).Copy DWB.Sheets(“Monthly Plan Summary”).Range(“Y29”)

Thanks Rudi.

I have incorporated most of your code but I am hitting a snag. Specifically, the line of code:

Sheets(“Inter-Branch Allocation”).Range(“A1216:BI1251”).Select

is getting flagged with a runtime error 1004 (Select method of Range class failed).

The entire code as I copied and modified from your post follows:

Dim CWB As Workbook
Dim DWB As Workbook
Set CWB = ActiveWorkbook
DestinationBook = Application.GetOpenFilename(“All Excel Files, *.xls”)
Workbooks.Open DestinationBook
Set DWB = ActiveWorkbook
CWB.Activate
Sheets(“Inter-Branch Allocation”).Range(“A1216:BI1251”).Select
Selection.Copy Destination = DWB.Sheets(“Inter-Branch Allocation”).Range(“A1216”)

Thanks for your help.

Thanks Rudi.

I have incorporated most of your code but I am hitting a snag. Specifically, the line of code:

Sheets(“Inter-Branch Allocation”).Range(“A1216:BI1251”).Select

is getting flagged with a runtime error 1004 (Select method of Range class failed).

The entire code as I copied and modified from your post follows:

Dim CWB As Workbook
Dim DWB As Workbook
Set CWB = ActiveWorkbook
DestinationBook = Application.GetOpenFilename(“All Excel Files, *.xls”)
Workbooks.Open DestinationBook
Set DWB = ActiveWorkbook
CWB.Activate
Sheets(“Inter-Branch Allocation”).Range(“A1216:BI1251”).Select
Selection.Copy Destination = DWB.Sheets(“Inter-Branch Allocation”).Range(“A1216”)

Thanks for your help.

The ASAP Utility is amazing. It works perfectly changing a block of cell formulas from relative to absolute (including multiple references within a formula). I am looking forward to checking out the other features.

Thanks Peter

The ASAP Utility is amazing. It works perfectly changing a block of cell formulas from relative to absolute (including multiple references within a formula). I am looking forward to checking out the other features.

Thanks Peter

Thanks. I was hoping to make this as automated as possible since the formula will be copied into many cells and I didn’t want the users to have to go through that process. I believe that the best approach would be to copy the corrected section into the user’s file and use Edit Links to change the source from the fix file to the user’s file.

Thanks. I was hoping to make this as automated as possible since the formula will be copied into many cells and I didn’t want the users to have to go through that process. I believe that the best approach would be to copy the corrected section into the user’s file and use Edit Links to change the source from the fix file to the user’s file.

I just did a copy from one file and paste the macro to the other file. I did solve the problem. I deleted any links, then for the buttons I had to re-assign all macros. It worked, I just thought there might be a faster solution.

I just did a copy from one file and paste the macro to the other file. I did solve the problem. I deleted any links, then for the buttons I had to re-assign all macros. It worked, I just thought there might be a faster solution.

Both subforms are subforms only to the main form. Both have the same link to the form. When I enter data I run a macro to update the forms. The first subform is refreshed, but the second subform does not refresh. Does this help. Here’s more, when I change the payroll in the first subform, and press the calculate button, a macro recalculates the premium. The total of all premiums is listed down in the second subform. The first subform shows the new calculated premium, but the bottom subform does not refresh the sum of all premiums. Thank you.

Both subforms are subforms only to the main form. Both have the same link to the form. When I enter data I run a macro to update the forms. The first subform is refreshed, but the second subform does not refresh. Does this help. Here’s more, when I change the payroll in the first subform, and press the calculate button, a macro recalculates the premium. The total of all premiums is listed down in the second subform. The first subform shows the new calculated premium, but the bottom subform does not refresh the sum of all premiums. Thank you.