How do I create a new table below the last one
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 |
How do I create a new table below the last one
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 '---------------------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com "Vince" wrote: 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 |
All times are GMT +1. The time now is 07:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com