Macro to paste value into first Blank Cell in a range of Cells

[FrankMcNally]

O.K. I have the following Code which takes data from Worksheet CDR1001 (data generated from a report) and pastes it into Sheet1 (for the time being, it will have to be pasted into another workbook when fixed!!) in the correct columns.

Credit Entries
Item# and Values will be copied/pasted into Cols A & B if the Item is <132 (values will be converted to a negative)
Item # and Values will be copied/pasted into Cols D & E if the Item is =132 (values will be converted to a negative)
Item # and Values will be copied/pasted into Cols G & H if the Item is >132 (values will be converted to a negative)

Debit Entries
Item# and Values will be copied/pasted into Cols A & B if the Item is <132
Item # and Values will be copied/pasted into Cols D & E if the Item is =132
Item # and Values will be copied/pasted into Cols G & H if the Item is >132

What I'm having problem with is the Paste Area must be the first Blank Cell in the Column, in a range. i.e. A2:A25, B2:B25,D2:D25, etc

Right now as it stands it copies everything to a similar row as the Source Data, hence the 'CDR_Count' Variable.

Anybody have any ideas??

The Code looks long as I have repeat the same steps six times.

           If Cells(CDR_Count, 4) = "CR" Then
' This moves Credits for Accounts 15, 35, etc to RG-GL Side and makes them Negative
                    If Cells(CDR_Count, 1) < 132 Then
                    Range("A" & CDR_Count).Copy
                    Sheets("Sheet1").Range("A" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=True, Transpose:=False
                    Range("C" & CDR_Count).Copy
                    Sheets("Sheet1").Range("b" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=True, Transpose:=False
                    If Sheets("Sheet1").Range("B" & CDR_Count) > 0 Then
                    Sheets("Sheet1").Range("B" & CDR_Count).Value = (Sheets("Sheet1").Range("B" & CDR_Count).Value) * -1
                    End If
                End If
' This moves Credits to the Account 132 to the RG-GL side and makes Negative
                        If Cells(CDR_Count, 1) = 132 Then
                        Range("A" & CDR_Count).Copy
                        Sheets("Sheet1").Range("D" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=True, Transpose:=False
                        Range("C" & CDR_Count).Copy
                        Sheets("Sheet1").Range("E" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=True, Transpose:=False
                        If Sheets("Sheet1").Range("E" & CDR_Count) > 0 Then
                        Sheets("Sheet1").Range("E" & CDR_Count).Value = (Sheets("Sheet1").Range("E" & CDR_Count).Value) * -1
                        End If
                    End If
' This moves Credits to the Account 401-409 to the RG-GL side and makes Negative
                                If Cells(CDR_Count, 1) > 132 Then
                                Range("A" & CDR_Count).Copy
                                Sheets("Sheet1").Range("G" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=True, Transpose:=False
                                Range("C" & CDR_Count).Copy
                                Sheets("Sheet1").Range("H" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=True, Transpose:=False
                                If Sheets("Sheet1").Range("H" & CDR_Count) > 0 Then
                                Sheets("Sheet1").Range("H" & CDR_Count).Value = (Sheets("Sheet1").Range("H" & CDR_Count).Value) * -1
                                End If
                            End If
Else
' This moves Debits for Accounts 15, 35, etc to RG-GL Side
                    If Cells(CDR_Count, 1) < 132 Then
                    Range("A" & CDR_Count).Copy
                    Sheets("Sheet1").Range("A" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=True, Transpose:=False
                    Range("C" & CDR_Count).Copy
                    Sheets("Sheet1").Range("b" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=True, Transpose:=False
                End If
' This moves Debits for Accounts 132 etc to RG-GL Side
                    If Cells(CDR_Count, 1) = 132 Then
                        Range("A" & CDR_Count).Copy
                        Sheets("Sheet1").Range("D" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=True, Transpose:=False
                        Range("C" & CDR_Count).Copy
                        Sheets("Sheet1").Range("E" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=True, Transpose:=False
                End If
' This moves Debits for Accounts 401-409 etc to RG-GL Side
                    If Cells(CDR_Count, 1) > 132 Then
                                Range("A" & CDR_Count).Copy
                                Sheets("Sheet1").Range("G" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=True, Transpose:=False
                                Range("C" & CDR_Count).Copy
                                Sheets("Sheet1").Range("H" & CDR_Count).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=True, Transpose:=False
                End If
        End If
Next
End Sub

 

[FrankMcNally]

It's too bad this does have an edit mode after posting!! Ah well I just thought I should add that I need to paste these vales in a particular range as it's going into a set format with a top line and a sum command at the bottom of each column which is why any Last.Row or Count.Row functions don't work very well.

 

[FrankMcNally]

Really?? No-one has any thoughts??