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

BTLbl

New Member
Joined
Dec 3, 2018
Messages
5
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.


Code:
[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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top