Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
Though I do not know VBA I managed to adapt Ron de Bruins code as follows. It
does what I want thus far. I now wish to have an additional condition i.e.IF Consol!D2=1 Application.Run €śCopyToSh5€ť , IF Consol!D2=2, Application.Run €śCopyToSh6€ť,IF Consol!D2=3,Application.Run €śCopyToSh7€ť, etc upto 10. Any help from the ng will be appreciated.. Also would it be possible to select the files from a list (named range €śSALES€ť) in the Main Sheet rather than an onscreen selection Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" For N = LBound(FName) To UBound(FName) Set destrange = sh.Cells(1, 1) GetData FName(N), "Sheet1", "A1:D6", destrange, True Application.Run "CopyToSh5" Next End If ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub ============================= Sub CopyToSh5() ' ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select Range("A1:D6").Select Selection.Copy Sheets("Sheet5").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False End Sub ================================================== = Sub DeleteConsol() ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select ActiveWindow.SelectedSheets.Delete Sheets("Sheet5").Select Selection.Clear End Sub -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
You can loop through a list like so. I haven't tested this but it seems to
be what you ask Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count,"A").End(xlUp).Row For i = 1 To iLastRow sFilename = sh2.Cells(i,"A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) GetData sFilename, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case1 : Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6", Case 3: Application.Run "CopyToSh7" 'etc. End Select End If -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Though I do not know VBA I managed to adapt Ron de Bruins code as follows. It does what I want thus far. I now wish to have an additional condition i.e.IF Consol!D2=1 Application.Run "CopyToSh5" , IF Consol!D2=2, Application.Run "CopyToSh6",IF Consol!D2=3,Application.Run "CopyToSh7", etc upto 10. Any help from the ng will be appreciated.. Also would it be possible to select the files from a list (named range "SALES") in the Main Sheet rather than an onscreen selection Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" For N = LBound(FName) To UBound(FName) Set destrange = sh.Cells(1, 1) GetData FName(N), "Sheet1", "A1:D6", destrange, True Application.Run "CopyToSh5" Next End If ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub ============================= Sub CopyToSh5() ' ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select Range("A1:D6").Select Selection.Copy Sheets("Sheet5").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False End Sub ================================================== = Sub DeleteConsol() ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select ActiveWindow.SelectedSheets.Delete Sheets("Sheet5").Select Selection.Clear End Sub -- Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
Thanks Bob for the fast response. I seem to have a problem.
When executing, I get a comple error "variable not defined" and "sh2=" (your second line of code) is highlighted in blue. I have also removed a coma after"CopyToSh6". I don't mind continuing to select the files onscreen so long as the conditional posting to the respective worksheets can be done. -- Robert "Bob Phillips" wrote: You can loop through a list like so. I haven't tested this but it seems to be what you ask Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count,"A").End(xlUp).Row For i = 1 To iLastRow sFilename = sh2.Cells(i,"A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) GetData sFilename, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case1 : Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6", Case 3: Application.Run "CopyToSh7" 'etc. End Select End If -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Though I do not know VBA I managed to adapt Ron de Bruins code as follows. It does what I want thus far. I now wish to have an additional condition i.e.IF Consol!D2=1 Application.Run "CopyToSh5" , IF Consol!D2=2, Application.Run "CopyToSh6",IF Consol!D2=3,Application.Run "CopyToSh7", etc upto 10. Any help from the ng will be appreciated.. Also would it be possible to select the files from a list (named range "SALES") in the Main Sheet rather than an onscreen selection Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" For N = LBound(FName) To UBound(FName) Set destrange = sh.Cells(1, 1) GetData FName(N), "Sheet1", "A1:D6", destrange, True Application.Run "CopyToSh5" Next End If ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub ============================= Sub CopyToSh5() ' ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select Range("A1:D6").Select Selection.Copy Sheets("Sheet5").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False End Sub ================================================== = Sub DeleteConsol() ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select ActiveWindow.SelectedSheets.Delete Sheets("Sheet5").Select Selection.Clear End Sub -- Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
You need to declare the extra variables in your code
Dim sh2 As Worksheet Dim iLastRow As Long Add that and try this correction Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow sFileName = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) 'GetData sFileName, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case 1: Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6" Case 3: Application.Run "CopyToSh7" 'etc. End Select Next i -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Thanks Bob for the fast response. I seem to have a problem. When executing, I get a comple error "variable not defined" and "sh2=" (your second line of code) is highlighted in blue. I have also removed a coma after"CopyToSh6". I don't mind continuing to select the files onscreen so long as the conditional posting to the respective worksheets can be done. -- Robert "Bob Phillips" wrote: You can loop through a list like so. I haven't tested this but it seems to be what you ask Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count,"A").End(xlUp).Row For i = 1 To iLastRow sFilename = sh2.Cells(i,"A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) GetData sFilename, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case1 : Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6", Case 3: Application.Run "CopyToSh7" 'etc. End Select End If -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Though I do not know VBA I managed to adapt Ron de Bruins code as follows. It does what I want thus far. I now wish to have an additional condition i.e.IF Consol!D2=1 Application.Run "CopyToSh5" , IF Consol!D2=2, Application.Run "CopyToSh6",IF Consol!D2=3,Application.Run "CopyToSh7", etc upto 10. Any help from the ng will be appreciated.. Also would it be possible to select the files from a list (named range "SALES") in the Main Sheet rather than an onscreen selection Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" For N = LBound(FName) To UBound(FName) Set destrange = sh.Cells(1, 1) GetData FName(N), "Sheet1", "A1:D6", destrange, True Application.Run "CopyToSh5" Next End If ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub ============================= Sub CopyToSh5() ' ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select Range("A1:D6").Select Selection.Copy Sheets("Sheet5").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False End Sub ================================================== = Sub DeleteConsol() ' Macro recorded 11/12/2005 by Robert Sheets("Consol").Select ActiveWindow.SelectedSheets.Delete Sheets("Sheet5").Select Selection.Clear End Sub -- Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
Using new code as below. Error "i" (marked**) "not declared".
When "Dim i As Long" (????) was added, sFileName (next row) became "not declared". Thank you for your patience, perhaps one more try?.Please check if my ending is correct. Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row ** For i = 1 To iLastRow sFileName = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) 'GetData sFileName, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case 1: Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6" Case 3: Application.Run "CopyToSh7" 'etc. End Select Next i End Sub -- Robert |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
Sub GetData_Example3()
Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Dim i As Long Dim sFilename As String Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow sFilename = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) GetData sFilename, "Sheet1", "A1:D6", destrange, True Select Case Worksheets("Consol").Range("D2") Case 1: Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6" Case 3: Application.Run "CopyToSh7" 'etc. End Select Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Using new code as below. Error "i" (marked**) "not declared". When "Dim i As Long" (????) was added, sFileName (next row) became "not declared". Thank you for your patience, perhaps one more try?.Please check if my ending is correct. Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row ** For i = 1 To iLastRow sFileName = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) 'GetData sFileName, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case 1: Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6" Case 3: Application.Run "CopyToSh7" 'etc. End Select Next i End Sub -- Robert |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
Dear Bob after trying many many times with with possible solutions,
have to call it a day. The code still does not work. Could it be that the original code was meant to read from closed files. Actions that took place. Sheet "Consol" was deleted, then window msg "The file name, Sheet or Range is invalid of:1", then runtime error 1004. "sh.name="Consol" highlighted in yellow. A new worksheet gets inserted at each run with the sheet numbers incrementing by 2. Thank you for your efforts. I shall monitor for responses failing which I shall use the original code. Robert "Bob Phillips" wrote: Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Dim i As Long Dim sFilename As String Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow sFilename = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) GetData sFilename, "Sheet1", "A1:D6", destrange, True Select Case Worksheets("Consol").Range("D2") Case 1: Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6" Case 3: Application.Run "CopyToSh7" 'etc. End Select Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Using new code as below. Error "i" (marked**) "not declared". When "Dim i As Long" (????) was added, sFileName (next row) became "not declared". Thank you for your patience, perhaps one more try?.Please check if my ending is correct. Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row ** For i = 1 To iLastRow sFileName = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) 'GetData sFileName, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case 1: Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6" Case 3: Application.Run "CopyToSh7" 'etc. End Select Next i End Sub -- Robert |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
Robert,
The difficulty lies in the code referring to other procedures which you have but we do not. One certainly seems to delete the Consol worksheet (judging by its name), but that does not explain why the sh.Name should fail. Perhaps if you share DeleteConsol, GetData, CopyToSh5, CopyToSh6 andCopyToSh7 macros with us we might be able to test it. -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Dear Bob after trying many many times with with possible solutions, have to call it a day. The code still does not work. Could it be that the original code was meant to read from closed files. Actions that took place. Sheet "Consol" was deleted, then window msg "The file name, Sheet or Range is invalid of:1", then runtime error 1004. "sh.name="Consol" highlighted in yellow. A new worksheet gets inserted at each run with the sheet numbers incrementing by 2. Thank you for your efforts. I shall monitor for responses failing which I shall use the original code. Robert "Bob Phillips" wrote: Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Dim i As Long Dim sFilename As String Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow sFilename = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) GetData sFilename, "Sheet1", "A1:D6", destrange, True Select Case Worksheets("Consol").Range("D2") Case 1: Application.Run " " 'etc. End Select Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Robert" wrote in message ... Using new code as below. Error "i" (marked**) "not declared". When "Dim i As Long" (????) was added, sFileName (next row) became "not declared". Thank you for your patience, perhaps one more try?.Please check if my ending is correct. Sub GetData_Example3() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant, N As Long Dim destrange As Range Dim sh As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Application.Run "DeleteConsol" SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:\Data" ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _ MultiSelect:=True) If IsArray(FName) Then FName = Array_Sort(FName) Application.ScreenUpdating = False Set sh2 = Worksheets("Sheet2") iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row ** For i = 1 To iLastRow sFileName = sh2.Cells(i, "A").Row Set sh = ActiveWorkbook.Worksheets.Add sh.Name = "Consol" Set destrange = sh.Cells(1, 1) 'GetData sFileName, "Sheet1", "A1:D6", destrange, True Select Case Consol!D2 Case 1: Application.Run "CopyToSh5" Case 2: Application.Run "CopyToSh6" Case 3: Application.Run "CopyToSh7" 'etc. End Select Next i End Sub -- Robert |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from closed files and IF statement
Thanks again Bob for the follow up. I dont have a GetData Sub.
The other codes as given below. For testing purposes I had 3 files, Test2.xls, Test3.xls, Test4.xls with just some numbers A2:D6, D2 having 1 to 5 only. The main file had sheets 1, Consol, 5,6,7 What I am attempting is to copy Range A1:D6 from a range of closed files one At a time to Consol!A2. Check for value of D2. If €ś1€ť copy the contents to Sheet5, PasteSpecial Add, If €ś2€ť to Sheet6,PasteSpecial Add, If €ś3€ť to Sheet7 and so on. The paste range of all destination sheets will have to be cleared of historic values at the start of the process. As such Consol need not be deleted and and a new Worksheet entered and renamed. The existing code does that because I adapted (through hours of trial and error) Ron de Bruins to copy data from closed files. His original code copys the data from the closed files (which is a VERY BIG HELP) sequentially downwards in a sheet which I named €śConsol€ť. My desire is to sum the values copied according to different data type, hence the checking in D2. I could use the Paste Link, but I have over 70 source files. Sub CopyToSh5() ' ' CopyToSh5 Macro ' Macro recorded 11/12/2005 by Robert ' ' Sheets("Consol").Select Range("A1:D6").Select Selection.Copy Sheets("Sheet5").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False End Sub Sub CopyToSh6() ' ' CopyToSh6 Macro ' Macro recorded 11/14/2005 by Robert ' Sheets("Consol").Select Range("A1:D6").Select Selection.Copy Sheets("Sheet6").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False End Sub Sub CopyToSh7() ' ' CopyToSh7 Macro ' Macro recorded 11/14/2005 by Robert ' Sheets("Consol").Select Range("A1:D6").Select Selection.Copy Sheets("Sheet7").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False End Sub Sub DeleteConsol() ' ' DeleteConsol Macro ' Macro recorded 11/12/2005 by Robert ' ' Sheets("Consol").Select ActiveWindow.SelectedSheets.Delete Sheets("Sheet5").Select Range("A1:D7").Select Selection.Clear Sheets("Sheet6").Select Range("A1:D7").Select Selection.Clear Sheets("Sheet7").Select Range("A1:D7").Select Selection.Clear Sheets("Sheet2").Select End Sub ======================================== Regards -- Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying cell value to master report file which is closed? | Excel Worksheet Functions | |||
Copying From Closed Workbooks | Excel Worksheet Functions | |||
closed files | Excel Programming | |||
Testing Closed Files | Excel Programming | |||
Copying Data from closed workbooks | Excel Programming |