Thread: Link multiple sheets in VBA for paste macro

HI all

I have a very basic knowledge of VBA and am trying to turn my 6 hour manual entry horror into something more streamlined i have the first part of my command which is to copy data from a selected range and to paste it into a specified range
Example

Code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet2").Select
    Range("B3:AK3").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Dim SelRange As Range
    ActiveSheet.Paste
End Sub

Now instead of the 4th line down where i Select Sheet1 i want to select multiple sheets.
Note: These sheets are not predetermined for example i do not want sheet 1,2,3, evrytime i may want sheet 1,2,3, then second time i run the macro i want sheet 1,2,4,

Now i know you can use a pop up a tick box window to do a similar thing with selecting multiple sheets to print but cannot work out how to apply it to my use of pasting.

Any ideas and if poss an explanation of what each line is doing so i can fully understand the code.

Cheers
Mike

Welcome to the Forum Bebbio89.

However, your post does not comply with the Forum Rules you agreed to follow. All VBA code must be wrapped in code tags. Please review the rules and amend your post to add the missing code tags, after which solutions will be suggested. Thanks.

Rule #3

Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button. For more information about these and other tags, click here.

Here is one approach which uses Marlett check boxes to select the sheets. The check box is ticked when the cell is selected and unticked when the cell is reselected. Column-B is used as the check box cells for as many rows in column-A as are filled with sheet names.

First, a list of all worksheets in the workbook is created in column-A when sheet1 is activated.

Code:

Option Explicit

Private Sub Worksheet_Activate()
    Dim i As Long
    
    'create a list of sheet names for the selection list

    For i = 1 To Sheets.Count
         Cells(i, 1) = Sheets(i).Name
    Next i
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim lrow As Long, wsName As String
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    wsName = Target.Offset(0, -1).Value
    
    If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Target, Range("B1:B" & lrow)) Is Nothing Then
            
            Target.Font.Name = "Marlett"
                If Target = vbNullString Then
                    Target = "a"
                Else
                    Target = vbNullString
                End If
        'prevent selection of the source sheet
            If wsName = Me.Name Then Target = vbNullString
        End If
End Sub

This code, below, is run manually and can be assigned to a button when ready to proceed with copying the ranges.
Note: You will need to adjust ranges in the code, but I suggest you insert a new column between A & B if you want to use the code as is. The source range to copy is already adjusted in the code (shifted 1-column to the right)

Code:

Option Explicit

Sub Copy_Cells()

    Dim wsName As String, c As Range, lrow As Long
    
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    If WorksheetFunction.CountIf(Range("B1:B" & lrow), "a") = 0 Then
        MsgBox "Sheet selections were not made - cancelling action", vbExclamation
        Exit Sub 'no selections made
    End If
    
    For Each c In Range("B2:B" & lrow)
        If c.Value = "a" Then
            wsName = c.Offset(0, -1).Value
            If wsName <> "Sheet1" Then
                Range("C3:AL3").Copy Worksheets(wsName).Range("A1")
            End If
        End If
    Next c

'Clear the selections after copying the values
    With Sheet1
        .Range("B1:B" & lrow).ClearContents
    End With
    Application.ScreenUpdating = True
    
End Sub