+ Reply to Thread
Results 1 to 3 of 3

How can I copy the data from workbook1(sheet1) to workbook2(sheet1)

  1. #1
    Registered User
    Join Date
    11-19-2010
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    3

    How can I copy the data from workbook1(sheet1) to workbook2(sheet1)

    Hi expert, I am a new excel user and trying to use VBA to do the copy and paste automatically from one workbook to another workbook.
    This is the monthly report I am doing every month, there is a raw data file called (a1_wct6) which generate from foxpro program and I have to copy the date and the data to another workbook (trend_local_ztess). The format of file (a1_wct6) is not changed, it's just changed the date such as Oct in my file, but I have to copy the data to a varient file (trend_local_ztess) because the data will be accumulated so that the row number will be increased every month.

    It's much appreciated if anyone help me how to use VBA code to copy the data automatically ?

    I have attached both files for your reference. Thanks so much !
    Attached Files Attached Files
    Last edited by wkshek; 12-02-2010 at 03:13 AM.

  2. #2
    Registered User
    Join Date
    11-19-2010
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How can I copy the data from workbook1(sheet1) to workbook2(sheet1)

    Hi Expert, I've tried to write the VB code myself to copy the data from a1_wct6.xls (Sheet: a1_wct6) to trend_local_ztess.xls(Sheet :wct6), but the range of the data need to change every month, for example, on Oct-10, I have to input the range such as (C2103:C2133) in the VB code so as let it to copy and paste to correct ROW and COLUMN in worksheet (WCT6), however, I am going to do the report for Nov-10, then the data of the ROW will be increased. Appreciated if anyone can teach me how to write a VB code to automatically update the new data from Nov-10 to avoid to change the code I wrote everytime ? Many Thanks

  3. #3
    Registered User
    Join Date
    11-19-2010
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How can I copy the data from workbook1(sheet1) to workbook2(sheet1)

    Sorry that I forgot to put my code here. Here you are

    Sub testing13()

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ChDir "D:\billy\vb_program"
    Workbooks.Open Filename:="D:\billy\vb_program\a1_wct6.xls"

    Windows("a1_wct6.xls").Activate

    ChDir "D:\billy\vb_program"
    Workbooks.Open Filename:="D:\billy\vb_program\trend_local_ztess.xls"

    Windows("a1_wct6.xls").Activate
    Sheets("a1_wct6").Select
    Range("C2:C32").Select
    Selection.Copy

    Windows("trend_local_ztess.xls").Activate
    Sheets("wct6").Select
    Range("C2103:C2133").Select
    Selection.PasteSpecial Paste:=xlValues

    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Windows("a1_wct6.xls").Activate
    Sheets("a1_wct6").Select
    Range("C33:C63").Select
    Selection.Copy

    Windows("trend_local_ztess.xls").Activate
    Sheets("wct6").Select
    Range("D2103:D2133").Select
    Selection.PasteSpecial Paste:=xlValues

    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Windows("a1_wct6.xls").Activate
    Sheets("a1_wct6").Select
    Range("C64:C94").Select
    Selection.Copy

    Windows("trend_local_ztess.xls").Activate
    Sheets("wct6").Select
    Range("E2103:E2133").Select
    Selection.PasteSpecial Paste:=xlValues

    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Windows("a1_wct6.xls").Activate
    Sheets("a1_wct6").Select
    Range("C95:C125").Select
    Selection.Copy

    Windows("trend_local_ztess.xls").Activate
    Sheets("wct6").Select
    Range("F2103:F2133").Select
    Selection.PasteSpecial Paste:=xlValues
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Windows("a1_wct6.xls").Activate
    Sheets("a1_wct6").Select
    Range("C126:C156").Select
    Selection.Copy

    Windows("trend_local_ztess.xls").Activate
    Sheets("wct6").Select
    Range("G2103:G2133").Select
    Selection.PasteSpecial Paste:=xlValues

    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Windows("a1_wct6.xls").Activate
    Sheets("a1_wct6").Select
    Range("C157:C187").Select
    Selection.Copy

    Windows("trend_local_ztess.xls").Activate
    Sheets("wct6").Select
    Range("H2103:H2133").Select
    Selection.PasteSpecial Paste:=xlValues

    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Windows("a1_wct6.xls").Activate
    Sheets("a1_wct6").Select
    Range("C188:C218").Select
    Selection.Copy

    Windows("trend_local_ztess.xls").Activate
    Sheets("wct6").Select
    Range("I2103:I2133").Select
    Selection.PasteSpecial Paste:=xlValues

    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Windows("trend_local_ztess.xls").Activate
    ActiveWindow.Close

    Windows("a1_wct6.xls").Activate

    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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