cchris_uk
02-11-2008, 05:13 AM
Hi again!
I have 4 variables,
dataRowStart (First row of wanted data - manually entered into a userform)
dataRowEnd (This is obtained by finding the last row containing data)
dataColumnStart (First column, usually 'C' but may change, so this is a manual entry via userform)
dataColumnEnd (Operator selects how many columns there are, so for example, if they enter '4', the script needs to make the calculation to determine the last column, eg, dataColumnStart = C, therefore 4 columns starting from 'C' means last column is 'F')
These are setup by the operator using a userform.
An example of userfom data is:
dataRowStart = 7
dataColumnStart = C
dataColumnCount = 4
dataEndRow = 135
I currently have the following script (4 'with'/'end with' loops that import individual columns before a 'Do While' loop loads up the next spreadsheet):
Do While mpFile <> ""
Set mpNewWB = Workbooks.Open(reportloc & "\" & mpFile)
mpNextCol = 1
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("C7:C135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNextCol = 1
mpNextLine = mpNextLine + 2
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("D7:D135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNextCol = 1
mpNextLine = mpNextLine + 2
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("E7:E135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNextCol = 1
mpNextLine = mpNextLine + 2
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("F7:F135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNewWB.Close
mpNextLine = mpNextLine + 2
mpFile = Dir
Loop
What I need to happen is:
Where the ranges for the data are currently set via hardcode, they should be calculated from the entries made in the userform.
The 'with' loops could also be reduced to just one loop that updates itself within another loop dependant on how many 'columns' of data are required.
Regards,
Chris:think:
I have 4 variables,
dataRowStart (First row of wanted data - manually entered into a userform)
dataRowEnd (This is obtained by finding the last row containing data)
dataColumnStart (First column, usually 'C' but may change, so this is a manual entry via userform)
dataColumnEnd (Operator selects how many columns there are, so for example, if they enter '4', the script needs to make the calculation to determine the last column, eg, dataColumnStart = C, therefore 4 columns starting from 'C' means last column is 'F')
These are setup by the operator using a userform.
An example of userfom data is:
dataRowStart = 7
dataColumnStart = C
dataColumnCount = 4
dataEndRow = 135
I currently have the following script (4 'with'/'end with' loops that import individual columns before a 'Do While' loop loads up the next spreadsheet):
Do While mpFile <> ""
Set mpNewWB = Workbooks.Open(reportloc & "\" & mpFile)
mpNextCol = 1
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("C7:C135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNextCol = 1
mpNextLine = mpNextLine + 2
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("D7:D135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNextCol = 1
mpNextLine = mpNextLine + 2
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("E7:E135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNextCol = 1
mpNextLine = mpNextLine + 2
With mpThisWB.Worksheets(1)
For Each mpCell In mpNewWB.Worksheets(1).Range("F7:F135")
.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1
Next mpCell
End With
mpNewWB.Close
mpNextLine = mpNextLine + 2
mpFile = Dir
Loop
What I need to happen is:
Where the ranges for the data are currently set via hardcode, they should be calculated from the entries made in the userform.
The 'with' loops could also be reduced to just one loop that updates itself within another loop dependant on how many 'columns' of data are required.
Regards,
Chris:think: