Macro to copy paste special ignoring formulas in cells

[geminian]

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

 

[oldbrewer]

say your data was only A1 to C3
and the formulas in A2 B2 C2 return blanks
add a helper column D and put D1 D2 D3 =1
copy paste special values to a new sheet
now sort by col D - this will push all blank rows to the bottom

 

[geminian]

say your data was only A1 to C3
and the formulas in A2 B2 C2 return blanks
add a helper column D and put D1 D2 D3 =1
copy paste special values to a new sheet
now sort by col D - this will push all blank rows to the bottom

Hi
Thankyou for the reply but the macro already does do this.
The trouble is that the blank cells pushed to the bottom are not really blank.

I need the macro to only select the range with values and to ignore formulas

 

[MARK858]

Seeing as your sort was on Column C the code below will give you the last cell in column C ignoring cells containing "" (empty strings) .

Dim lr as Long
lr = Worksheets("Data").Columns(3).Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row

You can then use it to help define the range.
i.e something like...

MsgBox "the range is " & Range("A1:O" & lr).Address

 

[oldbrewer]

a cell with a formula returning a blank - if copied, paste special values will be a blank and will be sorted as text

 

[geminian]

Thankyou,
I'm not after sorting the data, I want to remove that.
I put this in as a get around to the macro pasting 1 row of data and 19 rows of blank because the next time it pastes data it will skip these 19 blank rows.

Thus,
currently without the sort, I have 1 row of data, 19 blank rows, 1 row of data etc.

I want the macro to copy only rows with values in Column O from the data sheet, then I can remove the sort get around from the macro.
Its pasting something into the 19 rows because when I get to the end of my values on the data page and then do a SHIFT END Down Arrow, it takes me exactly to the end of 20 rows.
If I highlight and press delete on these rows to clear contents and then do the SHIFT END Down Arrow, it takes me to the end of the worksheet.

The issue is that over the last 5 months the sheet has become very large, when I delete these it reduced the size of the workbook by 90%

Thankyou

 

[oldbrewer]

if you have data in rows 1 to 3 and blanks in rows 4 to 10

=counta(A1:A10)+1 returns 4 which is the row to paste new data in

so in macro cells(1,6).select (assumes counta formula is in F1) and then code for paste operation

 

[MARK858]

if copied, paste special values will be a blank

Hmm...

Sub xxxk()
Range("a1:a5").Value = 111
Range("c1:c10").Formula = "=IF(A1<>"""",A1,"""")"
Range("C1:aC10").Copy
Range("E1").PasteSpecial xlPasteValues
Range("F1:F10").Formula = "=ISBLANK(E1)"
End Sub

What is the result in the ISBLANK formulas in F6:F10 for you?

admittedly for me it is not an "" result from a formula but it is not Blank

and for me

Sub SSS()
Dim lr As Long
lr = ActiveSheet.Columns(5).Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row
MsgBox "the range is " & Range("E1:E" & lr).Address
End Sub

still picks up the range required.

 

[geminian]

Hi Mark,
Thankyou..
I'm struggling to see the connection with my ranges so I'm going to put this into a new workbook and try it out.
Cheers

 

[MARK858]

I'm struggling to see the connection with my ranges

The last post was aimed at Oldbrewer to test to see if he/she got the same results (as per the quote). The code in post #4 was based on you sorting the data based on column C as per your original code.

From your original code...

ActiveWorkbook.Worksheets("Data").Sort.SortFields.[B]Add Key:=Range("C2:C5000") [/B]_
 , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Data").Sort