+ Reply to Thread
Results 1 to 7 of 7

Macro to save data in another workbook

Hybrid View

DeckyB Macro to save data in another... 06-05-2010, 02:56 PM
mrice Re: Macro to save data in... 06-05-2010, 03:52 PM
DeckyB Re: Macro to save data in... 06-06-2010, 05:49 AM
mrice Re: Macro to save data in... 06-06-2010, 06:07 AM
DeckyB Re: Macro to save data in... 06-07-2010, 05:02 PM
ajaykgarg Re: Macro to save data in... 06-08-2010, 08:22 AM
DeckyB Re: Macro to save data in... 06-09-2010, 06:32 PM
  1. #1
    Registered User
    Join Date
    05-25-2010
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    10

    Macro to save data in another workbook

    I hope someone can help me please. I have two workbooks "Ordering System" and "Retail_Master".

    I need a macro within the workbook "Ordering System" that when I run it, it opens up workbook Retail_Master and copies and pastes cell range (A1:I20) from sheet "Chris" into the worksheet "Chris" in the Retail_Master" workbook and then saves and closes the Retail Matser workbook.

    If I then run the macro again I need the macro to paste the next set of data on the next line down. I have tried using a number of different sets of code of the internet but with no success, can anyone help me??
    Last edited by DeckyB; 06-09-2010 at 06:31 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro to save data in another workbook

    Try this adjusting paths as necessary.

    Sub Test()
    Workbooks.Open "C:\temp\Retail_Master.xls"
    ThisWorkbook.Sheets("Chris").Range("A1:I20").Copy Destination:=Workbooks("Retail_Master.xls").Sheets("Chris").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Workbooks("Retail_Master.xls").Save
    Workbooks("Retail_Master.xls").Close
    End Sub

  3. #3
    Registered User
    Join Date
    05-25-2010
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Macro to save data in another workbook

    It opens up the Retail Master workbook but I get a 400 error message and no data copies over. Any ideas?

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro to save data in another workbook

    Hmmm...


    Does the Retail_Master workbook have any hidden or protected sheets and is there definitely a sheet called "Chris"?

  5. #5
    Registered User
    Join Date
    05-25-2010
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Macro to save data in another workbook

    Cheers Martin that works a treat I hadd a small typo in the sheet. How can I tweak it slightly so it only pastes values and not formula?

  6. #6
    Forum Contributor
    Join Date
    03-11-2010
    Location
    India
    MS-Off Ver
    2010
    Posts
    268

    Re: Macro to save data in another workbook

    Replacing the following:-

    ThisWorkbook.Sheets("Chris").Range("A1:I20").Copy Destination:=Workbooks("Retail_Master.xls").Sheets("Chris").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    with following

    ThisWorkbook.Sheets("Chris").Range("A1:I20").Copy 
    
    Workbooks("Retail_Master.xls").Sheets("Chris").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:= xlPasteValues
    might help.

    Please not that I have no tested this.

    hth
    Ajay

  7. #7
    Registered User
    Join Date
    05-25-2010
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Macro to save data in another workbook

    cheers amigo, thats me sorted

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1