-1

I am relatively new to coding in general, but here goes:

I have a huge list of membershipdata which I am trying to organize. This is going to be done weekly as the data is variable, so I am trying to automate the work a bit.

I have written (with help) a code that copies an entire row of data if a specific cell contains a specific text, and pastes it in another sheet under an existing table.

However, when doing this using a macro, the table doesnt resize accordingly, like it would do when using ctrl+c and ctrl+v. I.e. when there is more data this week then last, the table isnt large enough, and so the sorting options in the table wont sort all the data.

I have been able to resize the table using this code:

Sub sortOK()

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("OK")
Set StartCell = Range("A1")

  Worksheets("OK").UsedRange

  LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
  LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column

  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

  sht.ListObjects("OK").Resize Range(StartCell, sht.Cells(LastRow, LastColumn))

End Sub

This works fine, however it only works when I am viewing this specific sheet, I cant operate it from another sheet. How can I do so?

I would also like to do the same operation in one go on four other lists, each in its own spreadsheet.

I have been stuck on this problem alost an entire day, and my head is about to explode!

Thank you in advance.

Erik Løkås
  • 3
  • 1
  • 5
  • *I have written (with help) a code that copies an entire row of data if a specific cell contains a specific text, and pastes it in another sheet under an existing table* That's not the code you've shown us. Show us that code. – David Zemens Oct 26 '17 at 12:47
  • Read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) about fully qualifying your range objects. Don't use `Select` or `Activate`. Qualify your ranges properly, and you can run a macro regardless of which sheet is active at runtime. – David Zemens Oct 26 '17 at 12:48
  • Always have option explicit at the top of your code for starters. Fully reference your range objects including the workbook you are using. Also, i am surprised your are not getting a compile error with Worksheets("OK").UsedRange as you are not setting this to a variable. – QHarr Oct 26 '17 at 12:53
  • If Worksheets("OK") is in the same workbook as the code then fully qualifying would start to look like Dim wb As Workbook Set sht = wb.Worksheets("OK") Set StartCell = sht.Range("A1") etc – QHarr Oct 26 '17 at 12:54

1 Answers1

0

Try like this:

Set StartCell = sht.Range("A1")

It should work. This way, you are referencing the worksheet to the range as well. If you do not do it, it takes the ActiveSheet.Range("A1").

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thank you for your respons, but it didn't work, however my error changed from: "Method "range" of object"_Worksheet" failed"" to "Select-method in Range-class failed". Both with a "Run-time error "1004". – Erik Løkås Oct 26 '17 at 12:51
  • @ErikLøkås - write `sht.Select` on the line before the error. – Vityata Oct 26 '17 at 12:52
  • @ErikLøkås - welcome. You may consider reading this later - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Vityata Oct 26 '17 at 13:21