Copy and Paste Multiple [Closed]

Report
Posts
1
Registration date
Thursday January 28, 2016
Status
Member
Last seen
January 28, 2016
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hi

I'm looking for away to be able to copy a single cell from multiple sheets (tabs) for example cell a1 and list it's contents on a separate summary sheet.

Many Thanks

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello AndySpud,

Perhaps the following code will do it for you:-


Sub CopyAll()

Application.ScreenUpdating = False

       Dim ws As Worksheet

For Each ws In Worksheets
       If ws.Name <> "Summary" Then
       ws.Select
       Range("A1").Copy Sheets("Summary").Range("A" & Rows.Count).End(3)(2)
       End If
Next ws

Sheets("Summary").Select

Application.ScreenUpdating = True

End Sub


Following is a link to my test work book for you to peruse. Just run the code by pressing Alt + F8 and click on "run" in the window that opens.

https://www.dropbox.com/s/yuqmcpc38k3ntlk/AndySpud%28Cell%20A1%20values%20from%20multi%20sheets%20to%20Summary%29.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.