I have a little issue if you could help I copy and paste from one workbook to another with the code below which works fine as long as both workbooks are in the same folder and both open, what I would like to do is have the PriceSheet workbook in another folder and be able paste its data in to the closed DataBase workbook which is password protected so I would need it to unprotect then protect again.
Sub Clear_Existing_Data_Before_Paste()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Workbooks("PriceSheet.xls").Worksheets("ALL")
Set wsDest = Workbooks("DataBase.xls").Worksheets("ALL")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Clear contents of existing data range
wsDest.Range("A2:S" & lDestLastRow).ClearContents
'4. Copy & Paste Data
wsCopy.Range("A2:S" & lCopyLastRow).Copy _
wsDest.Range("A2")
End Sub
Regards
Sub Clear_Existing_Data_Before_Paste()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Workbooks("PriceSheet.xls").Worksheets("ALL")
Set wsDest = Workbooks("DataBase.xls").Worksheets("ALL")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Clear contents of existing data range
wsDest.Range("A2:S" & lDestLastRow).ClearContents
'4. Copy & Paste Data
wsCopy.Range("A2:S" & lCopyLastRow).Copy _
wsDest.Range("A2")
End Sub
Regards