Thread: Export Data based cell value

Sub snb()
  For Each tb In sheet1.CheckBoxes
   with tb.topleftcell
     If tb = 1 Then
        If .Row = 1 Then Sheets("sheet2").Cells(1,.Column).resize(usedrange.rows.count) = usedrange.Columns(.Column).Value
        If .Column = 1 Then Sheets("sheet2").Cells(.Column,1).resize(usedrange.colums.count) = usedrange.Rows(.Row).Value
     else
        If .Row = 1 Then Sheets("sheet2").Columns(.Column).clearcontents
        If .Column = 1 Then Sheets("sheet2").Rows(.Row).clearcontents
    End If
   end with
  Next
End Sub

It means you have to analyse the code, and to debug it.

thats what i've tried to do, except i dont realy have an experience with vb :/

EDIT: Tried editing the code but had no luck

Stick the code in the sheet1 code rather than a module and try it - or fully qualify the UsedRange

No, I mean like this:

UsedRange.Rows etc

Becomes

Sheet1.UsedRange etc

You have to be explicit when using code in modules, you need to tell it which sheet to reference as it has no idea. When using it in the sheet object, in this example it will use the sheet that the columns are in by default.

Whilst learning it is probably a good idea to always be explicit as it makes it easier for you to understand exactly what's going on, also in general step through the code and have the locals window open when you are learning, you get a much better insight of what is going on.

You can also query particular bit of code in the immediate window for example:

?UsedRange.Address

EDIT: Good news, i got rows and columns working successfully interestingly, it only works up to a certain amount of rows. goes up to 283 and then doesn't copy anything. the data on the sheet finishes at row 326, so it takes out a fair chunk.

Any ideas?

Thanks,
Storm08

EDIT: Okay... dont worry, i just found a slightly strange solution... o.0

All i did was put a thick border along the left hand side of the spreadsheet and it worked...

how did that make a difference???

Do you have any formulas in the rows below?

I would say (without having seen your workbook) that the first issue was caused by having a blank row in your data

That first issue is sorted now, i dont really know what it was but its gone :P and i dont have any formula in the rows below, it's completely empty.

EDIT:Managed to sort it