I have the following code from a form. It works just fine copying named ranges from one sheet to another. The probloem now is that I don't want to copy the characteristic of the formating or whether they are protected or not. The originating spreadsheets have cells that are protected. I am trying to copy those values over to the new sheet and paste their values or formulas but not their formatting (background color) or the fact that some of the cells are 'locked'. I want the new sheet to have the values and formulas but not the be lock for the user. Is this possible with the current way I do the code or do I have to rewrite it? How would you approach this?
Thanks,
Guy
PS: Happy Holidays to All! :catinthe:
Code
Private Sub CommandButton1_Click()
Import_Data_Form.Hide
Run "NPA"
Set b = Selection
ad = b.Address
' Local Variables
Dim wkbDataFile As Workbook
' Let user select source datafile
Call UserSelectFile_WOpen(wkbDataFile)
If wkbDataFile Is Nothing Then
MsgBox "User did not select a workbook to open"
Exit Sub
End If
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Application.DisplayAlerts = False
'Log Data
wkbDataFile.Worksheets("2009 Log").Range("Column_Type_Of_Ride").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Type_Of_Ride")(1,1)
wkbDataFile.Worksheets("2009 Log").Range("Column_Time_of_Day").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Time_of_Day")(1,1)
wkbDataFile.Worksheets("2009 Log").Range("Column_Route_Name").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Route_Name")(1,1)
On Error Resume Next
wkbDataFile.Worksheets("2009 Log").Range("Column_Ride_Style").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Ride_Style")(1,1)
On Error GoTo 0
Application.DisplayAlerts = True
wkbDataFile.Activate
Run "PA"
wkbDataFile.Worksheets("2009 Stats").Select
wb.Activate
Run "PA"
wb.Worksheets("2009 Stats").Select
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
Sub UserSelectFile_WOpen(wkbDataFile As Workbook)
' Local Variables
Dim strSelectedFile As String
' Let user select a workbook
strSelectedFile = Application.GetOpenFilename(FileFilter:="XLS Files (*.xls), *.xls", Title:="Select Desired Data File", MultiSelect:=False)
If strSelectedFile = "False" Then Exit Sub
Set wkbDataFile = Workbooks.Open(strSelectedFile)
End Sub
Private Sub CommandButton2_Click()
Import_Data_Form.Hide
End Sub
Display More