PDA

View Full Version : [SOLVED] Move data from one sheet to another and export



rey06
02-16-2016, 06:40 AM
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
02-16-2016, 01:13 PM
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
02-16-2016, 02:09 PM
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
02-16-2016, 02:19 PM
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
02-17-2016, 08:16 AM
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
02-17-2016, 10:59 AM
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
02-17-2016, 12:10 PM
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
02-17-2016, 03:13 PM
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
02-17-2016, 03:41 PM
Sheets("DisReport").Copy
With ActiveSheet.Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.ActiveWorkbook.SaveAs.......

rey06
02-17-2016, 04:15 PM
Perfect. Thank you much!

jolivanes
02-17-2016, 09:07 PM
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
02-17-2016, 10:46 PM
Way-ull, I wasn't coding in 69, but I was using Boolean logic. I was involved in Digital Cryptography.