Transferring data to 2 different sheets from one main data entry sheet

[cjarollins]

I have constructed a main data sheet where we can enter data for Return Good and Credit Memo. I'd like to have the info we enter to be transferred to the RGA sheet and the Credit Memo sheet. Then, I would like the sheet set up to where after we enter the data, we can "press" a macro button? and have both the RGA sheet and the Credit Memo sheet to save. Also, after hitting the button to create the two other sheets and save, if the Main Data Sheet can reset to empty and be ready for the next entry. The other issue would be if we have more than 1 item to enter (up to 9 items), how would all that info be transferred to the corresponding sheet? A little confusing but I can answer any questions you might have.
Thank you!

 

[mumps]

Can you attach a screen shot of what the main data sheet looks like? Look at Section B at this link for instructions on how to add a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[cjarollins]

https://www.dropbox.com/s/w7ku2p6gbda0wy3/RGA sheet.xlsm?dl=0
I would like the data entered in cells A2 thru R2 on the RGA-Credit Info sheet to be transferred to the other two sheets (RGA Return Sheet & Credit Memo Sheet). I have color coded the columns on the RGA-Credit Info sheet. Red dictates that the data in those columns will be on both the RGA Return Sheet & the Credit Memo Sheet. Green means that the info in those columns will only be on the RGA Return Sheet. Yellow means the data in these columns will only be on the Credit Memo Sheet. Once all the cells are filled with data on the RGA-Credit Info sheet, I'd like to have a way that I can click a macro button and have the RGA Return Sheet & the Credit Memo Sheet create a file that relates to the RGA# cells on the RGA-Credit Info sheet. Basically, create a file for the Return and the Credit with the RGA # being the file name. I will also need provisions if there is more than one item being return. We need the ability to enter up to at least 9 items, possibly more, for return on one single RGA Return Sheet and on one single Credit Memo Sheet. Once we have entered the data on the RGA-Credit Info sheet and the other two sheets have successfully been saved under the RGA #, can the data be cleared out of the cells on the RGA-Credit Info sheet or would we need to drop down to the next row to enter data for another return? I hope all this makes sense. I'll answer all questions to the best of my ability. Thank you!!

 

[cjarollins]

The last two columns should also be color-coded yellow.
Thank you!

 

[mumps]

Start by selecting the range C10:D19 in the RGA Return Sheet and un-merge the cells. You should avoid using merged cells as they create havoc for Excel macros. Then try this macro:

Sub CreateFiles()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook
    Set srcWB = ThisWorkbook
    Dim ciSH As Worksheet
    Set ciSH = srcWB.Sheets("RGA-Credit Info")
    Dim rSH As Worksheet
    Set rSH = srcWB.Sheets("RGA Return Sheet")
    Dim cmSH As Worksheet
    Set cmSH = srcWB.Sheets("Credit Memo Sheet")
    
    rSH.Range("A3") = ciSH.Range("A2")
    rSH.Range("C3") = ciSH.Range("B2")
    rSH.Range("D3") = ciSH.Range("C2")
    rSH.Range("C4") = ciSH.Range("D2")
    rSH.Range("C5") = ciSH.Range("F2")
    rSH.Range("C6") = ciSH.Range("G2")
    rSH.Range("C7") = ciSH.Range("H2")
    rSH.Range("C19") = ciSH.Range("N2")
    rSH.Range("E19") = ciSH.Range("O2")
    rSH.Range("D24") = ciSH.Range("N2")
    rSH.Range("A25") = ciSH.Range("E2")
    rSH.Range("B25") = ciSH.Range("D2")
    rSH.Range("D25") = ciSH.Range("G2")
    
    cmSH.Range("B2") = ciSH.Range("N2")
    cmSH.Range("B3") = ciSH.Range("Q2")
    cmSH.Range("B4") = ciSH.Range("O2")
    cmSH.Range("A8") = ciSH.Range("A2")
    cmSH.Range("E8") = ciSH.Range("B2")
    cmSH.Range("B21") = ciSH.Range("M2")
    cmSH.Range("C22") = ciSH.Range("D2")
    cmSH.Range("E22") = ciSH.Range("E2")
    cmSH.Range("C23") = ciSH.Range("G2")
    cmSH.Range("E26") = ciSH.Range("R2")
    
    ciSH.Range("I2:K" & Range("I" & Rows.Count).End(xlUp).Row).Copy
    rSH.Range("A10").PasteSpecial xlPasteValues
    ciSH.Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row).Copy
    rSH.Range("E10").PasteSpecial xlPasteValues
    ciSH.Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row).Copy
    cmSH.Range("A10").PasteSpecial xlPasteValues
    ciSH.Range("P2:P" & Range("P" & Rows.Count).End(xlUp).Row).Copy
    cmSH.Range("B10").PasteSpecial xlPasteValues
    ciSH.Range("J2:L" & Range("L" & Rows.Count).End(xlUp).Row).Copy
    cmSH.Range("C10").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Workbooks.Add (xlWBATWorksheet)
    srcWB.Sheets("RGA Return Sheet").Copy after:=Sheets(Sheets.Count)
    srcWB.Sheets("Credit Memo Sheet").Copy after:=Sheets(Sheets.Count)
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    ActiveWorkbook.SaveAs Filename:=srcWB.Path & "\" & Sheets("RGA Return Sheet").Range("C3").Value & ".xlsx", FileFormat:=51
    ActiveWorkbook.Close False
    ciSH.UsedRange.Offset(1, 0).ClearContents
    
    Application.ScreenUpdating = True
End Sub

It will save the new file to the same folder as where the main workbook is saved.

 

[cjarollins]

Where do I enter this macro code? No experience with macros.
Thank you

 

[mumps]

Do the following: Start by selecting the range C10:D19 in the RGA Return Sheet and un-merge the cells. Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. Check the folder where the main workbook is saved for the newly-saved file containing the RGA sheet and the Credit Memo sheet .There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

 

[cjarollins]

I completed all the above steps. This does not seem to be what I am looking for completely. At what point does the RGA Return Sheet and the Credit sheet get created? For more than 1 item being returned, what is the procedure then? As far as I can tell no other sheet was created or saved. Where would these be saved? What I am looking for is once all the data is completed on the RGA-Credit info sheet, I'd like the Return sheet and the Credit sheet to be created and saved automatically once all the info is entered. How do I move on to the next return/credit? More help please

 

[mumps]

I tested the macro on the file that you posted and it worked exactly as you requested. The macro works as follows:
-you fill in the data in the RGA-Credit Info sheet, multiple items if needed (as you said: up to 9 items)
-after entering all the data in the RGA-Credit Info sheet, you run the macro
-when you run the macro, all the necessary data is transferred to the RGA Return Sheet and the Credit sheet
-the two sheets are saved in a separate file named after the RGA number in the same folder where you saved the main data workbook as a macro-enabled file
-the data is cleared out of the cells on the RGA-Credit Info sheet ready for the next entry
How is the macro working or not working for you? Please describe in detail.
On another note, as well as clearing the RGA-Credit Info sheet, you would probably want to clear the RGA Return Sheet and the Credit sheet after they are saved. If you want the macro to do this, please let me know and I will modify it.

 

[cjarollins]

How do I run the macro?