macro to copy and paste range of cells to variable cell reference

[sgkessel]

Hello... I'm new, need help... and you masters of excel can probably solve this faster than I can compose this post

I need a macro that copies a range of cells... easy
then pastes it... in a column... at the first instance of a 3rd consecutive empty cell.. ie starting from the top of column B and searching til it finds the green cell (b31 in this case) and pasting there... so no matter how many times I press it, it keeps going down to the new set of empty cells... thanks in advance!

 

[tweedle]

something like this maybe...

there are actually two functions shown here: One custom using a top-down approach as requested, and another that uses a bottom up approach which will be faster. See Sub test for example usage.

Function FindTarget(ws As Worksheet) As Range
    'Top-Down Function
    With ws
        For i = 4 To .Rows.Count
            If IsEmpty(Cells(i, 2)) And IsEmpty(Cells(i - 1, 2)) And IsEmpty(Cells(i - 2, 2)) Then
                Set FindTarget = Cells(i, 2)
                Exit Function
            End If
        Next
    End With
End Function

Sub test()
    'Using Top-Down Function
    Range("B3:B10").Copy FindTarget(ActiveSheet)
    
    'Using Bottom-up Function
    With ActiveSheet
        nextrow = Cells(.Rows.Count, 2).End(xlUp).Row + 3
        Cells(nextrow, 2).Select
    End With
End Sub

fyi: I have these in a module

 

[sgkessel]

wow... not 1, but 2 answers to my question... I don't have time tonight, but I will definitely be implementing this soon. Thanks Tweedle!

 

[sgkessel]

ok... that is rad... so stoked you can figure that out in minutes and save me days of tooling around in VBA where I am completely lost... but before leave you... a few questions...

1. After it copies my range... the cell that is active or clicked on or highlighted if you will is the next "3rd empty cell" below when I would be really happy if it was the top left cell of the range I just copied as shown below... that way I can start editing it right away... B31 in this first copy which by my count is 29 cells above it for this example

2. and i copied this right into a module and I am working fine... but is the top-down and bottom-up something something that I need to pick one over the other or can I just leave the code in there as you wrote it... I wasn't sure about that... its greek to me

Either way, this is awesome... I just dont want it to make me start thinking up more ways to make this sheet Im designing any more complicated...
thanks again for all your help.

 

[tweedle]

... but is the top-down and bottom-up something something that I need to pick one over the other ...

pick one over the other. Personally; the bottom up is better in this case as it minimizes risk of having 3 vacant cells get copied on the first pass, and having that get over-laid on the second pass.

Here's a revised Bottom Up method:

Sub TestBottumsUp()

    'Using Bottom-up Function
    With ActiveSheet
        nextrow = Cells(.Rows.Count, 2).End(xlUp).Row + 3 'Figures out where to paste at the bottom
        Selection.Copy Cells(nextrow, 2) 'Copy/Paste
        Selection.Cells(1, 1).Select 'takes you back to your original selection
    End With

End Sub

Either way, this is awesome... I just dont want it to make me start thinking up more ways to make this sheet Im designing any more complicated...

Oh, you will It's inherent.

thanks again for all your help.

N/P

FYI:
Alt F11 to open the VBA editor
F2 Opens the Object Browser
This gives you access to all the properties and methods of all the objects Excel knows about.
Good things to start with:
Worksheets collection
Worksheet
Range [tricky as it's so dynamic]

and four things I use in VBA more than I ever thought I would:
lastrow = Cells(.Rows.Count, 2).End(xlUp).Row
nextrow = Cells(.Rows.Count, 2).End(xlUp).Row + 3
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
nextcol = Cells(2, Columns.Count).End(xlToLeft).Column + 1

 

[sgkessel]

ok... so I'm golden... however... I made a slight error...
it turns out I need to be counting 3rd blank in column C... not far left column of my range... which is column B...
so my code so far is:


Sub test()
Range("B2:g28").Copy FindTarget(ActiveSheet)
With ActiveSheet
nextrow = Cells(.Rows.Count, 2).End(xlUp).Row + 3
Cells(nextrow, 2).Select
ActiveCell.Offset(-29, 0).Select
End With
End Sub

but if my range looks like this:

it copies over not below and jacks everything up...

can you augment my code for the column shift.

Thanks... then I am straight...

and hopefully from seeing the two different codes can determine a bit of the code language... because trust me I tried to fix it on my own...

 

[tweedle]

I don't understand...

can you restate what you want this to do?

 

[sgkessel]

sorry man, I get long winded...
basically I just want to copy/paste the range shown into the third next fully empty row
I had explained this in terms of the column B and it having a non-empty cell to count from...

but looking back at my worksheet... column B has a ton of empty cells at the bottom and what I had meant was to reference column C...

I hope that is clear... my latest pic shows that the current formula would paste the range back inside of the range itself, not below it given the layout

thanks for any help you can give me

 

[sgkessel]

wow... I just saw that column C has two empty cells in the bottom which is only adding to the confusion... please consider them full... imagine all of column C is full... hope that better explains it...

 

[tweedle]

Let's try this....

Sub TestBottumsUp()

    'Using Bottom-up Function
    With ActiveSheet
        nextrow = Cells(.Rows.Count, "C").End(xlUp).Row + 3 'Figures out where to paste at the bottom
        Range("B2:g28").EntireRow.Copy
        Rows(nextrow).Select
        ActiveSheet.Paste 'Paste
        Application.CutCopyMode = False 'turns off the selection rectangle
        Range("B2").Select 'takes you back to your original selection
    
    End With

End Sub

my latest pic shows that the current formula would paste the range back inside of the range itself

This was the problem with the top down searching for where to paste, as I warned earlier.