apologies for posting it again but...
Thank you Patrick,
I appreciate your help...I'll try your suggestions and get back here when
done.
Sincerely
J_J
"Patrick Molloy" wrote in message
...
there's no need to keep opening the workbook from which you're copying
each
time....in the code below I have done most of what you need ... you just
need
to repeat the two lines for copy/paste for the ranges that I didn't do
Option Explicit
Sub Macro1()
Dim strFileName1 As String
Dim wb1 As Workbook
strFileName1 = Application.GetOpenFilename("Excel Templates
(*.xlt),*.xlt")
If strFileName1 = "" Then Exit Sub
Set wb1 = Workbooks.Open(strFileName1)
''' Application.Visible = False
wb1.Worksheets("Sheet13").Range("C21:C30").Copy
ThisWorkbook.Worksheets("Sheet1").Range("B1:B10"). PasteSpecial xlAll
wb1.Worksheets("Sheet7").Range("G5:G19").Copy
ThisWorkbook.Worksheets("Sheet1").Range("B14:B28") .PasteSpecial xlAll
wb1.Worksheets("Sheet5").Range("G5:G12").Copy
ThisWorkbook.Worksheets("Sheet1").Range("B31:B38") .PasteSpecial xlAll
''' REPEAT THESE TWO LINES FOR EVERY RANGE '''
wb1.Worksheets("Sheet5").Range("G5:G12").Copy
ThisWorkbook.Worksheets("Sheet1").Range("B31:B38") .PasteSpecial xlAll
''' etc etc
wb1.Close False
Set wb1 = Nothing
strFileName1 = ""
'Application.Visible = True
strFileName1 = Application.GetOpenFilename("Excel Templates
(*.xlt),*.xlt")
If strFileName1 = "" Then Exit Sub
Set wb1 = Workbooks.Open(strFileName1)
''' Application.Visible = False
''' REPEAT THESE TWO LINES FOR EVERY RANGE '''
wb1.Worksheets("Sheet13").Range("C21:C30").Copy
ThisWorkbook.Worksheets("Sheet2").Range("B1:B10"). PasteSpecial xlAll
''' etc etc
''' example
wb1.Worksheets("Sheet7").Range("G5:G19").Copy
ThisWorkbook.Worksheets("Sheet2").Range("B14:B28") .PasteSpecial xlAll
' Finished copying
Application.CutCopyMode = False
wb1.Close False
Set wb1 = Nothing
Application.Visible = True
Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("A12") = strFileName1
Worksheets("Sheet3").Range("B12") = strFileName1
End Sub
NOTE: Copy/paste is BAD - so use it if you need the formats etc etc
IF you only want the values then each pair can be made into a single line,
and this will work much faster...
example
these two lines
wb1.Worksheets("Sheet13").Range("C21:C30").Copy
ThisWorkbook.Worksheets("Sheet1").Range("B1:B10"). PasteSpecial xlAll
become
ThisWorkbook.Worksheets("Sheet1").Range("B14:B28") .value = _
wb1.Worksheets("Sheet7").Range("G5:G19").value
HTH
Patrick Molloy
Microsoft Excel MVP
"J_J" wrote:
Hi,
The below code intends to read data from 2 picked excel *.xlt files,
pastes
them on the target file one after the other.
But the problem is that, although it worked without any errors on Win98
Office2K, but now with my new system WinXP+Office2003 I am getting
complaint
messages now. Especially with the repeted
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
lines...
If I eliminate them it doesn't work at all.
How can I close the read workbooks (after all data is read) with the
filenames beeing as varibles (strFileName1, strFileName2) and locations
that
may vary?
'-------------------------
Sub Macro1()
'
' Macro1 Macro
'
'Dim strFileName1 As String
'Dim strFileName2 As String
strFileName1 = Application.GetOpenFilename("Excel Templates (*.xlt),
*.xlt")
If strFileName1 = "" Then Exit Sub
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Application.Visible = False
Worksheets("Sheet13").Activate
Range("C21:C30").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B1:B10").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet7").Activate
Range("G5:G19").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B14:B28").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet5").Activate
Range("G5:G12").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B31:B38").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet6").Activate
Range("G5:G11").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B41:B47").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet8").Activate
Range("G5:G13").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B50:B58").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet2").Activate
Range("G5:G9").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B61:B65").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet11").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B68:B71").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet3").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B74:B77").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName1, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet4").Activate
Range("G5:G7").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet1").Activate
Range("B80:B82").Select
ActiveSheet.Paste , True
'Application.Visible = True
strFileName2 = Application.GetOpenFilename("Excel Templates (*.xlt),
*.xlt")
If strFileName2 = "" Then Exit Sub
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Application.Visible = False
Worksheets("Sheet13").Activate
Range("C21:C30").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B1:B10").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet7").Activate
Range("G5:G19").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B14:B28").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet5").Activate
Range("G5:G12").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B31:B38").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet6").Activate
Range("G5:G11").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B41:B47").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet8").Activate
Range("G5:G13").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B50:B58").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet2").Activate
Range("G5:G9").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B61:B65").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet11").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B68:B71").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet3").Activate
Range("G5:G8").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B74:B77").Select
ActiveSheet.Paste , True
Workbooks.Open(strFileName2, Editable:=True).RunAutoMacros
Which:=xlAutoOpen
Worksheets("Sheet4").Activate
Range("G5:G7").Select
Selection.Copy
Windows("AnalizR4.xls").Activate
Worksheets("Sheet2").Activate
Range("B80:B82").Select
ActiveSheet.Paste , True
' Finished copying
Application.Visible = True
Application.CutCopyMode = False
Windows("AnalizR4.xls").Activate
Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("A12") = strFileName1
Worksheets("Sheet3").Range("B12") = strFileName2
End Sub
'-----------------------
'--------------------
TIA
J_J
|