200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi!
There are two identical files A and B with protected and unprotected cells scattered here and there and the data in A needs to be pasted in B. For a regular cell range e.g. A5:A231, I have been using a code like:
Worksheets("Sheet1").Range("A5:A231").Formula="=[A.xlsm]Sheet1!A5"
but in the second case, the cells are scattered and following this code for each of the cells individually will complicate the process.
Could there be a help from the forum, please?
The code below should do what you want. Please try it out.
Sub TransferData()
' 077
' specify first and last cell here
Const FirstCell As String = "C4"
Const LastCell As String = "I8"
Dim WsS As Worksheet ' Source
Dim WsT As Worksheet ' Target
Dim Arr As Variant ' all Source data
Dim C As Long ' loop counter: column
Dim R As Long ' loop counter: row
' this sheet is a template, containing locked cell data only
Set WsT = Worksheets("Sheet2")
' this sheet has the data the user filled in already
Set WsS = Worksheets("Sheet1")
Application.ScreenUpdating = False ' saving time
With WsS
' take the date from A1 to the last used cell
' using an array should speed up the process
Arr = Range(.Range("A1"), .Range(LastCell)).Value
For R = Range(FirstCell).Row To Range(LastCell).Row
For C = Range(FirstCell).Column To Range(LastCell).Column
If .Cells(R, C).Locked = False Then
WsT.Cells(R, C).Value = Arr(R, C)
End If
Next C
Next R
End With
Application.ScreenUpdating = True
End Sub[CODE]Code_Goes_HereEdit 13 Aug 2020 ================================
Here is another version of the above code which allows selection of parts of the source data. You can list as many ranges as you like. In fact, you might list just all the unprotected cells one by one.
Sub TransferData()
' 077 13 Aug 2020
' specify the range or ranges as CSVs
Const Sources As String = "C4:D8,F7,H4:I8"
Dim WsS As Worksheet ' Source
Dim WsT As Worksheet ' Target
Dim Src As Range ' = Sources
Dim Sp() As String ' range names from 'Sources'
Dim Arr As Variant ' WsS data
Dim Cell As Range ' loop object: cells of Src
Dim i As Integer ' loop counter: ranges
' this sheet is a template, containing locked cell data only
Set WsT = Worksheets("Sheet2")
' this sheet has the data the user filled in already
Set WsS = Worksheets("Sheet1")
Application.ScreenUpdating = False ' saving time
With WsS
Arr = Range(.Range("A1"), .UsedRange.SpecialCells(xlCellTypeLastCell)).Value
Sp = Split(Sources, ",")
For i = 0 To UBound(Sp)
Set Src = .Range(Trim(Sp(i)))
For Each Cell In Src
With Cell
If Not .Locked Then
WsT.Cells(.Row, .Column).Value = Arr(.Row, .Column)
End If
End With
Next Cell
Next i
End With
Application.ScreenUpdating = True
End Sub
On small volumes of data - anything less than a couple of tenthousands of cells is considered small - the difference should be negligible unless your PC runs on a 286 chip. But if you have a slower computer or really large worksheets there is a good chance that you might save the time it took me to write this code within a couple of years, provided you use the procedure frequently :-).