Pasting Tables

  • Thread starter Thread starter cj21
  • Start date Start date
C

cj21

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
 
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.
 
Thankyou for the reply, i don't think you understand my porblem.


I have about 100 individual country datasets with trade data. For eac
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 th
same worksheet as the data). I have created the standard table tha
includes some complex formulas, i just want it so i press a button an
it appears for each of the 100 sets. this will save time copying an
pasteing.

Chri
 
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).
 
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
 
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.
 
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
 
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.
 
this except where you put sheet 99 and sheet12345. I don't know what i
have to put in this bit.


Chris
 
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
 
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
 
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.
 
Back
Top