Copy and SaveAs from Multiple Workbooks

[elbarauljr]

Hi,

I am new to this forum, and very exited to be part of it. I would appreciate to get help with a macro coding that allows me to do the following actions:

1. I already have open: "Raw Expenses", "Raw Encumbrances", "Raw Funding" and "Raw Tasks" .csv files that I export from a Web base program.
2. The need to open "Master Data" workbook to paste from each .csv file above the range (A1:J1200) to a specific sheet ("Expenses", "Encumbrances", "Funding" and "Tasks") starting in cell D1. Input today date on sheet 1 A1, then save/close the "Master Data" workbook.
3. Finally for backup purpose, I need to SaveAs each .csv file as .xlsx with name "yyyy mm dd Expenses", "yyyy mm dd Encumbrances", "yyyy mm dd Funding" and "yyyy mm dd Tasks" under same drive location.

Thank you in advance.

 

[JLGWhiz]

This is untested: Since no other information was provided, it was assumed that all workbooks, including the one which hosts the code will be in the same directory. If not the code may need adjustment.

Sub t()
Dim wb As Workbook, shEx As Worksheet, shEn As Worksheet, shF As Worksheet, shT As Worksheet
Dim rng As Range, sary As Variant, dary As Variant, i As Long, fPath As String
fPath = ThisWorkbook.Path & "\"
Set shEx = Workbooks("Raw Expenses.csv").Sheets(1)
Set shEn = Workbooks("Raw Encumbrances.csv").Sheets(1)
Set shF = Workbooks("Raw Funding.csv").Sheets(1)
Set shT = Workbooks("Raw Tasks.csv").Sheets(1)
Set wb = Workbooks.Open(fPath & "Master Data.xlsx") 'Validate file extension
sary = Array(shEx, shEn, shF, shT)
dary = Array("Expenses", "Encumbrances", "Funding", "Tasks")
    For i = LBound(sary) To UBound(sary)
        With sary(i)
            .Range("A1:J1200").Copy wb.Sheets(dary(i)).Range("D1")
        End With
    Next
Workbooks("Raw Expenses.csv").SaveAs fPath & Format(Date, "yyyy mm dd") & " Expenses.xlsx", FileFormat:=51
Workbooks("Raw Encumbrances.csv").SaveAs fPath & Format(Date, "yyyy mm dd") & " Encumbrances.xlsx", FileFormat:=51
Workbooks("Raw Funding.csv").SaveAs fPath & Format(Date, "yyyy mm dd") & " Funding.xlsx", FileFormat:=51
Workbooks("Raw Tasks.csv").SaveAs fPath & Format(Date, "yyyy mm dd") & " Tasks.xlsx", FileFormat:=51
wb.Sheets(1).Range("A1") = Date
wb.Close True
End Sub

 

[elbarauljr]

You are correct and I forgot to mention a couple of details. I tried your code and works, but only if the Master Data.xlsx is on the same folder as the file that hosts the code like you mentioned.

Here are the missing details of the process that I need:
1. The data from the .csv files need to be pasted as value to keep the formatting in the Master Data.
2. The Master Data file location is U:\Updates\Encumbrance Plan
3. The four new .xlsx files should be saved to U:\Updates\Backup (then close the files)

Thank you for your help!

 

[JLGWhiz]

Sub t2()
Dim wb As Workbook, shEx As Worksheet, shEn As Worksheet, shF As Worksheet, shT As Worksheet
Dim rng As Range, sary As Variant, dary As Variant, i As Long, mPath As String, sPath As String
mPath = "U:\Updates\Encumbrance Plan\"
sPath = "U:\Updates\Backup\"
Set shEx = Workbooks("Raw Expenses.csv").Sheets(1)
Set shEn = Workbooks("Raw Encumbrances.csv").Sheets(1)
Set shF = Workbooks("Raw Funding.csv").Sheets(1)
Set shT = Workbooks("Raw Tasks.csv").Sheets(1)
Set wb = Workbooks.Open(mPath & "Master Data.xlsx") 'Validate file extension
sary = Array(shEx, shEn, shF, shT)
dary = Array("Expenses", "Encumbrances", "Funding", "Tasks")
    For i = LBound(sary) To UBound(sary)
        With sary(i)
            .Range("A1:J1200").Copy wb.Sheets(dary(i)).Range("D1")
        End With
    Next
Workbooks("Raw Expenses.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Expenses.xlsx", FileFormat:=51
Workbooks("Raw Expenses.xlsx").Close False
Workbooks("Raw Encumbrances.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Encumbrances.xlsx", FileFormat:=51
Workbooks("Raw Encumbrances.xlsx").Close False
Workbooks("Raw Funding.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Funding.xlsx", FileFormat:=51
Workbooks("Raw Funding.xlsx").Close False
Workbooks("Raw Tasks.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Tasks.xlsx", FileFormat:=51
Workbooks("Raw Tasks.xlsx").Close False
wb.Sheets(1).Range("A1") = Date
wb.Close True
End Sub

 

[elbarauljr]

It works, except when copying from the .csv files and pasting to the "Master Data.xlsx" is replacing the format. Is there a way to add to the code to paste as values so it maintain the format in the "Master Data.xlsx" ?

Thank you.

 

[JLGWhiz]

I guess I didn't think about csv files having and special formatting since they are basically text files.

Sub t3()
Dim wb As Workbook, shEx As Worksheet, shEn As Worksheet, shF As Worksheet, shT As Worksheet
Dim rng As Range, sary As Variant, dary As Variant, i As Long, mPath As String, sPath As String
mPath = "U:\Updates\Encumbrance Plan\"
sPath = "U:\Updates\Backup\"
Set shEx = Workbooks("Raw Expenses.csv").Sheets(1)
Set shEn = Workbooks("Raw Encumbrances.csv").Sheets(1)
Set shF = Workbooks("Raw Funding.csv").Sheets(1)
Set shT = Workbooks("Raw Tasks.csv").Sheets(1)
Set wb = Workbooks.Open(mPath & "Master Data.xlsx") 'Validate file extension
sary = Array(shEx, shEn, shF, shT)
dary = Array("Expenses", "Encumbrances", "Funding", "Tasks")
    For i = LBound(sary) To UBound(sary)
        With sary(i)
            .Range("A1:J1200").Copy
            wb.Sheets(dary(i)).Range("D1").PasteSpecial xlPasteFormats
            wb.Sheets(dary(i)).Range("D1").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        End With
    Next
Workbooks("Raw Expenses.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Expenses.xlsx", FileFormat:=51
Workbooks("Raw Expenses.xlsx").Close False
Workbooks("Raw Encumbrances.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Encumbrances.xlsx", FileFormat:=51
Workbooks("Raw Encumbrances.xlsx").Close False
Workbooks("Raw Funding.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Funding.xlsx", FileFormat:=51
Workbooks("Raw Funding.xlsx").Close False
Workbooks("Raw Tasks.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Tasks.xlsx", FileFormat:=51
Workbooks("Raw Tasks.xlsx").Close False
wb.Sheets(1).Range("A1") = Date
wb.Close True
End Sub

 

[elbarauljr]

Added to the above Post:

It works, except when copying from the .csv files and pasting to the "Master Data.xlsx" is replacing the format. Is there a way to add to the code to paste as values so it maintain the format in the "Master Data.xlsx" ? Also, my mistake the range to copy from "Raw Funding.csv" is "A1:K1200" How can add to the code ?

Thank you.

 

[elbarauljr]

Sorry I did not see your last code, so please disregard the previous post. The only thing I missed is that the range to copy from "Raw Funding.csv" is "A1:K1200" How can add to the code ?

 

[JLGWhiz]

You are running out of "Oh, I forgot"s.

Sub t4()
Dim wb As Workbook, shEx As Worksheet, shEn As Worksheet, shF As Worksheet, shT As Worksheet
Dim rng As Range, sary As Variant, dary As Variant, i As Long, mPath As String, sPath As String
mPath = "U:\Updates\Encumbrance Plan\"
sPath = "U:\Updates\Backup\"
Set shEx = Workbooks("Raw Expenses.csv").Sheets(1)
Set shEn = Workbooks("Raw Encumbrances.csv").Sheets(1)
Set shF = Workbooks("Raw Funding.csv").Sheets(1)
Set shT = Workbooks("Raw Tasks.csv").Sheets(1)
Set wb = Workbooks.Open(mPath & "Master Data.xlsx") 'Validate file extension
sary = Array(shEx, shEn, shF, shT)
dary = Array("Expenses", "Encumbrances", "Funding", "Tasks")
    For i = LBound(sary) To UBound(sary)
        With sary(i)
            If i <> 2 Then
                .Range("A1:J1200").Copy
            Else
                .Range("A1:K1200").Copy
            End If
            wb.Sheets(dary(i)).Range("D1").PasteSpecial xlPasteFormats
            wb.Sheets(dary(i)).Range("D1").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        End With
    Next
Workbooks("Raw Expenses.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Expenses.xlsx", FileFormat:=51
Workbooks("Raw Expenses.xlsx").Close False
Workbooks("Raw Encumbrances.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Encumbrances.xlsx", FileFormat:=51
Workbooks("Raw Encumbrances.xlsx").Close False
Workbooks("Raw Funding.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Funding.xlsx", FileFormat:=51
Workbooks("Raw Funding.xlsx").Close False
Workbooks("Raw Tasks.csv").SaveAs sPath & Format(Date, "yyyy mm dd") & " Tasks.xlsx", FileFormat:=51
Workbooks("Raw Tasks.xlsx").Close False
wb.Sheets(1).Range("A1") = Date
wb.Close True
End Sub

 

[elbarauljr]

This works perfectly!! I appreciate your help and thank you for your patience.