Simple copying, but getting stuck in macro

Thanks and sorry for the delay in getting back to the thread. The only problem with the above is that the table that the macro is to draw from is a collection of If formulas (showing blank if the referenced cells contain nothing) so if I use end.down I will end up grabbing all of the rows in the table, including the blank ones. This is why I set a counter cell above the table that gives the actual number of rows with data. My problem was in trying to define a range that will have fixed columns but where the number of rows will change depending on what is in the counter cell.

Are the blank looking cells just at the end of a range or scatterd troughout the length of the column?
Either way, you could use Autofilter >0 and copy the visible cells only.
A stripped down copy of a workbook would greatly assist.
Change information that is not to be seen by others in the workbook.

I have done some re-working but still getting errors (object variable or With block variable not set). Using the code above I changed my macro but "buyrange" is still not getting defined right. BA4 is where I have the counter for the number of rows that actually contain data (and not just empty formulas). I also defined a cell on the target sheet (buyblock) where I want to copypastevalue the data from the buyrange. I think I also have a problem in defining where to start on buyblock (the counter on that sheet, located in W1, changes as data is copied in there and I simply need to have the target cell as "A" + buystartrow, or the value in W1). Here it is:

Dim maxrows_buy As Long
Dim buyrange As Range
Dim buystartrow As Long
Dim buystart As Range
maxrows_buy = Range("BA4").Value
buyrange = Range("AV8:BH" & maxrows_buy)
buystartrow = Sheets("Buyblock").Cells("W1").Value
buystart = Sheets("BuyBlock").Range("A" & buystartrow)
buyrange.Select
Selection.Copy
Sheets("BuyBlock").Select
Range(buystart).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

@agpyle
I have an example file for you to have a look at but we're not allowed to answer threads where people are not adhering to the rules. And so it should be.
See jeffreybrown's comment.

Sorry, didn't know about having to put tags in. Here is the code again, I assume I don't have to repeat my comments in the earlier posts.

Please Login or Register  to view this content.

Dim maxrows_buy As Long
Dim buyrange As Range
Dim buystartrow As Long
Dim buystart As Range
maxrows_buy = Range("BA4").Value
buyrange = Range("AV8:BH" & maxrows_buy)
buystartrow = Sheets("Buyblock").Cells("W1").Value
buystart = Sheets("BuyBlock").Range("A" & buystartrow)
buyrange.Select
Selection.Copy
Sheets("BuyBlock").Select
Range(buystart).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Please Login or Register  to view this content.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found

http://www.excelforum.com/misc.php?do=bbcode

About 2/3rds down is the explanation about code tags

Please Login or Register  to view this content.

Would it help if I uploaded the workbook as well, though I have already populated tables manually.

I highlighted the code as suggested and clicked on #, showing <CODE> at the start and end, but I don't see the CODE tags in my post when I view it now.

Did you see how your code is in a blue separate window now? That's the way.

Have a look at this simple example.
The pasting can be done anywhere you want.
This is for Excel 2007. I hope that's what you've got.

I opened the file but I'm not sure what this is pertaining to.

It is for Excel 2007, correct.

Re post #12
Yes, attach your workbook with before and after.

I didn't receive a reply to my last question. Was my previous post and highlighted code not sufficient to explain what I am trying to do?

Thanks but I managed to solve the problem by iteration.