Extract multiple cells from multiple sheets in a workbook to a master sheet in the said workbook

ssincerely

ssincerely

New Member
Joined
Aug 27, 2012
Messages
31
Good Day,
Here is a challenge for the masters of excel (EXCELITES). I have a workbook with multiple sheets, all are formatted the same way and require the said information (SEE BELOW).

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]I[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]NAME[/TD]
[TD]JOHN BROWN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OFFENCE[/TD]
[TD]LARCENY[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]DOB[/TD]
[TD]27-3-72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]OCCUPATION[/TD]
[TD]CARPENTER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]ADDRESS[/TD]
[TD]123 LOVE LANE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]TELEPHONE[/TD]
[TD]923-1723[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]DATE REPORTED[/TD]
[TD][/TD]
[TD][/TD]
[TD]LAST DATE[/TD]
[TD]NEXT DATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]31-3-18[/TD]
[TD][/TD]
[TD][/TD]
[TD]24-3-18 [/TD]
[TD]17-5-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The other cells are occupied or merge for other info etc.....


What I now want is a Mastersheet to generate with the information in RED ABOVE in rows (SEE BELOW)

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]DOB [/TD]
[TD]OCCUPATION[/TD]
[TD]ADDRESS[/TD]
[TD]TELEPHONE[/TD]
[TD]OFFENCE[/TD]
[TD]DATE REPORTED[/TD]
[TD]LAST DATE[/TD]
[TD]NEXT DATE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JOHN BROWN[/TD]
[TD]27-3-72[/TD]
[TD]CARPENTER[/TD]
[TD]123 LOVE LANE[/TD]
[TD]923-1723[/TD]
[TD]LARCENY[/TD]
[TD]31-3-18[/TD]
[TD]24-3-18[/TD]
[TD]17-5-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any assistance that can even lead me in the right direction....Respect and Jah Love
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Note Data are in Cells C2 to C6, B20, E20, F20 and I2 on the first diagram.

ssincerely said:
Good Day,
Here is a challenge for the masters of excel (EXCELITES). I have a workbook with multiple sheets, all are formatted the same way and require the said information (SEE BELOW).

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]I[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]NAME[/TD]
[TD]JOHN BROWN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OFFENCE[/TD]
[TD]LARCENY[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]DOB[/TD]
[TD]27-3-72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]OCCUPATION[/TD]
[TD]CARPENTER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]ADDRESS[/TD]
[TD]123 LOVE LANE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]TELEPHONE[/TD]
[TD]923-1723[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]DATE REPORTED[/TD]
[TD][/TD]
[TD][/TD]
[TD]LAST DATE[/TD]
[TD]NEXT DATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]31-3-18[/TD]
[TD][/TD]
[TD][/TD]
[TD]24-3-18 [/TD]
[TD]17-5-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The other cells are occupied or merge for other info etc.....


What I now want is a Mastersheet to generate with the information in RED ABOVE in rows (SEE BELOW)

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]DOB [/TD]
[TD]OCCUPATION[/TD]
[TD]ADDRESS[/TD]
[TD]TELEPHONE[/TD]
[TD]OFFENCE[/TD]
[TD]DATE REPORTED[/TD]
[TD]LAST DATE[/TD]
[TD]NEXT DATE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JOHN BROWN[/TD]
[TD]27-3-72[/TD]
[TD]CARPENTER[/TD]
[TD]123 LOVE LANE[/TD]
[TD]923-1723[/TD]
[TD]LARCENY[/TD]
[TD]31-3-18[/TD]
[TD]24-3-18[/TD]
[TD]17-5-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any assistance that can even lead me in the right direction....Respect and Jah Love
Click to expand...
 
Upvote 0
Hello SSincerely,

Try the following code in a standard module and assigned to a button:-

Code: 
Option Explicit
Sub NotSure()

        Dim ws As Worksheet, sh As Worksheet
        Dim x As Long
        Dim cAr As Variant, pAr As Variant

        Set sh = Sheets("Master")
     
Application.ScreenUpdating = False
Application.DisplayAlerts = False

cAr = Array("C2", "C3", "C4", "C5", "C6", "B20", "E20", "F20", "I2")
pAr = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")

For Each ws In Worksheets
If ws.Name <> "Master" Then
For x = LBound(cAr) To UBound(cAr)
        ws.Range(cAr(x)).Copy
        sh.Range(pAr(x) & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        sh.Columns.AutoFit
        Next x
End If
Next ws

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0
Thanks for your response, something happened yes but it seems the special pasting didn't start in column A as also I wanted to eliminate about 3 sheets namely "Links", "Home Page", "personnel"

vcoolio said:
Hello SSincerely,

Try the following code in a standard module and assigned to a button:-

Code: 
Option Explicit
Sub NotSure()

        Dim ws As Worksheet, sh As Worksheet
        Dim x As Long
        Dim cAr As Variant, pAr As Variant

        Set sh = Sheets("Master")
     
Application.ScreenUpdating = False
Application.DisplayAlerts = False

cAr = Array("C2", "C3", "C4", "C5", "C6", "B20", "E20", "F20", "I2")
pAr = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")

For Each ws In Worksheets
If ws.Name <> "Master" Then
For x = LBound(cAr) To UBound(cAr)
        ws.Range(cAr(x)).Copy
        sh.Range(pAr(x) & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        sh.Columns.AutoFit
        Next x
End If
Next ws

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.
Click to expand...
 
Upvote 0
Hello Ssincerely,
........as also I wanted to eliminate about 3 sheets namely "Links", "Home Page", "personnel"
Click to expand...

This should have been made crystal clear in your opening post. You cannot assume that we explicitly know your thoughts.

Following is the code again with a couple of minor alterations to the arrays and the excluded worksheets:-
Code: 
Option Explicit
Sub NotSure()

        Dim ws As Worksheet, sh As Worksheet
        Dim x As Long
        Dim cAr As Variant, pAr As Variant

        Set sh = Sheets("Master")
     
Application.ScreenUpdating = False
Application.DisplayAlerts = False

cAr = Array("C2", "C3", "C4", "C5", "C6", "I2", "B20", "E20", "F20")
pAr = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")

For Each ws In Worksheets
If ws.Name <> "Master" And ws.Name <> "Links" And ws.Name <> "Home Page" And ws.Name <> "Personnel" Then
For x = LBound(cAr) To UBound(cAr)
        ws.Range(cAr(x)).Copy
        sh.Range(pAr(x) & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        sh.Columns.AutoFit
        Next x
End If
Next ws

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Following is the link to a mock-up of what I believe your workbook to look like. You'll see that the code works just fine in doing its task:-

http://ge.tt/4BmM9Ep2

You will see that sheets "Master", "Links", "Home Page" and "Personnel" are excluded from the copy/paste and only data from sheets 2, 3 and 4 is transferred to the Master sheet. Click on the "RUN" button to see it work.

If you still experience any issues then please upload a sample of your workbook (an exact replica but with dummy data) to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here.

Cheerio,
vcoolio.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Macro to open multiple workbooks and copy a range of data based on todays date. Then paste the data in master workbook in one sheet under each other.
Replies
8
Views
643
Luke1690
L
G
  • Question Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
694
bobaol
B
B
  • Question Question
VBA to extract data from multiple folders and add to a master workbook plus identifying tags
Replies
2
Views
1K
John_w
J
O
  • Question Question
VBA Extracting Data from multiple workbooks, with varying sheet names
Replies
4
Views
2K
HaHoBe
HaHoBe
D
  • Solved
Merging huge amount of data from two different workbooks into Master book based on multiple condition VBA
2
Replies
17
Views
1K
Denin Srmic
D

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

Share this page

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