Paste data in selected sheets - Loop through selected sheets

[nagovind]

Dear All,

Below is the code that paste the data in the selected sheets from clipboard
It is doing the job perfectly

But this is not working if the copied data contains an object instead it is pasting only in fist selected sheet and not in all sheets

Please refer to the attached Worksheet-1 without object, Worksheet-2 with object, please advise the code

Public Sub PASTEDATASELSHEET()
'PASTE DATA IN SELECTED SHEETS
Application.DisplayAlerts = False
Dim wks As Worksheet
For Each wks In ThisWorkbook.Windows(1).SelectedSheets
Range("B2").Select
ActiveSheet.Paste
Next wks
Application.DisplayAlerts = True
End Sub

 

[lohithsriram]

Hi Nagovind,

I am unable to figure out the issue you have. I tried the macro in each sheet and it works.

1) Do you mean grouping the sheets and then run the macro?
2) Do you want to run this macro on all the sheets in the work book? If so, you will have to change the line of code (for loop to the below).

 For Each wks In ThisWorkbook.Sheets

Try and let me know, if your requirement is different.

 

[nagovind]

Hi Lohithsriram

Thank you for your reply
My query is
1) Do you mean grouping the sheets and then run the macro? YES

Please refer to the attached VIDEO in the below Link

https://dl.dropboxusercontent.com/u/101570276/Video_Paste data issue.zip

Issue is, if the data is pasted in the selected group of sheets WITH OBJECT then this code is pasting data in to only one sheet - first sheet of a group

But without OBJECT the code is working well

Regards
Arul Govind

 

[lohithsriram]

Hi Arul Govind ,

Add the below line of code after the FOR statement. Activate the sheet before you paste the values. This should work fine.

wks.Activate

Your code should be

Public Sub PASTEDATASELSHEET()
'PASTE DATA IN SELECTED SHEETS
Application.DisplayAlerts = False
Dim wks As Worksheet
For Each wks In ThisWorkbook.Windows(1).SelectedSheets
wks.Activate
Range("B2").Select
ActiveSheet.Paste
Next wks
Application.DisplayAlerts = True
End Sub

 

[nagovind]

Hi Lohithsriram,

Code PERFECTLY works

Thanks a lot for your code

Sincerely
Govind