Help Needed (Detailed Problem)!!!!

[AcTiVation]

With regards to a previous post that I made, I hope this gives everyone a better idea of my predicament. I'm not a VBA or Macro guru, so go easy on me. I can catch on real quickly, but I'm not that advanced in excel usage. I know formulas (pretty advanced ones), how to mix formulas together, but when it comes to VBA or Macros, i'm lost. I know what they are and am familiar with the code format (similar to C+ it seems), but haven't coded anything advanced in excel. So could if you provide me with a step by step solution (where to go, where to click, how to activate, etc) it would be really helpfull.
Here's my problem:
There are two files with names and locations "c:filesfile1file1.xls" & "c:filesfile2file2.xls" with passwords file1 and file2 respectively. Is it possible to construct a VBA code or Macro (or some combination - the easier the better to extract a range of data say B355:J355 (the data range is the same for both files) which will insert/paste them in a file which is located in c:filescentralcentral.xls.

Thanks in advanced for your help. You can e-mail me directly @ lmashaud@bdp.ca

 

[Gahagan]

Try this out: long, but VERY straight forward. Open the VBE (Alt+F11), right click whatever project you're working on (this is the name of the workbook you want to put this code into, and select 'add module' or 'new module' whatever. Paste the following code in, and revise as necessary. Let me know how it goes!!

I'd recommend trying it out on other files first, as always...
::nly use one backslash in the file path - board displays incorrectly:::
Sub openem()

'Open the workbook you plan to copy information TO
Workbooks.Open "c:\files\central\central.xls"

'Open the first workbook you plan to copy FROM
Workbooks.Open "c:\files\file1\file1.xls", , , , "file1" 'this is the password
'Select the correct sheet in the workbook (if necessary)
Sheets("Sheet1").Activate
'Copy the desired range from file1.xls
Range("B355:J355").Copy
'Activate central.xls
Windows("central.xls").Activate
'Select the correct sheet (if necessary)
Sheets("sheet1").Activate
'Paste the range to central.xls
Range("B355:J355").PasteSpecial xlPasteAll
'Close file1.xls
Workbooks("file1.xls.").Close savechanges:=False

'Open the second workbook you plan to copy FROM
Workbooks.Open "c:\files\file2\file2.xls", , , , "file2" 'this is the password
'Select the correct sheet in the workbook (if necessary)
Sheets("Sheet1").Activate
'Copy the desired range from file2.xls
Range("B355:J355").Copy
'Activate central.xls
Windows("central.xls").Activate
'Select the correct sheet in central.xls (if necessary)
Sheets("sheet1").Activate
'Paste the range to central.xls
Range("B355:J355").PasteSpecial xlPasteAll
'Close file2.xls
Workbooks("file2.xls.").Close savechanges:=False

'you may delete all the commented lines (green ones like this one) if you want

End Sub

Then in Excel: Tools-Macros-Macro...-openit-run.

Good luck!
Gahagan
This message was edited by Gahagan on 2002-03-07 10:37
This message was edited by Gahagan on 2002-03-07 10:41
This message was edited by Gahagan on 2002-03-13 23:48