Hey Folks
I'm having problems getting the loop below to work. My objective is to go through a series of sheets and once a cell is identified as "Company" another loop fills in information from another page.
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 Sheets(i).Range("C3") = "Company" Then
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
Next x
End If
Sheets(i).Range("C3") = " "
Next
End Sub
katyjohn
I'm having problems getting the loop below to work. My objective is to go through a series of sheets and once a cell is identified as "Company" another loop fills in information from another page.
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 Sheets(i).Range("C3") = "Company" Then
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
Next x
End If
Sheets(i).Range("C3") = " "
Next
End Sub
katyjohn