Transfer multible sheets into the existing sheet named "Results"

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
822
Hi all, i would like to create a VBA code so that to run through a workbook and transfer all Sheets' data (Sheet1, Sheet2 e.t.c.) into the first one, on the left which is named "Results" and it already exists. Therefore that The data of each Sheet, should be placed into "Results", below each other.
The Sheets are not stably and have no standard name and are more than 2 but no more than 50.
However, the meaning of code is update my results whenever i enter new data in any of spreadsheet.
Thanks to all in advance
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hello Panoos64,

Try the following code assigned to a button on the "Results" sheet:-


Code:
Sub Test()

Dim ws As Worksheet, sh As Worksheet
Set sh = Sheets("Results")

Application.ScreenUpdating = False

sh.UsedRange.Offset(1).Clear

For Each ws In Worksheets
       If ws.Name <> "Results" Then
       ws.UsedRange.Offset(1).Copy sh.Range("A" & Rows.Count).End(3)(2)
       End If
Next ws

Application.ScreenUpdating = True

End Sub

The code also clears the "Results" sheet prior to any new data being transferred. I'm assuming that you will be keeping all data in the source sheets.

I hope that this helps.

Cheerio,
vcoolio.
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
822
Thank you so much vcool. It works perfect and based to my data. Thanks also for your time spent for my project. Hv a great, lovely day!!
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
You're welcome Panoos.
I'm glad that I was able to help and thanks for the feed-back.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top