Passing Worksheet Names as Variables
 

Hi,

I've created a simple macro that accesses a .txt file, copies a range
or data, opens a new document and pastes the data.

I need to increase the functionality of this Macro as follows:

One of the copied cells in the first document contains a value that
needs to be stored into a variable (the value is always in the same
cell so this reference can be hardcoded).

When the new workbook is opened I need to retrieve the value above and
compare it to the worksheet names, once the relevant worksheet has been
found I need to open the worksheet and paste the information into the
appropriate cells.

I'm sure this can be done and am half way to doing it but can't seem to
get the syntax right. If anyone can give me some pointers I'd be very
appreciative. If at all possible could you provide comments for each
step so I can see the processes involved for future reference.
Many thanks
Dave


--
francd1
------------------------------------------------------------------------
francd1's Profile: http://www.excelforum.com/member.php...o&userid=24703
View this thread: http://www.excelforum.com/showthread...hreadid=382715

Passing Worksheet Names as Variables
 

Something along the lines of (not tested)

With Activeworkbook 'this will be the newly opened text file
.Range("H1:M10").Copy
Destination:=myWb.Worksheets(.Range("A1").Value).R ange("H1")
End With

This is guessing a bit at what you do in detailed, but it is taking the
value in A1 from the newly opened text file and passing that as the
worksheet name of a workbook object called myWb.

--
HTH

Bob Phillips

"francd1" wrote in
message ...

Hi,

I've created a simple macro that accesses a .txt file, copies a range
or data, opens a new document and pastes the data.

I need to increase the functionality of this Macro as follows:

One of the copied cells in the first document contains a value that
needs to be stored into a variable (the value is always in the same
cell so this reference can be hardcoded).

When the new workbook is opened I need to retrieve the value above and
compare it to the worksheet names, once the relevant worksheet has been
found I need to open the worksheet and paste the information into the
appropriate cells.

I'm sure this can be done and am half way to doing it but can't seem to
get the syntax right. If anyone can give me some pointers I'd be very
appreciative. If at all possible could you provide comments for each
step so I can see the processes involved for future reference.
Many thanks
Dave


--
francd1
------------------------------------------------------------------------
francd1's Profile:
http://www.excelforum.com/member.php...o&userid=24703
View this thread: http://www.excelforum.com/showthread...hreadid=382715

Passing Worksheet Names as Variables
 

Thanks for the reply, it looks like it's exactly what I'm after but I
don't seem to be able to get it to work.
It appears the debugger objects to me using the Destination statment
without an expression after it, I have tried the following (apologies
if the parenthesis are in the wrong place but I've tried pretty much
every derivation and can't get it to work)

With Activeworkbook
..Range("C1:D4").Copy
Destination:=Workbooks"Trend
Analysis.xls".Range("A1").Value).Range("C1)
End With

I get a different error depending on whether Destination is on the same
line as the .Copy statment or on a seperate line.

Further to the above it would be handy to have a button in "Trend
Analysis.xls" that when pressed displays a user entry box that allows
the user to enter the name of the .txt file to open. The macro should
then open the .txt file and execute the above section.


--
francd1
------------------------------------------------------------------------
francd1's Profile: http://www.excelforum.com/member.php...o&userid=24703
View this thread: http://www.excelforum.com/showthread...hreadid=382715

Passing Worksheet Names as Variables
 

On the basis that it is a text file and will only have 1 worksheet

Set myWb = Workbooks("Book2")
With ActiveWorkbook 'this will be the newly opened text file
.Worksheets(1).Range("H1:M10").Copy _
myWb.Worksheets(.Worksheets(1).Range("A1").Value). Range("H1")
End With


--
HTH

Bob Phillips

"francd1" wrote in
message ...

Thanks for the reply, it looks like it's exactly what I'm after but I
don't seem to be able to get it to work.
It appears the debugger objects to me using the Destination statment
without an expression after it, I have tried the following (apologies
if the parenthesis are in the wrong place but I've tried pretty much
every derivation and can't get it to work)

With Activeworkbook
Range("C1:D4").Copy
Destination:=Workbooks"Trend
Analysis.xls".Range("A1").Value).Range("C1)
End With

I get a different error depending on whether Destination is on the same
line as the .Copy statment or on a seperate line.

Further to the above it would be handy to have a button in "Trend
Analysis.xls" that when pressed displays a user entry box that allows
the user to enter the name of the .txt file to open. The macro should
then open the .txt file and execute the above section.


--
francd1
------------------------------------------------------------------------
francd1's Profile:
http://www.excelforum.com/member.php...o&userid=24703
View this thread: http://www.excelforum.com/showthread...hreadid=382715