Marcros for inserting rows and copying sheets

May 10, 2010 at 01:51:34
Specs: Excel 2007

Hello every one
I need support in the below issue

i have a workbook that serves up to 60 projects
The workbook has 1 summary sheet ,1 format sheet and 60 identical copy of the format sheet
The summary sheet is the summarizing the 60 sheet, Each one of the 60 identical sheet has a 5 rows for data entry and one row for summarizing the 5 rows
Each 6 rows ( 5 data entry and one summary) label is the sheet no , So row label no. 6 is a summary for sheet no. 6
Therefore, row 1 is the column headers, Rows 2-361 are for data entry and row 362 is summary of the 360 row
The file size is very big for me and it's slow during saving
what i need to do is to make the workbook only 2 sheet as a start, Summary sheets and the format sheets labeled "0"
The summary sheet will contain only a row for the column headers and a 6 rows with row header "0" and the summary row
Then i need to create a Command button for example labeled "New entry" and assign a macro where each time i press the command button it it that should copy the 6 rows*( range 2:7) and insert it in it's sequence and label them with the their serial and copy the format sheet and label it with the serial given to the 6 rows

Can any one help with this?


See More: Marcros for inserting rows and copying sheets

Report •


#1
May 10, 2010 at 03:17:20

Hi,

From what you say, it appears that instead of starting with 62 sheets in the workbook, you want to start with 2 (Summary and Format), then create new data sheets each time a button is clicked. The macro that runs when the button is clicked also creates links from the new worksheet to the summary sheet.

Presumably you will eventually have 60 data sheets, which brings you back to where you are now with a speed problem.

It seems surprising that a workbook with only 6 rows of data on 60 sheets and a summary sheet with about 360 linked cells should present a speed problem.
How big is the workbook.
Are you saving it on a local drive or a network drive
Does the workbook have any external links.

Regards


Report •

#2
May 10, 2010 at 04:25:37

Hi

I just want to clarify the below

-I will not create a data sheet
-The workbook structure is not "6 rows of data on 60 sheets and a summary sheet with about 360 linked cells should present a speed problem."
-The 60 sheet are not always used, in some months only 30 used and in some months i add new sheets, it's just made to serve up to 60 project

I'll try to make it more clear
-It's 60 identical complicated sheets and a summary sheet, each one of the 60 sheet has a 6 rows in the summary sheet
For example, Sheet no. "7" has six rows labeled "7" ( row 38-43) in the Summary sheet, each one of the 6 rows has formulas that summarizes results of sheet "7".
The summary sheet is linked to the 60 sheet through "=INDIRECT" function that uses the row label for defining the sheet no. to read from and uses the column header for as a criteria.

-My work sequence on the old workbook is as the follow
Suppose i have a project with 5 sub projects under it, so this project is 5 lines
In the first 6 rows (labeled"1") i'll paste the 5 line on the data entry area and the 6th row will automatically summarize some data for the project
After that i'll go to sheet no "1" and paste another details for the project and for the 5 sub projects , according sheet "1" will calculate some results based on the pasted data
Then If i went back to the summary sheet i'll find the results summarized in row 38-43

What i need is
1- Make the work book structure only 2 sheet,
2- The first sheet is the Sheet"0" that represent the a template for the 60 sheet
3- The second sheet is the summary sheet with only six rows labeled the same as the template sheet "0"
4- Create a command button in the summary sheet with assigned macros, when the macro runs it will copy the first 6 rows (labeled "0" ) and insert them below and label them 1+ the previous 6 rows label "0+1= 1" and then copy sheet no "0" to a new sheet in the workbook and label it the same as the inserted rows , therefore i'll have sheet no"1" with 6 rows labeled"1" to summarize it and so on for each a new entry.

I hope it more clear now
Thanks in advance for help


Report •

#3
May 14, 2010 at 15:58:31

Hi,

Your use of =INDIRECT() may be your problem.

INDIRECT() is one of Excel's 'volatile' functions. This means that even if data has not changed at all, every INDIRECT() function will be recalculated, not only every time anything changes, but also when you save.

Here is a macro that creates the new sheets and makes the links without using INDIRECT()

Assuming that you start with a worksheet named "Summary" and a template worksheet named "0" with the value 0 in column A of the rows to be linked, then this macro will create a new sheet when the embedded button on the summary page is clicked.
The sheet will get the next number as it's name and the Summary sheet will be linked to the selected range of cells on each new worksheet.

The summary worksheet will have the worksheet numbers in column A in blocks of 7 rows.

Note that the range of cells to be copied has been set in the macro - I used A1 to K7 - change as required.

Here is the code attached to the button:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngCell As Range
Dim rngSmryEnd As Range
Dim intSheet As Integer
Dim strName As String

On Error GoTo ErrHnd

'turn off screen updating
Application.ScreenUpdating = False

'find end of current summary data + 1 row - i.e. first blank row
Set rngSmryEnd = Worksheets("Summary"). _
            Range("A" & CStr(Application.Rows.Count)). _
            End(xlUp).Offset(1, 0)
'get last sheet 'number'
intSheet = CInt(rngSmryEnd.Offset(-1, 0).Text)
'next sheet
intSheet = intSheet + 1
'sheet name
strName = Format(intSheet, "#0")

'add a new sheet using worksheet '0' as the template
Worksheets("0").Copy after:=Worksheets(Worksheets.Count)
'name new worksheet
Worksheets(Worksheets.Count).Name = strName

'create links to the seven rows in the new worksheet
'link columns A to K (change as required)
Worksheets(strName).Range("A1:K7").Copy
Worksheets("Summary").Activate
Worksheets("Summary").Range(rngSmryEnd.Address).Select
ActiveSheet.Paste Link:=True

'change the column A value from 0 to next number
'on the numbered worksheet - the Summary worksheet will 'follow'
Worksheets(strName).Range("A1:A7").Value = intSheet

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'reinstate screen updating
Application.ScreenUpdating = True
End Sub

Regards


Report •

Related Solutions

#4
May 22, 2010 at 23:46:34

Thanks you all for your reply and sorry for my late feedback

i was able to creat this macro that exactly match my need
Thanks again for all of you

Sub Macro1()
'
' Macro1 Macro
'

'

Sheets("Invoicing").Select
Rows("2:7").Select
Selection.Copy
Range("B8").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Sheets("0").Select
Sheets("0").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Previous.Select
Range("A1").Select
Selection.Copy
ActiveSheet.Next.Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet().Select
ActiveSheet.Name = Range("A1")
Sheets("Invoicing").Select

End Sub


Report •

#5
May 23, 2010 at 01:51:30

I have a suggestion:

After you record a macro, you should go into the VBA editor and "clean it up".

Rarely do you need to select cell ranges or activate sheets to perform an operation on them.

I don't have a workbook set up exactly like yours, so I can't test my "condensed" code fully, but this code seems to do the same thing your code is doing.

Sub EfficientMacro1()

Sheets("Invoicing").Rows("2:7").Copy
Sheets("Invoicing").Range("B8").End(xlDown).End(xlToLeft).Insert Shift:=xlDown
Sheets("0").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Previous.Range("A1").Copy
ActiveSheet.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Name = ActiveSheet().Range("A1")
Sheets("Invoicing").Select
End Sub


I'm not quite sure what you are doing with the Copy and PasteSpecial...Values section, but this seems to do the same thing:

Sub EvenMoreEfficientMacro1()

Sheets("Invoicing").Rows("2:7").Copy
Sheets("Invoicing").Range("B8").End(xlDown).End(xlToLeft).Insert Shift:=xlDown
Sheets("0").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Range("B1") = ActiveSheet.Previous.Range("A1")
ActiveSheet.Name = ActiveSheet().Range("A1")
Sheets("Invoicing").Select
End Sub


Report •


Ask Question