Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    85

    Unanswered: macros excel 2010

    Ok, so im having trouble recording my macro. I have it attached to a button. on the click i want it to go to 5 work sheets individually and save each sheet to the desk top as individual work books. It keeps giving me time error etc. i dont know what im doing wrong.

    I have My one work sheet as a questionaire. I have 5 other worksheets linked to the questionaire. So i have the five tables (on 5 Different worksheets). Now i have been trying to get this macro which is attached to a button to work. I cant get it to go into each of the 5 worksheets and save it as the work sheet name onto my desk top as 5 individual workbooks. for some reason it isnt working. I am new to using macros so i have a feeling im not doing it right.

    I need this because it will make my life a lot easier when importing into my database. I have messsed this up so many times please help! If more information is needed just ask!

    Mike
    ____________
    Last edited by Mike02; 06-08-12 at 13:19.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi - I think we'll need to see the code.

  3. #3
    Join Date
    Jun 2012
    Posts
    85
    Heres my code. Im fairly new with code. I believe i have made it easier to manipulate on the forum...

    Sub Button149_Click()
    '
    ' Button149_Click Macro
    ' saving worksheets into individual workbooks
    '

    '
    Public Sub SaveWorksheetsAsCsv()
    Dim WS As Excel.Worksheet
    Dim SaveToDirectory As String

    SaveToDirectory = "C:\"

    For Each WS In ThisWorkbook.Worksheets
    WS.SaveAs SaveToDirectory & WS.Name, xlCSV
    Next

    End Sub

  4. #4
    Join Date
    Jun 2012
    Posts
    85
    I was able to get it to work. I re- recorded the macro... Here was the code that worked.

    Sub Button149_Click()
    '
    ' Button149_Click Macro
    '

    '
    Sheets("Address Information").Select
    Sheets("Address Information").Copy
    ChDir "C:\Users\Stephan2\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\Stephan2\Desktop\Address Information.csv", FileFormat:=xlCSV, _
    CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
    Sheets("Portfolio Manager Section").Select
    Sheets("Portfolio Manager Section").Copy
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\Stephan2\Desktop\Portfolio Manager Section.csv", FileFormat:=xlCSV _
    , CreateBackup:=False
    ActiveWindow.Close
    Sheets("Fund Strategy Section").Select
    Sheets("Fund Strategy Section").Copy
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\Stephan2\Desktop\Fund Strategy Sectoion.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Application.WindowState = xlMinimized
    Application.WindowState = xlNormal
    Sheets("Risk Management").Select
    Sheets("Risk Management").Copy
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\Stephan2\Desktop\Risk Management.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Service Providers").Select
    Sheets("Service Providers").Copy
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\Stephan2\Desktop\Service Providers.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    End Sub

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    The code you posted in #3 will give a compile error. You need to delete this line:
    Code:
    Public Sub SaveWorksheetsAsCsv()

  6. #6
    Join Date
    Jun 2012
    Posts
    85
    ok thanks, Im trying to create another macro as well now preparing my data for import to access. since access doesnt like fromulas i am trying to paste values instead withthis macro. I have worked out the following code... but im not able to get the values to paste. an errror comes up saying values are not the same size, (copy and paste area) this doesnt make any sense because I worked when i origionally reecorded the macro..

    Thanks in advance..


    Application.WindowState = xlMinimized
    Range("A2:P2").Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("B7").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.WindowState = xlMinimized
    Range("A2:AY2").Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("D6").Select
    ActiveWorkbook.Save
    Application.WindowState = xlNormal
    ActiveWindow.Close
    Application.WindowState = xlMaximized
    Application.WindowState = xlMinimized
    Selection.End(xlToLeft).Select
    Range("A2:R2").Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("2:3").Select
    Selection.Delete Shift:=xlUp
    Range("D8:E9").Select
    Range("E9").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.WindowState = xlMinimized
    Range("P3").Select
    Selection.End(xlToLeft).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.WindowState = xlMinimized
    Range("DC2").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("DA2").Select
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("D3:E5").Select
    Range("E5").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    End Sub

  7. #7
    Join Date
    Jun 2012
    Posts
    85
    nvm, few hours later and i got it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •