VBA Help - Creating new files (.xlsx & .csv) from active workbook worksheets

[TatCE]

Hi,

I am a long-time programmer, but new to Excel VBA. I want to export to new files, some .xlsx & some .csv, etc., a selected sub-set of the contents of multiple worksheets in an open, active workbook. This is an INVENTORY UPLOAD situation.

Here are the particulars of what I am using:

1. Windows 10
2. Excel 2016

ISSUE DESCRIPTION
-----------------------

The ISSUE with the code cited below is that it does not write the copied data to the 5 vendor inventory files, which are left OPEN and are EMPTY. I have struggled for days to understand how to switch back and forth between workbooks and worksheets and what is active and what is not, etc. At this point I think I am a bit lost.

WHAT I WANT TO ACCOMPLISH
-----------------------------------

A. Context

1. 
   [*=1]I have one .xlsm workbook that has many worksheets. Some of them begin with a prefix that lets me screen out the ones I do not want from the MAIN PROCESSING LOOP which steps through the list of worksheets in this primary, active workbook file.
   [*=1]When a valid worksheet is found the code proceeds to establish the FILE NAME and FILE TYPE of the specific vendor in question (as discovered from the worksheet name), and creates a NEW FILE NAME variable which contains this new file PATH and FULL NAME.
   [*=1]Each valid worksheet is an INVENTORY worksheet with about 250 rows of data, including a HEADER to for column (field) names. The columns and column headers found in any valid inventory worksheet are determined by the vendor.
   [*=1]Each valid worksheet also has in columns A to E some evaluation formulas that enable current inventory quantities to be LOOKED UP (from a MASTER inventory query table that is another worksheet of this primary, active workbook file) and used as the new inventory values for that vendor, etc. So, the ONLY columns I want to EXPORT out to the NEW FILES are columns F and beyond (Note: I am coding it as Range("F:AZ") since AZ is well beyond what I need to copy and I do not know how to tell it just use the remaining column width that is actually there.)
   [*=1]There are (currently) five (5) vendor worksheets so I am expecting five (5) NEW files exported/created.
   [*=1]I need to PASTE SPECIAL VALUES as I do not want any FORMULAS to be present in the created files since they need to be uploaded to web vendors.
   [*=1]And as I mentioned earlier, some the vendors require CSV files while others accept XLSX files. Currently these are the only 2 file types that are being required. I have a FILE TYPE worksheet established as configuration data for vendor file types and the main loop finds this info and makes it available to the code cited below.

B. SPECIFIC ERRORS with RESULTS

1. The intended new-file worksheet name of "SHEET1" is not being established when the new file is created.
2. The COPY seems to work, but the PASTE does not take place.
3. The new file IS in fact created, it is just EMPTY and left OPEN.
4. There are NO execution errors.

C. CODE

(sorry for the messy code, the indentations were lost on the copy/paste)

For Each cws In ActiveWorkbook.Worksheets

' Validate worksheet name.
wsn_pfx = Mid(cws.Name, 1, 4)
' Debug.Print "cws.Name = "; cws.Name; " wsn_pfx = "; wsn_pfx
If (wsn_pfx = "OCI-") Then
' Debug.Print "cws.Name = "; cws.Name; " wsn_pfx = "; wsn_pfx
' For the valid worksheet name, find/get the associated export FILE TYPE from the oc_info array
For i = 1 To oc_cnt
' Debug.Print "oc_info part 1 = "; oc_info(i, 1); " oc_info part 2 = "; oc_info(i, 2)
If ((wsn_pfx & oc_info(i, 1)) = cws.Name) Then
ftype = oc_info(i, 2)
' Debug.Print "cws.Name = "; cws.Name; " ftype = "; ftype
End If
Next i

' Set the SAVE-AS file-type
ffmtno = 51
If ftype = "XLSX" Then ffmtno = 51
If ftype = "CSV" Then ffmtno = 6


' Set the new FILE NAME
new_fn = FolderName & "\Casual_Elements_" & cws.Name & "_" & Format(Now(), "yyyy-mm-dd-hhmm.") & ftype


' Copy the required worksheet contents and paste values only (i.e. no formats or formulas)
' Debug.Print "cws.Name = "; cws.Name; " new_fn = "; new_fn

cws.Range("F:AZ").Copy

Set nwb = Workbooks.Add
With nwb
Set nws = nwb.Sheets("Sheet1")
.SaveAs Filename:=new_fn, FileFormat:=ffmtno
'Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
End With
Debug.Print "nwb.Name = "; nwb.Name; " nws.Name = "; nws.Name
cwb.Activate
Application.CutCopyMode = False

End If
Next cws

CONCLUSION

That's pretty much it. I am kind of embarrassed to have to ask for this help since I have been programming (in many other languages) for MANY years. I think somehow I am just not getting the gist of VBA.

Any assistance would be greatly appreciated!

Thank you in advance.

Thomas

 

[Fluff]

Hi & welcome to the board.
Could you please supply all your code? It's very difficult to decipher your code, with only a portion of it. For instance what is oc_info(i, 1)
Also when posting code please place it between code tags (the # icon in the reply window).
Cheers