Help I need a hero! Advanced Macro open close copy paste etc.

[bemcbride]

I have a master commission sheet that has every salesperson with the invoices they get commissioned on. Each individual also has their own individual spreadsheet that has their information along with some personal targets and other information. In the master commission sheet, I have the individual sheets for each person listed.

I want to write a macro that opens this spreadsheet and copy and pastes (values only) onto the specific tab of the individual sheets. After that it should save and close the individual sheet.

Does anyone know if or how something like that could work or be done? I was thinking I could put the file name location in the Workbook column and that might help the macro...I don't know.

Thank you all for any help or assistance you can provide or suggestions. Yall are awesome.

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your master sheet and one of the individual sheets. Alternately, you could upload a copy of your master file and one of the individual files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[bemcbride]

I have a master commission sheet that has every salesperson with the invoices they get commissioned on. Each individual also has their own individual spreadsheet that has their information along with some personal targets and other information. In the master commission sheet, I have the individual sheets for each person listed.

I want to write a macro that opens this spreadsheet and copy and pastes (values only) onto the specific tab of the individual sheets. After that it should save and close the individual sheet.

Does anyone know if or how something like that could work or be done? I was thinking I could put the file name location in the Workbook column and that might help the macro...I don't know.

Thank you all for any help or assistance you can provide or suggestions. Yall are awesome.

 

[mumps]

I see your original message but not a response to my request in Post #2.

 

[bemcbride]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your master sheet and one of the individual sheets. Alternately, you could upload a copy of your master file and one of the individual files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Alright so below are the links to view the shared excel document samples that I have.

Commision Macro Sample.xlsm

Shared with Dropbox

www.dropbox.com

Aaron Kravetz - 63 Sales Comp Plan.xlsx

Shared with Dropbox

www.dropbox.com

Or download both

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

I want the Macro to go through the Commission Macro Sample sheet (Master) and open up each file location (one per individual) and copy and paste that persons respective data from the Data tab in Commission Macro Sample sheet over to the Data tab in their respective individual sheet. So for example: It would open Aaron's excel sheet with the provided location. Then copy and paste ONLY Aaron's information on the Master Data tab to Aarons individual Data tab. Pivot table would update and save and close. And then it would loop to the next person and so on and so on.

Let me know if that makes sense? I really hope this is possible because this is taking us weeks to do....maybe there is another way that we haven't thought of I don't know.

 

[bemcbride]

I see your original message but not a response to my request in Post #2.

Sorry butterfingers! Just posted.

 

[mumps]

Do you want the copied data pasted at the end of any existing data in each individual sheet? Also, the first row on each Data sheet is blank. Is that the way you want it?

 

[bemcbride]

Do you want the copied data pasted at the end of any existing data in each individual sheet? Also, the first row on each Data sheet is blank. Is that the way you want it?

I want it to copy over all existing data because the data in the master file has all year data...we will update the master with the new monthly data. If it copies underneath existing data it will create duplicated of previous information you know what i mean.

The first row does not need to be blank I just left it blank so I can add buttons on the top row. If it's not possible to leave it that's fine I can move the buttons elsewhere.

 

[mumps]

Try:

Sub ExportData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWB As Workbook, v As Variant, i As Long, dic As Object
    Set srcWS = ThisWorkbook.Sheets("Data")
    v = srcWS.Range("E2", srcWS.Range("E" & Rows.Count).End(xlUp)).Resize(, 13).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.Exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            With srcWS
                .Cells(1).AutoFilter 5, v(i, 1)
                Set desWB = Workbooks.Open(v(i, 13))
                Sheets("Data").UsedRange.Delete
                .AutoFilter.Range.Copy Sheets("Data").Range("A1")
                With Sheets("Data")
                    .Columns.AutoFit
                    .Range("A1").AutoFilter
                End With
                ActiveWorkbook.Close True
            End With
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Before running the macro, delete the first row in both files and save them and make sure that you have backup copies of your individual files just in case. If you widen the first row, you can place your buttons in the widened space above the headers.

 

[bemcbride]

Try:

Sub ExportData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWB As Workbook, v As Variant, i As Long, dic As Object
    Set srcWS = ThisWorkbook.Sheets("Data")
    v = srcWS.Range("E2", srcWS.Range("E" & Rows.Count).End(xlUp)).Resize(, 13).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.Exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            With srcWS
                .Cells(1).AutoFilter 5, v(i, 1)
                Set desWB = Workbooks.Open(v(i, 13))
                Sheets("Data").UsedRange.Delete
                .AutoFilter.Range.Copy Sheets("Data").Range("A1")
                With Sheets("Data")
                    .Columns.AutoFit
                    .Range("A1").AutoFilter
                End With
                ActiveWorkbook.Close True
            End With
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Before running the macro, delete the first row in both files and save them and make sure that you have backup copies of your individual files just in case. If you widen the first row, you can place your buttons in the widened space above the headers.

Ahh it's so close! It's just pasting everyone's data on the individual sheets instead of just that persons data.