| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
apologies for posting it again but...
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
apologies for posting it again but...
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 Macro/Link Issue (apologies for cross-posting) | Links and Linking in Excel | |||
| Apologies for triple post | New Users to Excel | |||
| the owner of posting should be able to delete the posting | Excel Discussion (Misc queries) | |||
| Apologies | Excel Programming | |||
| My Apologies | Excel Programming | |||
![J_J[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
