Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
HI ALL
I have this macro whitch copy antire row from one wbook to another If a value is =x ;it works with 1 workbook : PART 1 OF TOPIC ! Option Explicit Sub Copy_Ranges() Dim FromWks As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng As Range Set FromWks = Workbooks("YTA1.xls").Worksheets("sheet1") Set DestWks = Workbooks("R1.xls").Worksheets("sheet1") With FromWks Set myRng = .Range("BD91", .Cells(.Rows.Count, "BD").End(xlUp)) End With For Each myCell In myRng.Cells If myCell.Value <= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy _ Destination:=.Cells(NextRow, "A") End With End If Next myCell End Sub PART 2 OF TOPIC . I change this macro for loop in 3 steps ; the macro becomed so : Option Explicit Sub Copy_Ranges() Dim FromWks As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng1 As Range Dim myRng2 As Range Dim myRng3 As Range Set FromWks = Workbooks("Registru1.xls").Worksheets("1") Set DestWks = Workbooks("R1.xls").Worksheets("1") With FromWks Set myRng1 = .Range("BD91:BD22000") End With With FromWks Set myRng2 = .Range("BD22001:BD44000") End With With FromWks Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value =33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value =33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value =33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell End Sub PART 3 OF TOPIC . For open 3 workbook this macro and work with them , I ' ve done this changes : Option Explicit Sub Copy_Ranges() Dim FromWks1 As Worksheet Dim FromWks2 As Worksheet Dim FromWks3 As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng1 As Range Dim myRng2 As Range Dim myRng3 As Range Workbooks.Open ("D:\WAVE\YTA1.xls") Set FromWks1 = Workbooks("YTA1.xls").Worksheets("1") Set DestWks = Workbooks("R1.xls").Worksheets("1") With FromWks1 Set myRng1 = .Range("BD91:BD22000") End With With FromWks1 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks1 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C44001:BB65536").Select Selection.ClearContents Workbooks("YTA1.xls").Close SaveChanges:=False Workbooks.Open ("D:\WAVE\YTA2.xls") Set FromWks2 = Workbooks("YTA2.xls").Worksheets("1") With FromWks2 Set myRng1 = .Range("BD91:BD22000") End With With FromWks2 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks2 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C44001:BB65536").Select Selection.ClearContents Workbooks("YTA2.xls").Close SaveChanges:=False Workbooks.Open ("D:\WAVE\YTA3.xls") Set FromWks3 = Workbooks("YTA3.xls").Worksheets("1") With FromWks3 Set myRng1 = .Range("BD91:BD22000") End With With FromWks3 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks3 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C44001:BB65536").Select Selection.ClearContents Workbooks("YTA3.xls").Close SaveChanges:=False End Sub PART 4 OF TOPIC . I want to work with 10 workbooks in one macro . Name of this workbooks is from YTA1 to YTA10 . MY PROBLEM IS : HOW CAN I declare the variables for all workbooks ?THE ONLY PROBLEM IS TO DECLARE VARIABLES IN THIS MACRO ! Please don't impresionate for the big size of this macro , only problem is to declare variables for the 10 workbooks and them Rng1 , Rng2 and Rng3 ! ! Something I write wrong , because after first workbook the macro don't work with speed !! It copy entire row in the other workbook very slow ! (ps:I have 231 workbooks , named from YTA1 to YTA231, but I think it cann't be posible to work with all them in one only macro [ a macro cann't be bigger of 64 kb ! ]) Any sugestion will be wellkome ! Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
Hi ytayta555;
This may not help but i'l take a shot at it. My first suggestions would be to switch to a database solution. If you have that much data is seems like it might be time to to do that. Second, have you tried going back and forth between the workbooks by using the Windows.Workbooks("xxx").Activate method. Third, you might could bring all the worksheets into one workbook instead of so many workbooks. Fourth you might use linking to have all 200 + workbook answer sheets link into a master workbook. I hope it helps and good luck, "ytayta555" wrote: HI ALL I have this macro whitch copy antire row from one wbook to another If a value is =x ;it works with 1 workbook : PART 1 OF TOPIC ! Option Explicit Sub Copy_Ranges() Dim FromWks As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng As Range Set FromWks = Workbooks("YTA1.xls").Worksheets("sheet1") Set DestWks = Workbooks("R1.xls").Worksheets("sheet1") With FromWks Set myRng = .Range("BD91", .Cells(.Rows.Count, "BD").End(xlUp)) End With For Each myCell In myRng.Cells If myCell.Value <= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy _ Destination:=.Cells(NextRow, "A") End With End If Next myCell End Sub PART 2 OF TOPIC . I change this macro for loop in 3 steps ; the macro becomed so : Option Explicit Sub Copy_Ranges() Dim FromWks As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng1 As Range Dim myRng2 As Range Dim myRng3 As Range Set FromWks = Workbooks("Registru1.xls").Worksheets("1") Set DestWks = Workbooks("R1.xls").Worksheets("1") With FromWks Set myRng1 = .Range("BD91:BD22000") End With With FromWks Set myRng2 = .Range("BD22001:BD44000") End With With FromWks Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value =33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value =33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value =33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell End Sub PART 3 OF TOPIC . For open 3 workbook this macro and work with them , I ' ve done this changes : Option Explicit Sub Copy_Ranges() Dim FromWks1 As Worksheet Dim FromWks2 As Worksheet Dim FromWks3 As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng1 As Range Dim myRng2 As Range Dim myRng3 As Range Workbooks.Open ("D:\WAVE\YTA1.xls") Set FromWks1 = Workbooks("YTA1.xls").Worksheets("1") Set DestWks = Workbooks("R1.xls").Worksheets("1") With FromWks1 Set myRng1 = .Range("BD91:BD22000") End With With FromWks1 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks1 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C44001:BB65536").Select Selection.ClearContents Workbooks("YTA1.xls").Close SaveChanges:=False Workbooks.Open ("D:\WAVE\YTA2.xls") Set FromWks2 = Workbooks("YTA2.xls").Worksheets("1") With FromWks2 Set myRng1 = .Range("BD91:BD22000") End With With FromWks2 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks2 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value = 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
On 14 Mar, 03:50, DownThePaint
wrote: Hi ytayta555; Thank for sugestion Dawn ! Third, you might could bring all the worksheets into one workbook instead of so many workbooks. Fourth you might use linking to have all 200 + workbook answer sheets link into a master workbook. I can't do this because my powerless resource sistem ...256mb , and it don't open a wbook such that ; every wbook have now 10 mb ; Second, have you tried going back and forth between the workbooks by using the Windows.Workbooks("xxx").Activate method I don't know to work with this metod ..... If you have that much data is seems like it might be time to to do that. . it take 3 sessions of 2,5 hours This may not help but i'l take a shot at it. My first suggestions would be to switch to a database solution. how look a database solution ?.... I want to work in excel and VBA ... Now , I have some macros which work with a half of this workbooks (108 wbooks) ; with the macro I posted here , I can work in all 231 workbooks , but, I tried to get here the right declare of variables , and to find where the macro is wrong that it after first workbook don't have the same speed of copy entire rows from one wbook to another according to the criteria =32; Many thanks for your sugestions , Dawn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
On 14 Mar, 03:50, DownThePaint
wrote: Hi ytayta555; *Second, have you tried going back and forth between the workbooks by using the Windows.Workbooks("xxx").Activate method. * I forget to explain , my sistem is powerless and I must open and close every workbook before I begin the querry in it ; I can't open first 10 workbooks , for example ; Respectfully |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
Hi ytayta55,
From a brief look at your code & requirements you need to loop through the 231 workbooks. Try including something like this: For Each i From 1 to 231 Set FromWks = Workbooks("YTA" & i & ".xls").Worksheets("sheet1") existing code<<< Next i Also put his at the beginnning of your code to speed things up: Application.ScreenUpdating = False Application.Calculation = xlManual Also much of your code is inefficient, you should always try to avoid selecting, so replace this type of statement: Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents With: Sheets("1").Range("C22001:BB44005").ClearContents HTH Michael |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
Hi ytayta55,
Sorry typo in original reply: For Each i = 1 to 231 Set FromWks = Workbooks("YTA" & i & ".xls").Worksheets("sheet1") existing code<<< Next i Michael |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
Must be a bad morning, sorry typo in reposting:
For i = 1 to 231 * *Set FromWks = Workbooks("YTA" & i & ".xls").Worksheets("sheet1") * *existing code<<< Next i Michael |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
DECLARE VARIABLES PROBLEM
Also much of your code is inefficient, you should always try to avoid
selecting, so replace this type of statement: Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents With: Sheets("1").Range("C22001:BB44005").ClearConten ts HTH Michael Thank you very much Michael , this really helped me ! The macro work with this faster ! Excuse me for late feedback/reply . Must be a bad morning, sorry typo in reposting: For i = 1 to 231 Set FromWks = Workbooks("YTA" & i & ".xls").Worksheets("sheet1") existing code<<< Next i Michael This doesn't help me very much because the workbooks are not open ; Know somebady if it is posiblle to combin this function (,,for ... to ") with ,,OPEN" Method ?? Thank ypu very much again and apologise for late feedback . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declare Variables in Array | Excel Discussion (Misc queries) | |||
Declare and Set Public variables | Excel Discussion (Misc queries) | |||
declare multiple variables | Excel Programming | |||
How to efficiently declare variables | Excel Discussion (Misc queries) | |||
Best place to declare variables | Excel Programming |