asked on

Cutting and paste defined tables

Dear Experts:

I got several defined tables (named Table1, Table2, Table3, etc.) in Sheet1.

Is it possible to cut Table2 and paste it into Sheet2, cut Table3 and paste it into Sheet3 and so forth?

I have attached a sample file for your convenience with detailed explanations.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Cut-And-Paste-Defined-Tables.xlsx

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Dear akoster:

thank you very much for your swift reponse. It worked once, not a second time, strange. I keep on trying and let you know.

Thank you very much.

Regards, Andreas

Andreas Hermle

ASKER

Hi akoster,

yes it is working. Thank you very much. There is one thing to complement the macro. I formatted all these lists as tables (ListObject in VBA).

If the macro could first check the number of List Objects found on the current worksheet. This would then automatically determine the second integer on line 4, i.e. For x = 2 To Number of ListObjects.

What do you think? Is this possible?

Regards, Andreas

Andreas Hermle

ASKER

Hi akoster,

found out myself how to count ListObjects (lists formatted as table) in the current worksheet. (see attached code)

Regards, Andreas

Sub FindAllTablesOnSheet()
Dim j As Integer
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
    j = j + 1
    Next
   MsgBox j
   
End Sub

Open in new window

SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Dear akoster:

thank you very much for your great help.

Regards, Andreas