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

Crughp

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,424
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,424
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Yes this is correct.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,424
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,424
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
I have data in all of the fields of all of the sheets. However there is nothing populating in the "Master" sheet.
 

Crughp

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,693
Members
416,127
Latest member
MALEPINZON

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
Top