Move data from one sheet to another and export

[rey06]

Hello -

I am trying to create a macro that will essentially move data from one tab to another tab (which creates a report on yet another tab) and export those results.

I get a file that lists products that were purchased by vendor, and there are multiple vendors per file. I have the macro that will split that file out by vendor, each to its own sheet, but I am curious if there is a way to move the information each sheet one by one to the tab (we'll call it "input") that creates the report (on the "report" tab) and export each "report" one by one.

I kind of have an idea, but I can't get it down to where it'll begin to work.

[SamT]

s a way to move the information each sheet

Did you mean "Move" or did you mean "Copy?"

Why have the intermediate step of Vendor Sheets? Why not just work with the "Purchase List" sheet?

[rey06]

I definitely meant "copy." I need to make two different reports from the individual sheets. After the vendors are split onto individual tabs, a copy of each is saved and manually paste them one by one into another spreadsheet that makes this specific report. Basically, the get copied into one sheet and another sheet reorganizes the file as it needs to be for this particular report. But if there is a way to just add that step to the master spreadsheet I have now, it'd work pretty slick.

Basically, I think I just need to add a step after all of the vendors are on their own sheets to copy and paste all of those (one by one) into my "input" sheet so the "report" sheet is populated, and export each result.

Maybe I'm making it more complicated than it needs to be and maybe there is a better way. I'm open to any expertise you may offer

[SamT]

Use the Go Advanced button and use the PaperClip Icon to attach the books so we can see what there is and examples of what you want.

[rey06]

Hi, I think after some playing I was able to get it figured out for the most part. However, I need help with a part of it. Where I have "AR2", in my save as line, I want that to be the value of whatever is in cell "AR2". For instance, if cell AR2 is Rachel, the file name would be date_Rachel_Discrepancy Report. Can you help with that?

Sub test()
Dim Wb As Workbook
Dim wks As Worksheet
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
datebox = InputBox("DISCREPANCY REPORTS: Please enter the current date")
For Each xWs In ThisWorkbook.Sheets
If xWs.Name <> "MACROS" And xWs.Name <> "Flat File" And xWs.Name <> "DisInput" And xWs.Name <> "DisReport" Then
Cells.Select
    Selection.Copy
    Sheets("DisInput").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("DisReport").Select
    Application.CutCopyMode = False
    Sheets("DisReport").Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & datebox & "_" & "AR2" & "_Discrepancy Report" & ".xlsx"
                        Application.ActiveWorkbook.Close False
End If
Next
Application.DisplayAlerts = True
            Application.ScreenUpdating = True
    Windows("FF MACRO BOOK.xlsm").Activate
End Sub

[SamT]

That is actually pretty darn good coding. I cleaned it up and added a few flourishes. I prefer to use the year-Month-Day format when naming files because it keeps them in true chronological order. I also generally use 2 digit years since they will be good for the next 100 years, at which time I suspect it will be someone else's problem. IF you put the Date after "Discrepancy Report," the files will be in "AR2" name order.

I did have to make an assumption about which sheet to pull AR2 from.

Option Explicit

Sub test()
    Dim Wb As Workbook
    Dim xWs As Worksheet
Dim DateBox As String
'Dim xDate As String
Dim xPath As String
    xPath = ThisWorkbook.Path
    
    datebox = InputBox("DISCREPANCY REPORTS: Please enter the current date")
    'IF it should always be the day's Date, use
    'xDate = Format(Date, "yy-mm-dd") 'to replace DateBox and Formatting in code below
    
    'Uncomment below after testing
    'Application.ScreenUpdating = False
    'Application.DisplayAlerts = False

    For Each xWs In ThisWorkbook.Sheets
        If xWs.Name <> "MACROS" And xWs.Name <> "Flat File" And xWs.Name <> "DisInput" And xWs.Name <> "DisReport" Then
            xWs.Cells.Copy Sheets("DisInput").Range("A1")
            Application.Calculate
            Sheets("DisReport").Copy
            Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" _
              & Format(CDate(datebox), "yyyy-mm-dd") & "_" _
              & Sheets("DisReport").Range("AR2") _
              & "_Discrepancy Report" & ".xlsx"
            Application.ActiveWorkbook.Close False
        End If
    Next

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Windows("FF MACRO BOOK.xlsm").Activate
'ThisWorkbook.Activate
End Sub

[rey06]

This is naming the files correctly, but creating the same report for each file. For instance, if I have 12 vendors, I'll have 12 correctly named files, but each one contains the same report. I can dummy up the data and send to you if that'd help. I'd rather not just post it.

[rey06]

I actually know exactly what's wrong. When it's creating these files, it's copying the formulas, not the values. So, in the end I get whatever was created last. Is there a way to do exactly this, but somehow (for lack of better terminology) copy and paste values for each one before exporting?

[SamT]

Sheets("DisReport").Copy 
With  ActiveSheet.Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With 
Application.ActiveWorkbook.SaveAs.......

[rey06]

Perfect. Thank you much!

[jolivanes]

I suspect it will be someone else's problem

When you mentioned in another post that you started coding in 1969, I would say that this quote would be accurate!!!!!

[SamT]

Way-ull, I wasn't coding in 69, but I was using Boolean logic. I was involved in Digital Cryptography.