VBA - Open new sheet, Paste special values, save as

[benoj2005]

Is anyone able to sort me out some VBA that I can attach to a button on each of the attached sheets.

VBA - New sheet, Copy paste values, Saves as.xlsx

Macro needs to,

1. Copy the entire sheet which is open
2. Open a new workbook
3. Paste the values & formats
4. Save the sheet as file name "mm yyyy & the sheet name from where it was copied" - Month and year will need to be taken from the sheet titled "save info".

So basically, if the macro was run in sheet A. Then it would copy the sheet, open a new blank workbook, paste the values and formats, then save the new workbook as "04 2014 A"

[nathansav]

1 Range.Copy method
2 Workbooks.Add method
3 PasteSpecial method
4 Activeworkbook.SaveAs method

google these or type them in VBA and press F1, should get you started.

[benoj2005]

1 Range.Copy method
2 Workbooks.Add method
3 PasteSpecial method
4 Activeworkbook.SaveAs method

google these or type them in VBA and press F1, should get you started.

Thanks, found some code, I've modified it to what I want, but I'm struggling with the last piece of the puzzle.

VBA - New sheet, Copy paste values, Saves as.xlsm

Atm, the worksheet called "Save info" is copied and saved, I don't want this sheet to be included.

Also the parts of code where 04 2014 is, needs to be linked to cell B1 & B2 in the Save info worksheet

 'Create new folder to save the new files in
    FolderName = Sourcewb.Path & "\" & "04 2014"
    MkDir FolderName

and

'Save the new workbook and close it
            With Destwb
                .SaveAs FolderName _
                      & "\" & "04 2014 " & Destwb.Sheets(1).Name & FileExtStr, _
                        FileFormat:=FileFormatNum
                .Close False
            End With

[nathansav]

Sourcewb.Path & "\" & range("b1").value & " " & range("b2").value

you'll need to get all the worksheets into an array missing the save info one

dim w as worksheet

for each w in thisworkbook.worksheets

if w.name<>"save info" then
ADD TO ARRAY OF WORKSHEETS
end if

next w

Copy this array to a new workboook.

[benoj2005]

VBA - New sheet, Copy paste values, Saves as.xlsm

Changed the file names to include

range("b1").value & " " & range("b2").value

Works fine for the folder, if I run the macro when on the worksheet called Save info, can live with that. However the only sheet that saves with the 04 2014 infornt of the file name is the Save info one.

I need to reference the sheet Save info & cell b1, not just b1 in whatever the active sheet is.

Not tried the array method, is there a recommended source for this code? Or even better if you could add it in, I'm new to this VBA lark!

[nathansav]

Worksheets(sheetname).range(address)

this is all available on google, i'd start looking there at the Excel application model, how its put together, e.g. Application --> Workbook --> worksheets --> cells etc rather than pieceing together line by line.

[benoj2005]

Thanks, got it all working except not copying and saving the one particular sheet.

I find using google for what I'm looking for difficult when I don't know the lingo. It's easy when you know the code you're looking for.

[nathansav]

http://stackoverflow.com/questions/1...-as-a-variable

[benoj2005]

http://stackoverflow.com/questions/1...-as-a-variable

Yea I'd been on that, but it seemed I'd have to name all the sheets in the array, realised there's a much simpler way

      'If the sheet is visible then copy it to a new workbook
        If sh.Name <> "save info" Then
            sh.Copy

[nathansav]

Sure, pop Option Compare Text at the top of your code, the very top. This will negate the case sensitibity of strings.