Transpose data from multiple worksheets to a single column on another worksheet

[Crughp]

Good Afternoon,

I am trying to take data off of multiple worksheets and then put it into a single column on another worksheet in the same workbook. I can use the transpose function to move a column from a single worksheet to another worksheet, but I need to do that from 58 sheets on data that can change from time to time. I would like to automate the process so as data in 1 sheet is updated it will update the master list as well. I have been unable to figure this out as of yet.

 

[My Aswer Is This]

It's always best to provide specific details.
You mention:
1. multiple worksheets but do not give the name of those worksheets
2. and then put it into a single column on another worksheet. But do not say what column or what worksheet.
3. but I need to do that from 58 sheets but do not say what the names of those sheets are or say from worksheet 2 to last worksheet

Now if you want this to work automatically we would need to know when you do what will activate this automatically. Like when I double click on any cell in column A or enter some text in column A

I would like to help but I need a lot of specific details.

 

[Crughp]

Okay. The worksheet name that I am trying to compile to is called Master List. The column is A row 1 down. The first 4 worksheets are for other lookup values that I have already configured. So Worksheet A is actually the 5th worksheet. So I would need it to do Worksheet 5 to the last worksheet as far as gathering the data.

The worksheets I am trying to compile from are:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

BB

CC

DD

EE

FF

GG

HH

II

JJ

KK

LL

MM

NN

OO

PP

QQ

RR

SS

TT

UU

VV

WW

XX

YY

ZZ

AB

AC

AD

AE

 

[My Aswer Is This]

You said:
I would like to automate the process so as data in 1 sheet is updated it will update the master list as well. I have been unable to figure this out as of yet.

So if I understand if any cell in column A sheets 5 to last sheet in workbook changes you want reflected in column A of sheet named "Master" is this correct?

so if I enter "Alpha" in Range("A3") of sheet 5 you want "Alpha" entered in column A of sheet named master. Is this correct?
So we just enter "Alpha" into the first empty cell in column A of sheet named "Master"
Is this correct.
If not please explain more.

 

[Crughp]

Yes this is correct.

 

[My Aswer Is This]

You need to put this script in all the sheets except for sheet named Master.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/24/2020  11:10:38 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 1 Then
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Master").Cells(Lastrow, 1).Value = Target.Value
End If
End Sub

 

[Crughp]

Do I need to do anything for the macro to run?

 

[My Aswer Is This]

Do I need to do anything for the macro to run?

Yes you need to enter some value in column A of the sheets where you entered the script

I asked the question earlier:
So if I understand if any cell in column A sheets 5 to last sheet in workbook changes you want reflected in column A of sheet named "Master" is this correct? This means a manual change not the result of some formula
And you said correct

 

[Crughp]

I have data in all of the fields of all of the sheets. However there is nothing populating in the "Master" sheet.

 

[Crughp]

Also looking at the Macro, the only sheet name I am seeing listed is the "Master" sheet. I am assuming that is because the macro is imbedded in all of the other sheets. Is that correct? I am trying to understand the how this works as well so I can maybe modify to use it for something else in the future.