Copy data from multiple sheets to a master sheet

Joined
Mar 20, 2019
Messages
2
Hi –
I am very new to VBA and I have been having trouble finding out how to do this. I appreciate anyone who can help me.

I have a workbook that contains a sheet named for each person on our sales team, a master sheet, and few various other sheets. The headers for the sales team sheets start on row 14. The sales team fills in information on their sheets, but the number of rows they fill in can vary. I would like to create a script to go to each person’s sheet and copy the information to the master sheet, that has the same headers on row 14.

So Essentially, I want it to go to Sales Person1’s sheet, copy all of the data starting on row 15, (number of rows can vary), and paste to the master sheet on the first blank row after row 14. Then it would need to go to Sales person 2’s sheet and do the same thing, ect.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
HI and welcome,
I guess many forget that this forum is not a give me a code forum it is to help each other when someone is stuck with something.
This question what you have have been disgussed in the forum many times over and over and the best thing is to have a look through the forum first and check if there are similar questions posted .
Once you got started with your code I am sure there are many more questions you can post and sure many will help.

HTH
 
Last edited:
Upvote 0
Hi Analyst Learning VBA,

Since you are new I hope the following should give you a start on VBA to get your job done.

Hope it helps and enjoy.


Sub getTotalSalesInfo()


Dim wb As Workbook
Dim ws As Worksheet
Dim wsMS As Worksheet 'set up for master sheet
Dim lngMSRow As Long 'current last row of master sheet
Dim lngwsRow As Long 'holds last row of current worksheet
Dim i As Long 'current line of current worksheet
Dim lngMSMaxCol As Long 'max column of master Sheet
' Loop through all of the worksheets in the active workbook.
Set wb = ThisWorkbook

Set wsMS = wb.Worksheets("Master Sheet")

lngMSRow = wsMS.Range("A1048576").End(xlUp).Row

If lngMSRow > 14 Then 'reset the master sheet
wsMS.Rows("15:1048576").Delete
End If
lngMSRow = wsMS.Range("A1048576").End(xlUp).Row 'should now = 14

'allow for sales person name in last column
lngMSMaxCol = wsMS.Range("XFD" & lngMSRow).End(xlToLeft).Column
wsMS.Cells(14, lngMSMaxCol + 1) = "Sales Person"

For Each ws In wb.Worksheets
Select Case ws.Name
Case "Master Sheet" 'we dont want info on master sheet
'do nothing
Case "Ignor Sheet", "Ignor Sheet2" ' these are the other sheets to be ignored.
'do nothing
Case Else 'if not any of the above it must be a sales person.

lngwsRow = ws.Range("A1048576").End(xlUp).Row
If lngwsRow > 14 Then
For i = 15 To lngwsRow
lngMSRow = lngMSRow + 1 'get next free line on Master Sheet
ws.Rows(i).EntireRow.Copy wsMS.Range("A" & lngMSRow)
wsMS.Cells(lngMSRow, lngMSMaxCol + 1) = ws.Name ' add sales person to last column

Next i
Else
'if here salesperson had no sales
End If
End Select
Next


Set wb = Nothing
Set ws = Nothing
Set wsMS = Nothing




End Sub
 
Last edited:
Upvote 0
Hi Brombough
Thank you so much! This is so great and helped me so much!

Sorry I am so new to posting in this forum and didn’t list what I had done so far. I had looked at a lot of threads in this site and others, but I was having trouble finding examples for workbooks that were set up like mine. Everything I tried before was breaking. This has helped!
 
Upvote 0
Hi Anaylist Learning,
all good just was menting it as there where a few similar examples here thats all.
Good that someone had the been able to help!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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