loop is driving me "nuts"

[KatyJohn]

Hey Folks

I'm having a difficult time getting a loop to work. Problem is that I can't resolve the loops with a proper ending and this is stopping me from stepping through the macro to see if it actually works.

The objective of this macro is to loop through 4 or 5 sheets in a workbook(NewOpenFile). The macro should check a cell ("C3") in each sheet and if the cell contains the text "Company" another array loop copies a column of cell text from a sheet in (NewOpenFile) that meets the criteria and copies the text to a master workbook call "2009 Quote Form"

Here is the code:

Private Sub CommandButtonModHead_Click()

Dim NewOpenFile As String
Dim FromRng, ToRng
Dim i As Integer
Dim x As Integer
NewOpenFile = Range("BG21")

Workbooks(NewOpenFile).Activate
For i = 1 To Workbooks(NewOpenFile).Sheets.Count

If Workbooks(NewOpenFile).Sheets(i) = True Then

For Each sht In Workbooks(NewOpenFile).Sheets
If Range("C3") = "company" Then
With sht



FromRng = Array("D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10")
ToRng = Array("C25", "C26", "C27", "C28", "C29", "C30", "C31", "C32")
For x = LBound(FromRng) To UBound(FromRng)

Workbooks(NewOpenFile).Sheets("Module Number Entry").Range(FromRng(x)).Copy
Workbooks("2009 Quote Form.xlsm").Sheets(i).Range(ToRng(x)).PasteSpecial Paste:=xlPasteValues



End With


Next x

End If

Sheets(i).Range("C3") = " "

Next

End Sub



Thanks in advance
katyjohn

 

[KatyJohn]

I had the wrong copy and paste sequence.

Workbooks(NewOpenFile).Sheets(i).Range(FromRng(x)).Copy
Workbooks("2009 Quote Form.xlsm").Sheets("Module Number Entry").Range(ToRng(x)).PasteSpecial Paste:=xlPasteValues

 

[Norie]

Katy

Why are you looping through the worksheets twice?

And why are the to/from ranges stored in arrays?