How do I create a new table below the last one

[Vince]

I've been having problem creating this macro
my problem is that i have 3 sheets and I've been doing the macro jumping
from one sheet to another and if I dont put my active cell in and expecify
place the macro won't run and if I dont use the relative reference the macro
will be repeat it in the same position on top of the one already create it ,
How Do I create a macro that when i make the sheet in the first sheet
simultaniously create the other tables to in the other worksheets.

ActiveCell.Offset(-38, 0).Range("A1").Select
ActiveCell.Range("A1:O38").Select
Selection.Copy
ActiveCell.Offset(38, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(38, 0).Range("A1").Select
Sheets("T+M").Select
ActiveCell.Offset(-100, -8).Range("A1").Select
ActiveCell.Range("A1:W89").Select
Selection.Copy
ActiveSheet.Shapes("Object 29").Select
ActiveCell.Offset(89, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(89, 0).Range("A1").Select
Sheets("Murk 12C").Select
ActiveCell.Offset(-43, -8).Range("A1").Select
ActiveCell.Range("A1:Q32").Select
Selection.Copy
ActiveCell.Offset(41, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(41, 0).Range("A1").Select
Sheets("MURK 11A").Select
ActiveCell.Select
End Sub

 

[sebastienm]

Hi,
First thing, you don't need to select a sheet and a cell before copying or
pasting to it. It can be done even though the sheet is not the Activate one.

Try something as (could be done in a shorter way, but i expanded to make it
more clear... i hope):

'---------------------------------------------------------------
Sub test()

Dim wshO As Worksheet, rgO As Range '(O)rigin sheet and range
Dim wshD As Worksheet, rgD As Range '(D)estination sheet and range

'--- Copy TM!A1:O38 at end of data in "Murk 12C" ---
Set wshO = ThisWorkbook.Worksheets("T+M") 'origin sheet
Set rgO = wshO.Range("A1:W89") 'origin range
Set wshD = ThisWorkbook.Worksheets("Murk 12C") 'dest sheet
'Find last cell on dest range using column A.
Set rgD = wshO.Columns(1).Cells(wshO.Columns(1).Cells.Count) 'last cell
in col A: A65536
Set rgD = rgD.End(xlUp).Offset(1, 0) 'last cell of data in A then 1 row
bellow.
'Now copy/paste
rgO.Copy rgD 'Syntax: OriginRange.Copy DestinationRange

'--- repeat above section ---
End Sub
'----------------------------------------------------------------------