Paste from clipboard

[fredsunbay]

Good day,

I've got a workbook with 7 identical sheets of date and wrote the below to copy a certain selection from any of the sheets when active.

Sub Copy_Range_To_Clipboard()
ActiveSheet.Range("P12:DJ199").Copy
End Sub

I'm looking for a code to paste the selected data on any of the other 6 sheets in the same location, regardless of the sheet selected.
Meaning - run the above code, select the sheet, than run a macro which will paste the copied data in the active sheet.

Most of the codes I've found will paste the data on one specified sheet only.

Any suggestions?

 

[Akuini]

Hi, @fredsunbay
Are you saying the range to copy is fixed, i.e. "P12:DJ199"
Try:

Sub Copy_Range_To_Clipboard_2()
Dim c As Range, va

va = ActiveSheet.Range("P12:DJ199").Value
On Error GoTo skip:

Set c = Application.InputBox("Select any cell in another sheet to paste into:", Title, Type:=8)
    c.Parent.Activate
    Range("P12:DJ199") = va
   
Exit Sub
skip:
    If Err.Number <> 424 Then
        MsgBox "Error found: " & Err.Description
    End If
End Sub

how it works;

• the code will load Range("P12:DJ199").Value in the active sheet to variable va
• an input box will appear asking you to select any cell in another sheet. You can select any cell but the values always be sent to Range("P12:DJ199").

 

[fredsunbay]

Hi Akuini,

The above code works well, is there a way to have the 7 different tabs as a drop down from the input box ?

Meaning - the user would select the tab where he wishes to paste the data from the drop down box.

 

[Akuini]

Hi Akuini,

The above code works well, is there a way to have the 7 different tabs as a drop down from the input box ?

Meaning - the user would select the tab where he wishes to paste the data from the drop down box.

You will need a userform with a combobox. Let me know if you're interested.

 

[fredsunbay]

Would love it

 

[Akuini]

Would love it

Try this:
Put a combobox1 & a commandbutton1 on userform.
The code:
In Userform:

Private Sub UserForm_Initialize()
'put the 7 sheet names separated by "\"
For Each x In Split("Sheet1\Sheet2\Sheet3\Sheet4\Sheet5\Sheet6\Sheet7", "\") 'change the sheets name to suit
    If LCase(x) <> LCase(ActiveSheet.Name) Then 'the activesheet doesn't have to be on the list
        ComboBox1.AddItem x
    End If
Next
End Sub

Private Sub CommandButton1_Click()
With ComboBox1
    If .ListIndex <> -1 Then
        Sheets(ComboBox1.Text).Range("P12:DJ199").Value = ActiveSheet.Range("P12:DJ199").Value
    End If
End With
End Sub

in a code module, e.g. module1 (run this sub to show the userform):

Sub show_Userform()
    UserForm1.Show
End Sub

 

[fredsunbay]

Dear Akuini,

This is absolutely perfect !! Thank you so much for helping me out . . .

Fred

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.