So I have this script....with the help of forum...
Public Sub Copy_From_Daily_Source_File()
' I have assumed that if the program comes across 15 blank rows ,
' it means there is no more data to be processed ; I have used 15 as the limit
' because in the existing sheet , the maximum number of blank rows is 10.
Const MAX_BLANK_ROWS = 15
Const COLUMNS_TO_BE_COPIED = 7
Const ACCOUNT_TITLE = "ACCT:"
Workbooks("Template.xlsm").Worksheets("data").Activate
Set origin = Workbooks("Template.xlsm").Worksheets("data").Range("A5")
Workbooks("DAILY-SOURCE-FILE.xlsm").Worksheets("ckreg").Activate
Set sfn = Workbooks("DAILY-SOURCE-FILE.xlsm").Worksheets("ckreg").Range("A1")
curr_stock_num = ThisWorkbook.Worksheets("FORM").Range("Stockcell")
If curr_stock_num > 0 Then
Application.ScreenUpdating = False
num_of_blank_rows = 0
row_counter = 0
Do
' Select the source file
Workbooks("DAILY-SOURCE-FILE.xlsm").Activate
sfn.Select
ccv = ActiveCell.Offset(row_counter, 0).Value
' Stock number matches
If ccv = curr_stock_num Then
' We need to copy the stock data from the daily source file
' to the sheet labelled "data" in the Template.xlsm file
ActiveCell.Offset(row_counter, 0).Resize(1, COLUMNS_TO_BE_COPIED).Copy
Workbooks("Template.xlsm").Activate
origin.Select
' We need to check the last row of data already present in the sheet
' and go the next row , which will be the first blank row
' available for pasting new data.
' If the first column is blank , it is assumed the entire row is blank.
If ActiveCell = "" Then
ActiveCell = saved_acct
ActiveCell.Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
Else
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(1, 0) = saved_acct
ActiveCell.Offset(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
Else
ActiveCell.End(xlDown).Offset(1, 0) = saved_acct
ActiveCell.End(xlDown).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
End If
End If
Else
' Stock number does not match ; check whether the row is blank or not
If ccv = "" Then
num_of_blank_rows = num_of_blank_rows + 1
Else
num_of_blank_rows = 0
If Left(Trim(ccv), 5) = ACCOUNT_TITLE Then
saved_acct = ccv
End If
End If
End If
row_counter = row_counter + 1
Loop Until num_of_blank_rows >= MAX_BLANK_ROWS
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
Set origin = Nothing
Set sfn = Nothing
End Sub
But I keep getting a subscript out of range" error when I run the macro....
any ideas?? Please help
thank you all
