kewiopex
11-02-2016, 04:24 PM
While running a macro to perform a loop to copy and paste from 1 worksheet in a workbook to another , an error occurs. When I rename the actual file worksheet names from Unit 1 to Unit1, the macro progresses.
Is there a good way to do a VBA code to handle the space in the actual file worksheet?
Sub TestWB()
Dim sourceWB As Workbook
Dim i As Long
Set sourceWB = Workbooks("TestBP.xls")
For i = 1 To Worksheets.Count
sourceWB.Worksheets("Unit" & i).Range("B2").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B2").PasteSpecialxlPasteValues
sourceWB.Worksheets("Unit" & i).Range("B7").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B7").PasteSpecialxlPasteValues
Next i
End Sub
Is there a good way to do a VBA code to handle the space in the actual file worksheet?
Sub TestWB()
Dim sourceWB As Workbook
Dim i As Long
Set sourceWB = Workbooks("TestBP.xls")
For i = 1 To Worksheets.Count
sourceWB.Worksheets("Unit" & i).Range("B2").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B2").PasteSpecialxlPasteValues
sourceWB.Worksheets("Unit" & i).Range("B7").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B7").PasteSpecialxlPasteValues
Next i
End Sub