I have a cse equation in a worksheet. I am trying to paste the equation to
multiple workbooks in a folder. My problem is when I do this since I can't
hit cntrl-shift-enter it returns an error. Is there a way in vba when it says
to paste as a formula for it to know to paste it as a cse formula? Here is
the complete code. Thanks
Sub Macro8()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Unprotectworksheet As Worksheet
SaveDriveDir = CurDir
MyPath = "D:\Profiles\cherring\My Documents\AllStates\New Folder"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
Do While FNames <> ""
If FNames <> basebook.Name Then
Set mybook = Workbooks.Open(FNames)
Set sourceRange =
basebook.Worksheets("NetWeatherResidualLookup").Range("A1:B10000")
SourceRcount = sourceRange.Rows.Count
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cells(rnum, "A")
With sourceRange
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cells(rnum, "A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Formula = sourceRange.Formula
Application.DisplayAlerts = False
mybook.Close ([True])
rnum = rnum
End If
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
|
Similar Threads
