Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Create new excel table with filtered records from another table | Excel Worksheet Functions | |||
create pivot table from excel table | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
create a source table from a special pasted value dynamic crossed table | Excel Programming |