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
Andreas HermleTeam leaderAsked:
Who is Participating?
 
Arno KosterCommented:
This should do the trick :
Sub process_tables()
Dim x As Integer

    For x = 2 To 4
        Range("Tabelle" & x).Cut
        On Error GoTo add_sheet
        Worksheets("Sheet" & x).Paste
        On Error GoTo 0
    Next x
        
    Exit Sub

add_sheet:
    With Worksheets.Add
        .Name = "Sheet" & x
    End With
    Resume
    
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
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
0
 
Andreas HermleTeam leaderAuthor Commented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Andreas HermleTeam leaderAuthor Commented:

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

0
 
Arno KosterCommented:
excellent !

although the same result can be achieved faster and with less code by simply using

j = osh.listobjects.count


0
 
Andreas HermleTeam leaderAuthor Commented:
Dear akoster:

thank you very much for your great help.

Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.