Worksheet Transfer

Question

I have a workbook where few cells in the last sheet of the workbook is protected and hence, I am unable to run my clean up macros

I am looking for a macro which helps to cut the text & values from the last sheet of the Protected source workbook starting from range "B6" to the last value appearing in any of the columns in that sheet and paste into a new worksheet and then paste the same values back in the same source workbook starting from range "B6". So that I can call a few clean up macros I have.I have tried this with some codes, but I'm getting issues.Can anyone please check and help me on this.

Answer 1

Hi Shakiru, I am receiving Run-time error "1004":Application -defined or object-defied error on the below line, not sure which cause the issue"NewWorkbook.Worksheets("Sheet1").Range("B6", Cells(LastRow, LastColumn)).Copy SourceSheet.Range("B6")"

Answer 2

Yes, please try this:

Sub CopyDataToNewWorkbook()

' Define the source worksheet Dim SourceSheet As Worksheet Set SourceSheet = ThisWorkbook.Worksheets("Sheet1")

' Unprotect the source worksheet SourceSheet.Unprotect

' Find the last row and column of data in the source worksheet Dim LastRow As Long Dim LastColumn As Long LastRow = SourceSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious). Row LastColumn = SourceSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious). Column

' Copy the data to a new workbook Dim NewWorkbook As Workbook Set NewWorkbook = Workbooks.Add SourceSheet.Range("B6", Cells(LastRow, LastColumn)). Copy NewWorkbook.Worksheets("Sheet1"). Range("B6")

' Run cleanup macros on the data in the new workbook ' ...

' Paste the data back to the source worksheet NewWorkbook.Worksheets("Sheet1"). Range("B6", Cells(LastRow, LastColumn)). Copy SourceSheet.Range("B6")

' Protect the source worksheet SourceSheet.Protect

' Close the new workbook without saving NewWorkbook.Close False

End Sub

This code will copy the data to a new workbook and paste it back to the source worksheet after running the cleanup macros and also copy rather than cut.

You can modify the cleanup macros as per your requirements.

Answer 3

Also, instead of cut the text, can copy paste it?

Answer 4

Hi Shakiru, Thank you for the immediate replay, 

As the template is protected, its not allowed to add a new sheet on the source workbook, hence, checking is that possible to create a new workbook and paste the data, instead adding a new sheet in the source workbook

Answer 5

Hi Nagul1!

Please try this code below:

Sub CutAndPaste() Dim wsSource As Worksheet Dim wsNew As Worksheet Dim lastRow As Long Dim lastCol As Long Dim pasteRange As Range

'Set the source worksheet to the last sheet in the workbook Set wsSource = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

'Find the last row and last column with data in the source worksheet lastRow = wsSource.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious). Row lastCol = wsSource.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious). Column

'Create a new worksheet for the cut and pasted data Set wsNew = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

'Cut and paste the data from the source worksheet to the new worksheet wsSource.Range("B6", wsSource.Cells(lastRow, lastCol)). Cut wsNew.Range("A1")

'Set the paste range in the source worksheet Set pasteRange = wsSource.Range("B6", wsSource.Cells(lastRow, lastCol))

'Unprotect the source worksheet to allow pasting wsSource.Unprotect

'Paste the data back into the source worksheet pasteRange.Value = wsNew.Range("A1", wsNew.Cells(lastRow - 5, lastCol)). Value

'Protect the source worksheet again wsSource.Protect

'Delete the new worksheet Application.DisplayAlerts = False wsNew.Delete Application.DisplayAlerts = True End Sub

This should create a new worksheet with the data you want to clean up, paste the cleaned data back into the original sheet, and delete the temporary worksheet.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru