+ Reply to Thread
Results 1 to 12 of 12

Pasting Tables

  1. #1
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    151

    Pasting Tables

    I have a large table template that i want to paste into many documents. I don't know anything about Macro's but can i create an icon to do this for me.

    e.g suppose my table template is:

    Product code Product Description Average Stddev Var Skewness
    01 Hides
    02 CD's
    03 apparell
    04 bovine meat


    In practice it is larger. Note the descriptive statistics data is not inserted. I have data for many countries. So for instance i want to be able to paste this template into my data for Nigeria and then use formulas to fill it in (i have no problems with the formulas). Then i want to do the same thing for Kenya etc.

    Chris

  2. #2
    Dave Peterson
    Guest

    Re: Pasting Tables

    I think I would just open the workbook with the master table when I needed to
    add the formulas--then I'd just copy and paste. Why bother inserting the table
    before you actually need it. You may find that part way through the process,
    the table needs to be corrected, er, enhanced and you'll have fewer things to
    fix.

    cj21 wrote:
    >
    > I have a large table template that i want to paste into many documents.
    > I don't know anything about Macro's but can i create an icon to do this
    > for me.
    >
    > e.g suppose my table template is:
    >
    > Product code Product Description Average Stddev Var Skewness
    > 01 Hides
    > 02 CD's
    > 03 apparell
    > 04 bovine meat
    >
    > In practice it is larger. Note the descriptive statistics data is not
    > inserted. I have data for many countries. So for instance i want to be
    > able to paste this template into my data for Nigeria and then use
    > formulas to fill it in (i have no problems with the formulas). Then i
    > want to do the same thing for Kenya etc.
    >
    > Chris
    >
    > --
    > cj21
    > ------------------------------------------------------------------------
    > cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
    > View this thread: http://www.excelforum.com/showthread...hreadid=489072


    --

    Dave Peterson

  3. #3
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    151

    not sure if you understnad

    Thankyou for the reply, i don't think you understand my porblem.


    I have about 100 individual country datasets with trade data. For each dataset i want to evaluate it with some descriptive statistics e.g. mean, std dev, var skew etc.

    I want to present this in a standard table for each dataset (on the same worksheet as the data). I have created the standard table that includes some complex formulas, i just want it so i press a button and it appears for each of the 100 sets. this will save time copying and pasteing.

    Chris

  4. #4
    Dave Peterson
    Guest

    Re: Pasting Tables

    It sounds like you could set up a macro to copy and paste.

    If I were you, I'd record one when I did it manually. Then tweak that to make
    it more generic.

    You could even have the macro open the workbooks, copy from the master, paste
    into the workbook, and then save that second workbook.

    If you need help tweaking that code, post back. Be sure to include some
    details--the location of the original table. The location of where it should be
    pasted (include worksheet names and addresses, too).

    cj21 wrote:
    >
    > Thankyou for the reply, i don't think you understand my porblem.
    >
    > I have about 100 individual country datasets with trade data. For each
    > dataset i want to evaluate it with some descriptive statistics e.g.
    > mean, std dev, var skew etc.
    >
    > I want to present this in a standard table for each dataset (on the
    > same worksheet as the data). I have created the standard table that
    > includes some complex formulas, i just want it so i press a button and
    > it appears for each of the 100 sets. this will save time copying and
    > pasteing.
    >
    > Chris
    >
    > --
    > cj21
    > ------------------------------------------------------------------------
    > cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
    > View this thread: http://www.excelforum.com/showthread...hreadid=489072


    --

    Dave Peterson

  5. #5
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    151

    Macro

    Not sure how to do a macro. I was trying earlier but i did not suceed. my template is in the following directory:

    C:\Documents and Settings\economics\Desktop\Download Folder

    How would i get VBA to open it and paste it to one of my 100 datasets.


    Chris

  6. #6
    Dave Peterson
    Guest

    Re: Pasting Tables

    option explicit
    sub testme01()

    dim tWkbk as workbook
    dim wkbk as workbook

    set twkbk = workbooks.open("C:\Documents and Settings\economics" _
    & "\Desktop\Download Folder\workbooknamehere.xls")

    set wkbk = workbooks.open("C:\yourpathtotheotherworkbook\namehere.xls")

    twkbk.worksheets("sheet99").range("a1:x99").copy _
    destination:=wkbk.worksheets("sheet12345").range("a1")

    wkbk.close savechanges:=true
    twkbk.close savechanges:=false 'no need to change the master

    end sub

    would be a general approach. You'll have to fill in the actually names
    (workbooks and worksheets) and addresses.


    cj21 wrote:
    >
    > Not sure how to do a macro. I was trying earlier but i did not suceed.
    > my template is in the following directory:
    >
    > C:\Documents and Settings\economics\Desktop\Download Folder
    >
    > How would i get VBA to open it and paste it to one of my 100 datasets.
    >
    > Chris
    >
    > --
    > cj21
    > ------------------------------------------------------------------------
    > cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
    > View this thread: http://www.excelforum.com/showthread...hreadid=489072


    --

    Dave Peterson

  7. #7
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    151

    Thanks Dave

    This is a bit tough for me. I don't really understand any of it. Could you post the message again but highlight in red the bits i need to change..

    Chris

  8. #8
    Dave Peterson
    Guest

    Re: Pasting Tables

    I post in plain text.

    option explicit
    sub testme01()

    dim tWkbk as workbook
    dim wkbk as workbook

    set twkbk = workbooks.open("C:\Documents and Settings\economics" _
    & "\Desktop\Download Folder\????????????????.xls")

    set wkbk = workbooks.open("?:\????????\?????????\?????.xls")

    twkbk.worksheets("????????").range("????:????").copy _
    destination:=wkbk.worksheets("?????????").range("??")

    wkbk.close savechanges:=true
    twkbk.close savechanges:=false 'no need to change the master

    end sub

    Look for question marks.

    cj21 wrote:
    >
    > This is a bit tough for me. I don't really understand any of it. Could
    > you post the message again but highlight in red the bits i need to
    > change..
    >
    > Chris
    >
    > --
    > cj21
    > ------------------------------------------------------------------------
    > cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
    > View this thread: http://www.excelforum.com/showthread...hreadid=489072


    --

    Dave Peterson

  9. #9
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    151

    I've done all of

    this except where you put sheet 99 and sheet12345. I don't know what i have to put in this bit.


    Chris

  10. #10
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    151
    Option Explicit
    Sub testme01()
    i have entered the following and it works, i ignored the sheets.


    Dim tWkbk As Workbook
    Dim wkbk As Workbook

    Set tWkbk = Workbooks.Open("C:\Documents and Settings\economics" _
    & "\Desktop\Download Folder\TableTemplate.xls")

    Set wkbk = Workbooks.Open("C:\Documents and Settings\economics" _
    & "\Desktop\Download Folder\Africa\Nigeria\Nigeria rates 1992 2-digit.xls")

    tWkbk.Range("m2:v98").Copy _
    Destination:=wkbk.Range("m2:v98")

    wkbk.Close savechanges:=True
    tWkbk.Close savechanges:=False 'no need to change the master


    End Sub



    However i now get the following message:

    Run time error '438'

    Object doesn't support this property or method

    It talks about debugging whatevre that is.


    Chris

  11. #11
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    151

    not that quick

    The method is not really that quick because i have to type in the directory name of each of the 100 datasets. Is there anyway round this?

    Thankyou for your help


    Chris

  12. #12
    Dave Peterson
    Guest

    Re: Pasting Tables

    Are all the workbooks in the same folder?

    If no, then you can run this for as many folders as you have. Just click on the
    first workbook, then ctrl-click on the subsequent (or click on the first and
    shift-click to select all the files in between).

    Option Explicit
    Sub testme01()

    Dim tWkbk As Workbook
    Dim wkbk As Workbook
    Dim myFileNames As Variant
    Dim iCtr As Long

    myFileNames = Application.GetOpenFilename("Excel Files, *.xls", _
    MultiSelect:=True)

    If IsArray(myFileNames) = False Then
    Exit Sub
    End If

    Set tWkbk = Workbooks.Open("C:\Documents and Settings\economics" _
    & "\Desktop\Download Folder\TableTemplate.xls")

    For iCtr = LBound(myFileNames) To UBound(myFileNames)
    Set wkbk = Workbooks.Open(myFileNames(iCtr))

    tWkbk.Worksheets(1).Range("m2:v98").Copy _
    Destination:=wkbk.Worksheets(1).Range("m2")

    wkbk.Close savechanges:=True
    Next iCtr

    tWkbk.Close savechanges:=False

    End Sub

    I added worksheets(1) to the code (twice). That means it copies from the
    leftmost worksheet and pastes into the leftmost worksheet (according to the tabs
    at the bottom).

    And I changed the destination to be just one cell--excel will expand to match
    the copied range.

    cj21 wrote:
    >
    > The method is not really that quick because i have to type in the
    > directory name of each of the 100 datasets. Is there anyway round
    > this?
    >
    > Thankyou for your help
    >
    > Chris
    >
    > --
    > cj21
    > ------------------------------------------------------------------------
    > cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
    > View this thread: http://www.excelforum.com/showthread...hreadid=489072


    --

    Dave Peterson

+ 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