macro to copy changing range to next blank cell in another sheet

**d2whales** · 07-03-2013, 06:51 PM

Hi-

I'm building part of a macro in which I want to copy and paste a filtered range (whose size changes with each run) to the next blank cell after cell A24 in another worksheet. The next blank cell is always changing.

I'm an Excel novice compared to most of you and record most of my macros. I have a rudimentary understanding of VBA and need something simple. I'm hoping to find a way to use END+DOWN in the macro but don't know how to indicate "wherever" the next blank cell in the column is after cell A24.

Any help is much appreciated!

**Richard Buttrey** · 07-03-2013, 07:07 PM

Hi

Assuming that underneath the next blank cell after A24 there are no other cells with data then the usual way is to use

Range("A" & Rows.Count).End(xlUp).Cells(2,1)

And as an aside re copying your filtered range, don't forget you can always use code like

Formula:

Range("A1").CurrentRegion.Resize(,1).SpecialCells(xlCellTypeVisible)

But better still use a dynamic range name that results in a single column. e.g

Range("MyDynamicRangeName").SpecialCells(xlCellTypeVisible)

**d2whales** · 07-03-2013, 07:38 PM

Hi Richard,

This is what I was afraid of--looking like an idiot. I don't know what that means.

Can you tell me what this code actually means in English: Range("A" & Rows.Count).End(xlUp).Cells(2,1)?

You are correct in assuming there would be no data below the next blank cell after A24. Here is the little part of the macro I'm working on. When I run it like this, I get a Syntax Error.

Range("A19").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("ADULT FICTION").Select
Range("A" & Rows.Count).End(xlUp).Cells(2,1)
ActiveSheet.Paste
Range("A24").Select
Sheets("ADULT SORT").Select
Application.CutCopyMode = False

Thanks, Richard!

**Richard Buttrey** · 07-04-2013, 04:54 AM

Hi,

Try the following if your data is all values rather than formulae.

Sub test()
Range(Sheets("Adult Sort").Range("A19"), Sheets("Adult Sort"). _
Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Adult Fiction").Range("A24")
End Sub

If the data are formulae and you want values then

Sub test()
Range(Sheets("Adult Sort").Range("A19"), Sheets("Adult Sort"). _
Range("A" & Rows.Count).End(xlUp)).Copy 

Sheets("Adult Fiction").Range("A24").PasteSpecial(xlPasteValues)
End Sub

I had originally thought that since you mentioned the next blank cell that you perhaps wanted to copy that blank cell too. Hence the reference to .Cells(2,1). I suspect that you only want to copy down to the last value in the range so the above does not need the .Cells(2,1) reference.

In the original formula the
Range("A" & Rows.Count)
evaluates to
Range("A1048576")
since there are 1048576 rows on Excel 2007/2010
The .End(xlUp) simply moves upwards from row 1048576 until it finds the next non blank value.
The .Cells(2,1) then just identifies a cell which is the second row and first column away from the reference cell. So for instance if the .End(xlUp) were to find cell A45, then adding .Cells(2,1) to the instruction will position you at A46. A .Cells(4,3) would position you at C49.

However why don't you upload your workbook and explain your ultimate goal. The fact that you have a sheet named Sort makes me think you may be able to achieve your aim without necessarily copying data elsewhere. It's often the case that people ignore Excel's ability to Filter or sort data in place.

**d2whales** · 07-05-2013, 07:08 PM

Hi,

Attaching: copy of sheets in question (copied to Excel '03 because I'm not on my own computer at the moment). The macro you've been helping me with is 6E.

Cell A19 in sheet Adult Sort is the beginning of filtered and sorted data I want to move to sheet Adult Fiction, starting in the first blank cell after cell A24. Cells A1-A23 are reserved for further filters if needed later in the process. There are about 7 other groups of data that will be filtered and sorted in sheet Adult Sort before being copied to their places in line in sheet Adult Fiction. I am only copying values, no formulae. The filter and sort for each group takes place in sheet Adult Sort.

In the real workbook, there are 10 worksheets and 6 "official" macros. For each step in the process I'm building a macro to perform a small function. Once that's working, I move on to the next function and a new macro. When I'm finished and each piece is working, I'll combine all the macros into one.

What does the following expression mean in English? Destination:=Sheets("Adult Fiction").Range("A24") I understand that the destination for the copy is in sheet Adult Fiction but the .Range("A24") sounds like it will actually paste in cell A24 instead of the first blank cell after cell A24.

I seem to be having trouble with the Paste now. I've tried ActivateCell.Paste and ActiveSheet.Paste but I really have no idea how to activate the first blank cell after cell A24 for the Paste.

I so appreciate your time and patience.