Sub dowhile()
Dim x As Integer, x1 As Integer, row As Integer, row1 As Integer
Dim col As Integer, col1 As Integer, col2 As Integer, col3 As Integer, col4 As Integer, t As Integer, t1 As Integer
Dim y As String, z As String, x4 As String, w As String
t = Workbooks("weeksumry.xlsm").Worksheets("sheet1").Range("h1").Value + 1
row = t 'pointer to next available row in weeksumry
col = 1
z = ThisWorkbook.Worksheets("Order Form").Range("a6") 'Read agency name
'col4 = 4
'This code module creates a summary sheet for weekly deliveries to various welfare agencies around our valley. We make an
'excel delivery sheet
'for each agency as we deliver to them, and I'm trying to summarize our weekly deliveries from those sheets for inventory control and etc..
'The delivery sheets consist, first, of the agency name info followed by item quanities and descriptions. There are 12 or fewer items
'per sheet. The delivery sheet info is concatenated into four columns on the summary sheet. The agency info requires more space so needs
'to go into merged cells. So then the agency info is in the merged cells followed by the item quanities and their descriptions'
HERE IS MY PROB. - the code in the next line properly merges cells a1:d1 which holds the agency name for the first delivery
'sheet processed but I need to change it/something to format/merge the cells used for subsequent agency names as their sheets are
'processed. The deliveries are not all the same length so can't just format every nth row. Some agencies order more stuff than others.
'The variable h1 is a counter placed on the "sumry" sheet which points to the last used row, so I add one to point to the next row available.
'I'll strive for more coding elegance after I get this $*@%#& thing working. I now open each delivery sheet file manually. I'll next try to
'step through the folder and open and process each file automatically. Typically about a dozen delivery files per week.
Workbooks("weeksumry.xlsm").Worksheets("sheet1").Range("a1:d1").Select 'THIS IS THE LINE I CAN'T FIX!
With Selection
.ColumnWidth = 4
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.MergeCells = True '''Not through w this -need more rows to further adj col widths - later!
End With
Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row, col).Value = z 'Write agency name to weeksumry
For row = 8 To 30 'Read all the rows on thisworkbook
col1 = 1 'col* point to source columns in source file
col2 = col + 1
col3 = col + 2
col4 = col1 + 3
'Read columns from source file
x1 = ThisWorkbook.Worksheets("Order Form").Cells(row, col1).Value
x2 = ThisWorkbook.Worksheets("Order Form").Cells(row, col2).Value
x4 = ThisWorkbook.Worksheets("Order Form").Cells(row, col4).Value
x3 = x1 + x2
row1 = row - 7 + t 'Row number on destination page
If x1 + x2 = 0 Then GoTo continue
Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col1).Value = x1 'Write values to weeksumry
Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col2).Value = x2
Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col3).Value = x3
Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col4).Value = x4
continue:
row = row + 1 'Adjust row to skip empty source file row
Next row 'Read and copy next row
End Sub
Thank you, thank you for any suggestions.
Bookmarks