Hi,
Please can someone help me.
I have setup a workbook as follows:
Receipt Page which creates a table of data based on input data.
This creates a maximum possible range to copy of A2:I20
every cell in this range contains formulas although some may return a blank cell eg. (=IF(B3<>"",B3,""))
My macro then selects this range and copies it to the 'Data' sheet and paste values it on to the next available row.
It does work apart from copying the blank rows as well (As the range states)
The 'Data' sheet has a formula in column A that converts the date (first cell of the pasted data) into a calendar month, hence the offset in my code.
I thought I had got around this by doing a sort at the end of the pasting, only to realise down the line that these rows are being pushed to the end of the data and are not treated the same as truly blank rows.
i need it to only copy the rows that have values and not formulas,
Summary of actions
Person inputs sales info into a receipt template which is printed out and given to customer (Could be 1 to 20 items on different lines)
Relevant Data is taken from this and arranged in a table via formula which tidies it up
Macro copies table and pastes it into Data sheet which is a record of all transactions
Macro then sorts Data by date (in the hope of removing blank rows copied over)
Macro then clears the sales data from the receipt template
Macro then moves the receipt number on 1
I hope I've given enough info for someone to be able to help me as I've spent untold hours on this and am feeling rather fed up now.
See Code
' Macro1 Macro
'
'
Sheets("Receipt").Range("O14:AB31").Copy
Sheets("Data").Range("A5000").End(xlUp).Offset(1, 1).PasteSpecial _
Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("Data").Select
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("A:O").Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C2:C5000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:O5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Range("A1").Select
Sheets("Receipt").Select
Range("E11").Select
Selection.ClearContents
Range("g11").Select
Selection.ClearContents
Range("E8:J10").Select
Selection.ClearContents
Range("B14:B31").Select
Selection.ClearContents
Range("H14:H30").Select
Selection.ClearContents
Range("g35:G35").Select
Selection.ClearContents
Range("d29:d30").Select
Selection.ClearContents
Range("k29:k30").Select
Selection.ClearContents
Range("H31").Select
Selection.ClearContents
Range("L8").Select
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 + 1
Range("A1").Select
ThisWorkbook.Save
End With
End Sub
Please can someone help me.
I have setup a workbook as follows:
Receipt Page which creates a table of data based on input data.
This creates a maximum possible range to copy of A2:I20
every cell in this range contains formulas although some may return a blank cell eg. (=IF(B3<>"",B3,""))
My macro then selects this range and copies it to the 'Data' sheet and paste values it on to the next available row.
It does work apart from copying the blank rows as well (As the range states)
The 'Data' sheet has a formula in column A that converts the date (first cell of the pasted data) into a calendar month, hence the offset in my code.
I thought I had got around this by doing a sort at the end of the pasting, only to realise down the line that these rows are being pushed to the end of the data and are not treated the same as truly blank rows.
i need it to only copy the rows that have values and not formulas,
Summary of actions
Person inputs sales info into a receipt template which is printed out and given to customer (Could be 1 to 20 items on different lines)
Relevant Data is taken from this and arranged in a table via formula which tidies it up
Macro copies table and pastes it into Data sheet which is a record of all transactions
Macro then sorts Data by date (in the hope of removing blank rows copied over)
Macro then clears the sales data from the receipt template
Macro then moves the receipt number on 1
I hope I've given enough info for someone to be able to help me as I've spent untold hours on this and am feeling rather fed up now.
See Code
' Macro1 Macro
'
'
Sheets("Receipt").Range("O14:AB31").Copy
Sheets("Data").Range("A5000").End(xlUp).Offset(1, 1).PasteSpecial _
Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("Data").Select
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("A:O").Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C2:C5000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:O5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Range("A1").Select
Sheets("Receipt").Select
Range("E11").Select
Selection.ClearContents
Range("g11").Select
Selection.ClearContents
Range("E8:J10").Select
Selection.ClearContents
Range("B14:B31").Select
Selection.ClearContents
Range("H14:H30").Select
Selection.ClearContents
Range("g35:G35").Select
Selection.ClearContents
Range("d29:d30").Select
Selection.ClearContents
Range("k29:k30").Select
Selection.ClearContents
Range("H31").Select
Selection.ClearContents
Range("L8").Select
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 + 1
Range("A1").Select
ThisWorkbook.Save
End With
End Sub