Where to place the "[" ," ' " , and "!" - help

  • Thread starter Thread starter Jeff Smith
  • Start date Start date
J

Jeff Smith

Hi,

A little while ago, a kind person suggested some code that worked
brilliantly for my problem at that time. I didn't save the post and want to
extend this technique to a new situation. The code below works when it is in
the same workbook.

Sub TransferTo3104()
'
Dim target_wks As Worksheet
Dim source_wks As Worksheet
'
Set source_wks = Worksheets("Test Sheet") Set target_wks =
Worksheets("Data") '
target_wks.Range("A52:U53").Value = source_wks.Range("A3:AB3").Value
'
End Sub

My new need it to extract data from File A to File B

- the source file name changes but is in the format "yy-mm-dd Test xx.xls"
(or xxx when greater than 99). It will always be ThisWorksheet (where the
macro will be lodged)
- the target worksheet is always titled "3104Compliance.xls" at a worksheet
titled "Data"

I need to say somewhere that source_wks is "this workbook", at a worksheet
titled "Test Sheet" and the target_wks is "3104Compliance.xls" at a
worksheet titled "Data".

My problem is I think I need to add some left and right braces and/or left
and right apostrophe and an exclamation mark in the file name (and I don't
know how to do it :- (

I would be grateful if someone can help me to make this work.

Thanks in anticipation,

regards

Jeff Smith
 
Hi Jeff
try

Sub TransferTo3104()
'
Dim target_wbk as workbook
Dim source_wbk as workbook
Dim target_wks As Worksheet
Dim source_wks As Worksheet
'
set source_wbk = activeworkbook
on error resume next
set target_wbk = workbooks("3104Compliance.xls")
on error goto 0
if target_wbk is nothing then
workbooks.open "C:\temp\3104Compliance.xls"
end if

Set source_wks = source_wbk.Worksheets("Test Sheet")
Set target_wks =target_wbk.Worksheets("Data")
target_wks.Range("A52:U53").Value = source_wks.Range("A3:AB3").Value
target_wbk.save
target_wbk.close
source_wbk.activate

'
End Sub
 
Frank,

I believe you provided me with the code I'm trying to modify.
Thank you for this. I see I need to have more definitions when different
workbooks are involved. I am happy that I can apply this.

Many thanks again,

sincerely

Jeff Smtih
 
Back
Top