Copying values from a specified cell in Sheet1 into a specified range of the selected sheets

Question

I would like to paste values from a range of cells in sheet1 into a specific range set in previously selected/activated sheets. Thus I want it to paste in let say only range B1 onwards but only for the sheets Sheet2, Sheet3 and NOT for Sheet4 since I have not selected it on my workbook.

Sub CopyFirstRow()
Dim Wb As Workbook
Dim Sht As Worksheet
Dim mySelectedSheets As Sheets

Wb.Sheets("Global").Range("B1", "Q39").Copy

Set mySelectedSheets = ActiveWindow.SelectedSheets


For Each Sht In mySelectedSheets
            ActiveSheet.Paste
Sht.Range("B1").PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
End With
End With

End Sub

Please explain what I am doing wrong, as I am trying to understand more and more VBA and specifically the SET, FOR, WITH functions.

Davesexcel Davesexcel · Accepted Answer · 2019-04-03T15:23:27

You dim'd the wb but never set the workbook.

You have two "End With"'s but have no starting "With"'s .

You have Activesheet.paste but have not selected a sheet

Sub Button1_Click()


    Dim Wb As Workbook
    Dim Sht As Worksheet
    Dim mySelectedSheets As Sheets

    Set Wb = ThisWorkbook

    Wb.Sheets("Global").Range("B1", "Q39").Copy

    Set mySelectedSheets = ActiveWindow.SelectedSheets


    For Each Sht In mySelectedSheets
        Sht.Range("B1").PasteSpecial xlPasteValues
    Next

    Application.CutCopyMode = False


End Sub

Copying values from a specified cell in Sheet1 into a specified range of the selected sheets

1 Answers