New Workbook with xlsx

Qaiyum · Nov 28th 2013

Hi,

I have bulk data and while copy and pasting data into new workbook using below code, it is creating xls file format and paste function failed. Currently I am working on Excel 2010 version.
Please help how would I assign macro to create new workbook in xlsx format. Thanks in advance.

Code

Workbooks.Add

cytop · Nov 28th 2013

Re: New Workbook with xlsx

Quote

...using below code

What code...?

Also, your comments that "...it is creating xls file format and paste function failed" are not related. XLS is the file format for some versions of Excel so your paste is failing for some other reason - your code is needed.

Qaiyum · Nov 28th 2013

Re: New Workbook with xlsx

Hi,

When ever I Create New workbook by Pressing "Control N" the file open and it is having 65536 rows only, means Excel 97-2003 version (xls type). And I want to copy & Paste from 30 different sheet which around 150000/- rows +, it is not possible that code will paste data and will fail when it arrived to 65536 rows level.

What I want when my code create new file it should be having 1048576 rows, i.e. Excel 2010 version.
Please help... Hopes this clarifies my requirement....

Code

Private Sub CommandButton11_Click()
Dim x As Integer
Dim RIM, DMI
Set RIM = Application.ActiveWorkbook
Worksheets("RMIS").Select
ActiveSheet.Unprotect Password:="SBIMIS"


Worksheets("01").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("02").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("03").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("04").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("05").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("06").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("07").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("08").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("09").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("10").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("11").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("12").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("13").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("14").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("15").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("16").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("17").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("18").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("19").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("20").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("21").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("22").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("23").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("24").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("25").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("26").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("27").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("28").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("29").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("30").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Worksheets("31").Select
Range("d5:cx5004").Select
Selection.Copy
Range("d5").Select
Worksheets("RMIS").Select
x = Range("cv1").Value + 1
Range("A" & x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False




Worksheets("RMIS").Copy
Set DMI = Application.ActiveWorkbook
RIM.Activate
Range("a2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
ActiveSheet.Protect Password:="SBIMIS"
DMI.Activate
    Range("CV2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-98]=RC[-98],R[-1]C[-11]-RC[-17],""NEW"")"
    Range("CV2").Select
    Selection.Copy
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("CV1").Select
    Selection.ClearContents
    Range("CW1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],"">0"")+COUNTIF(C[-1],""<0"")"
    MsgBox ("Total Opening closing Mismatch for current month = " & Range("CW1").Value)
    End
End Sub

Display More

**royUK** · Nov 28th 2013

Re: New Workbook with xlsx

You need to specify the File Format when using saveas.

Where do you add a workbook., that code only writes in the activeworkbook so if the sheets don't exist in the new workbook the code will fail.

the code could do with a major overhaul as it looks to be recorded and does numerous unnecessary slections

Qaiyum · Nov 28th 2013

Re: New Workbook with xlsx

Hi,

So sorry, please find below is the correct code.
After all 31 sheets consolidated in "RMIS" Sheet the data will be around 1500000 Plus. And due the new opening workbook is having only 65536 rows paste function is failed.

Once again sorry for wrong code paste:

Code

[COLOR=#0000ff]Private[/COLOR] [COLOR=#0000ff]Sub[/COLOR] CommandButton11_Click() 
    [COLOR=#0000ff]Dim[/COLOR] x [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Integer[/COLOR] 
    [COLOR=#0000ff]Dim[/COLOR] RIM, DMI 
    [COLOR=#0000ff]Set[/COLOR] RIM = Application.ActiveWorkbook 
    Worksheets("RMIS").Select 
    ActiveSheet.Unprotect Password:="SBIMIS" 
     
    Worksheets("01").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("02").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("03").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("04").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("05").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("06").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("07").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("08").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("09").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("10").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("11").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("12").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("13").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("14").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("15").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("16").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("17").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("18").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("19").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("20").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("21").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("22").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("23").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("24").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("25").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("26").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("27").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("28").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("29").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("30").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Worksheets("31").Select 
    Range("d5:cx5004").Select 
    Selection.Copy 
    Range("d5").Select 
    Worksheets("RMIS").Select 
    x = Range("cv1").Value + 1 
    Range("A" & x).Select 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Cells.copy
     
    Workbooks.Add


    [COLOR=#0000ff]Set[/COLOR] DMI = Application.ActiveWorkbook 
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlPastevalues
    Selection.PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    RIM.Activate 
    Range("a2").Select 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    Selection.EntireRow.Delete 
    ActiveSheet.Protect Password:="SBIMIS" 
    DMI.Activate 
    Range("CV2").Select 
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-98]=RC[-98],R[-1]C[-11]-RC[-17],""NEW"")" 
    Range("CV2").Select 
    Selection.Copy 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
    Range("CV1").Select 
    Selection.ClearContents 
    Range("CW1").Select 
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],"">0"")+COUNTIF(C[-1],""<0"")" 
    MsgBox ("Total Opening closing Mismatch for current month = " & Range("CW1").Value) 
    [COLOR=#0000ff]End[/COLOR] 
[COLOR=#0000ff]End Sub[/COLOR]

Display More

**royUK** · Nov 28th 2013

Re: New Workbook with xlsx

Upgrade to Excel 2010.

That code will be horrendously slow in any version

New Workbook with xlsx

Participate now!

Share