VBA to copy data from one worksheet and paste to multiple sheets in a different worksheet

[BTLbl]

Good morning,

This is probably a simple one.

I am trying to copy data in a specific cell in one worksheet, and then paste that data on multiple sheets in a new worksheet. I need to find the next blank row in the "pasting" sheets and paste the copied data there. Here is what I have so far:

Sub test3()Dim sht As Worksheet
Sheets("Sheet1").Range("e11:e12").Copy
Windows("Summary.xlsx").Activate
For Each sht In Worksheets
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial xlPasteValues, Transpose:=True
Next
End Sub

this is not switching sheets though, and is pasting the same value downwards in successive rows.

Any help would be appreciated.

 

[Tim_Excel_]

Hi, and welcome to MrExcel

First of all, please use Code Tags when pasting code.

Secondly, when you're referring to ranges, make sure you specify the Workbook and Worksheet. Also, activating and selecting cells/WBs/WS's is bad practice and should be avoided.

[COLOR=#574123]Sub test3()
Dim sht As Worksheet
[/COLOR]
[COLOR=#574123]Workbooks("NAME HERE").Sheets("Sheet1").Range("E11:E12").Copy 'EDIT[/COLOR]

With [COLOR=#574123]Workbooks("[/COLOR][COLOR=#574123]Summary.xlsx")[/COLOR]

[COLOR=#574123]For i = 1 To .S[/COLOR][COLOR=#574123]heets.Count
[/COLOR]
[COLOR=#574123]   .Sheets(i).Range("A" & Rows.Count).End(xlUp).Offset(1).[/COLOR][COLOR=#574123]PasteSpecial xlPasteValues, Transpose:=True[/COLOR]
[COLOR=#574123]
Next i

[/COLOR]End With
[COLOR=#574123]End Sub[/COLOR]

let me know if this works for you

 

[BTLbl]

thank you very much, this worked well. In the above example you provided, what if I wanted to paste the data in column A, but based on the next empty row in column B? Is that a major problem?

 

[BTLbl]

I think I've solved it. Edit range to B and offset(1,-1)

thanks again for your assistance. I will read up on how to use the code tags for any future posts.

 

[Tim_Excel_]

Glad to have helped!