Macro Subcript Out of Range Error 9 Due to Worksheet Names have a Space

[kewiopex]

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

[Paul_Hossler]

What happens if you make it "Unit " & I instead of "Unit" & I ?






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

[kewiopex]

Unfortunately, it does not work. It only works if there are no spaces in the name of the worksheets.

[snb]

Please, use code tags !
Spaces in sheetnames are irrelevant.
Please, use F1 in the VBEditor: lemma Pastespecial.


Sub M_snb()
For each it Workbooks("TestBP.xls").sheets
ThisWorkbook.sheets(it.name).cells(2,2)=it.cells(2,2).value
ThisWorkbook.sheets(it.name).cells(7,2)=it.cells(7,2).value
Next
End Sub

[kewiopex]

Sorry about not including the tags! This will work for sure and I thank you. I have another application for the code but now I need to include ranges and not just a cell. Same problem with the sheet names having spaces. How can I adopt the above code with the range function?

[snb]

A range = cells, a cell = a Range.

[kewiopex]

ok then...i will adapt the cells accordingly and give it a try. Appreciate all this great support.