Copy and paste automatically

P

pedro AM

Hi all

I am trying to create a macro that copies from a txt file into excel but it
does not work.

I have a excel file with 12 sheets (one for each month) and in each 5
columns headed
year, period,account,location,value.

I want it to get info from a txt file that may change every month and paste
it in columns C,D and E.
The problem is that it doesn't give me the chance to, everytime I run it,
choose what tab to paste it to.
Is there a way to create this macro?

Any help would be very much appreciated. I have excel 2000 pro.

Thanks
 
J

Jezebel

You'll need to provide a little more information. There are any number of
ways for a macro to get information from the user -- what method are you
trying to use at the moment, and what doesn't work?
 
D

Dave Peterson

I think I'd run the macro from the sheet where I wanted it pasted.

Then I could just use Activesheet.

dim CurWks as worksheet
dim txtWks as worksheet

set curwks = activesheet
set txtwks = workbooks.open(your stuff to open the text file).worksheets(1)

txtwks.range("a:c").copy _
destination:=curwks.range("a1")
 
P

pedro AM

Hi Dave and Jezebel

Unfortunately I am not as good with VB as to be able to write that coding in
the macro and make sense of it.

I have tried to create a macro by going through the macro:create menu in
Excel.
I just do =data:get external data: select the file and import the data
(separator=semicolon,do not import first column) and paste in a chosen cell
this works well with the macro.
However, when I try the macro again to upload another file into another
sheet it does not give me the choice to either open a different txt file nor
to chose a different cell to paste it to.

It is in the file to import info to and not in the one to manage all
processes.

Do you have any idea as how can I set the parameters?

thank you
 
D

Dave Peterson

You can get the filename from the user (you??) with something like:

dim myFileName as variant

myfilename = application.getopenfilename(filefilter:="Excel Files, *.xls")
if myfilename = false then
exit sub 'user hit cancel
end if

then use myfilename in place of the filename specified in your recorded macro.
 
Top