Copy data from multiple sheets to a master sheet

[Analyst Learning VBA]

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.

 

[silentwolf]

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

 

[Brombrough]

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

 

[Analyst Learning VBA]

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!

 

[silentwolf]

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!