Need script to open sheet in workbook, copy dynamic range to temp workbook, export as CSV

[mikejennings]

Hello all,

This is my first post, so please be gentle. I've tried searching for this solution piecemeal with varying success.

Here is the project goal:

I need to give a colleague a workbook that has one mandatory sheet "Input". This sheet contains a known number of columns, but an unknown number of rows.

This data is then manipulated into several "Output" sheets in the same workbook.

These output sheets then need to be saved individually to .csv files on the desktop. It would be nice if the user could specify the location for each .csv file. I do not want to save the original workbook as a .csv.

Here is my thought process (again, please be gentle ):

I take this data from "Input" and manipulate it on other sheets ("output1", "output2", etc) in the same workbook using simple functions (i.e. - concat(first,last), etc). Since I have an unknown number of rows on the "Input" sheet, I use "if" functions on the output sheets to determine if data will show in the row.

For example:

Output1.B2 contains =IF('Input'!A2="","",'Input'!C2)
Output1.C2 contains =IF('Input'!A2="","","LOC "&'Input'!E2)

These check Cell A2 on "Input" and if there is anything, then I proceed to fill the value into these cells on Output1, etc. I then copy these rows of functions down to subsequent rows.

This solution is a bit of a kludge, but I could not find a way to dynamically add rows to "Output1", etc as users add more data to additional rows in "Input".

Next, I am trying to open each "Output" sheet, copy all relevant data into a new temp Workbook, SaveAs .csv and close the temp workbook.

Here is the ideal scenario:

The user enters data on "Input" to his heart's content. As he enters data, rows in the "Output" sheets fill in via the aforementioned functions. When done, he clicks an EXPORT button on the "Input" sheet.

The resulting script does the following:

1. Determines how many rows are in use on "Input" (based on data being present in column A) - set variable RowsInUse
2. Open sheet "Output1", select A1:QRowsInUse (we know Q is the last column), copy data
3. Open temp workbook, paste data, SaveAs .csv (I would love it if we can prompt the user for the save location), close temp workbook
4. Repeat step 3 for all other "Output" sheets
5. Return focus to "Input"

The result is the user has one .csv file for every "output" sheet. I have parts of this working with recorded macros. Not sure how to accomplish steps 1 and 2. On step 3, not sure how to prompt the user for save location.

If anyone is interested in the challenge, I'm happy to provide more info and the snippets of code I'm trying to use. Thanks for your kind attention to my ramblings.

 

[tweedle]

This may get you there...something to start with anyway....

Sub Foo()
'1. Determines how many rows are in use on "Input" (based on data being present in column A) - set variable RowsInUse
    With Sheets("Input")
        RowsInUse = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With


    For Each Sheet In Sheets
        If Sheet.Name Like "Output*" Then '4. Repeat step 3 for all other "Output" sheets
            '2. Open sheet "Output1", select A1:QRowsInUse (we know Q is the last column), copy data
            With Sheets("Output1")
                .Range("A1:Q" & RowsInUse).Copy
            End With


            '3. Open temp workbook, paste data, SaveAs .csv (I would love it if we can prompt the user for the save location), close temp workbook
            Dim wb As Workbook
            Set wb = Workbooks.Add
            wb.Sheets(1).Paste


            fileSaveName = Application.GetSaveAsFilename( _
                           fileFilter:="Text Files (*.csv), *.csv")
            If fileSaveName <> False Then
                wb.SaveAs fileSaveName, xlCSV
                wb.Close False
                Set wb = Nothing
            End If


            '4. Repeat step 3 for all other "Output" sheets


        End If
    Next Sheet


    '5. Return focus to "Input"
    Sheets("Input").Activate


End Sub

 

[tweedle]

Slight hard-code error; #2 s/b

            '2. Open sheet "Output1", select A1:QRowsInUse (we know Q is the last column), copy data
            With [COLOR=#0000cd]Sheet[/COLOR]
                .Range("A1:Q" & RowsInUse).Copy
            End With