Microsoft Excel - Range Copy to new excel worksheet

Asked By John on 13-Mar-10 04:18 AM


I have an excel file contains 8 sheet. I would like to copy full details to a new excel new file if 2 values not equel.


Suppose Cast Quantity <> Delivery then I want to copy it to a new excel file. All 8 should check then result should come with Head Lines to New File (Sheet1) one after another. I want all result of 8 sheet in New Excel file sheet 1.


Can some one to help me find the solution.

Jonathan VH replied to John on 13-Mar-10 07:27 AM
I guess you want to iterate throiugh the worksheets and then through the data range in each.  I suppose you should defer opening a new workbook until the first inequality, in case there are none found.  Here's an example:

Sub CopyWhereDifferent()
  Dim wb As Workbook, ws As Worksheet, cell As Range, n As Long, fn As String
  n = 1
  For Each ws In ThisWorkbook.Worksheets
    For Each cell In Range(ws.Cells(1), ws.Cells(1).End(xlDown))
      If cell <> cell(, 2) Then
        If wb Is Nothing Then Set wb = Workbooks.Add
        Range(cell, cell(, 3)).Copy wb.ActiveSheet.Cells(n, 1)
        n = n + 1
      End If
    Next
  Next
  Do
    fn = Application.GetSaveAsFilename
  Loop Until fn <> False
  wb.SaveAs fn
End Sub
John replied to Jonathan VH on 13-Mar-10 08:00 AM
I would like to check the 2 cell in the worksheet. That is Cast Quantity and Delivery. If Cast Quantity <> Delivery then I would like to copy it to new sheet.
So Can I change if Cast Quantity <> Delivery(,2) instead of If cell <> cell(,2) Then.
John replied to John on 13-Mar-10 08:31 AM
I want to attach an excel file. Becuse I want to compare two cell in the orginal worksheet. That is Cast Quantity <> Delivery then copy to a new excel worksheet  sheet one.
John replied to Jonathan VH on 13-Mar-10 08:36 AM
 would like to check the 2 cell in the worksheet. That is Cast Quantity and Delivery. If Cast Quantity <> Delivery then I would like to copy it to new sheet.
So Can I change if Cast Quantity <> Delivery(,2) instead of If cell <> cell(,2) .

How I can attach a sample Excel file?
John replied to John on 14-Mar-10 01:03 AM
 
 

SL.NO

BEAM MARK

SPAN

DRAWING NO

REV
NO.

Draw

REQD

CAST

REMAIN

STOCK

DEL

REMARKS

Status

QTY

QTY

QTY

1

PS-1509

1

B12-S-PR-0011

1

APP

90

90

0

10

80

 

2

PS-1666

1

B12-S-PR-0011

1

APP

10

10

0

5

5

3

PS-1640

1

B12-S-PR-0011

1

APP

4

4

0

2

2

4

PS-1641

1

B12-S-PR-0011

1

APP

4

4

0

2

2

 

5

PS-1642

1

B12-S-PR-0011

1

APP

4

4

0

2

2

6

PS-1643

1

B12-S-PR-0011

1

APP

4

4

0

2

2

7

PS-1644

1

B12-S-PR-0011

1

APP

4

4

0

2

2

8

PS-1645

1

B12-S-PR-0011

1

APP

4

4

0

2

2

9

PS-1646

1

B12-S-PR-0011

1

APP

4

4

0

2

2

10

PS-1647

1

B12-S-PR-0011

1

APP

4

4

0

2

2

11

PS-1648

1

B12-S-PR-0011

1

APP

4

4

0

2

2

12

PS-1649

1

B12-S-PR-0011

1

APP

4

4

0

2

2

13

PS-1509

2

B12-S-PR-0011

1

APP

165

165

0

100

65

 

14

PS-1667

2

B12-S-PR-0011

1

APP

12

12

0

4

8

 

15

PS-1668

2

B12-S-PR-0011

1

APP

2

2

0

1

1

 

16

PS-1669

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

17

PS-1670

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

18

PS-1671

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

19

PS-1672

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

20

PS-1673

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

21

PS-1674

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

22

PS-1675

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

23

PS-1676

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

24

PS-1677

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

25

PS-1678

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

26

PS-1679

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

27

PS-1680

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

28

PS-1681

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

29

PS-511

3

B12-S-PR-0011

1

APP

70

70

0

0

70

 

30

PS-511

3

B12-S-PR-0011

1

APP

182

182

0

22

160

 

31

PS-1682

3

B12-S-PR-0011

1

APP

28

28

0

10

18

 

32

PS-511

4

B12-S-PR-0011

1

APP

252

252

0

92

160

 

33

PS-1682

4

B12-S-PR-0011

1

APP

28

28

0

8

20

 

 

 

 

 

 

 

905

905

0

272

633

 


I would like to check the 2 cell in the worksheet. That is Cast Quantity and Delivery. If Cast Quantity <> Delivery then I would like to copy it to new sheet.  CAST <> DEL

CAN YOU CHANGE THE MACRO SUITABLE FOR THIS.


John replied to John on 14-Mar-10 01:07 AM
 
 

SL.NO

BEAM MARK

SPAN

DRAWING NO

REV
NO.

Draw

REQD

CAST

REMAIN

STOCK

DEL

REMARKS

Status

QTY

QTY

QTY

1

PS-1509

1

B12-S-PR-0011

1

APP

90

90

0

10

80

 

2

PS-1666

1

B12-S-PR-0011

1

APP

10

10

0

5

5

3

PS-1640

1

B12-S-PR-0011

1

APP

4

4

0

2

2

4

PS-1641

1

B12-S-PR-0011

1

APP

4

4

0

2

2

 

5

PS-1642

1

B12-S-PR-0011

1

APP

4

4

0

2

2

6

PS-1643

1

B12-S-PR-0011

1

APP

4

4

0

2

2

7

PS-1644

1

B12-S-PR-0011

1

APP

4

4

0

2

2

8

PS-1645

1

B12-S-PR-0011

1

APP

4

4

0

2

2

9

PS-1646

1

B12-S-PR-0011

1

APP

4

4

0

2

2

10

PS-1647

1

B12-S-PR-0011

1

APP

4

4

0

2

2

11

PS-1648

1

B12-S-PR-0011

1

APP

4

4

0

2

2

12

PS-1649

1

B12-S-PR-0011

1

APP

4

4

0

2

2

13

PS-1509

2

B12-S-PR-0011

1

APP

165

165

0

100

65

 

14

PS-1667

2

B12-S-PR-0011

1

APP

12

12

0

4

8

 

15

PS-1668

2

B12-S-PR-0011

1

APP

2

2

0

1

1

 

16

PS-1669

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

17

PS-1670

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

18

PS-1671

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

19

PS-1672

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

20

PS-1673

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

21

PS-1674

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

22

PS-1675

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

23

PS-1676

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

24

PS-1677

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

25

PS-1678

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

26

PS-1679

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

27

PS-1680

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

28

PS-1681

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

29

PS-511

3

B12-S-PR-0011

1

APP

70

70

0

0

70

 

30

PS-511

3

B12-S-PR-0011

1

APP

182

182

0

22

160

 

31

PS-1682

3

B12-S-PR-0011

1

APP

28

28

0

10

18

 

32

PS-511

4

B12-S-PR-0011

1

APP

252

252

0

92

160

 

33

PS-1682

4

B12-S-PR-0011

1

APP

28

28

0

8

20

 

 

 

 

 

 

 

905

905

0

272

633

 


I would like to check the 2 cell in the worksheet. That is Cast Quantity and Delivery. If Cast Quantity <> Delivery then I would like to copy it to new sheet.  CAST <> DEL

CAN YOU CHANGE THE MACRO SUITABLE FOR THIS.


Range Copy to new excel worksheet
John replied to John on 14-Mar-10 01:32 AM

I have an excel file contains 8 sheet. I would like to copy full details to a new excel new file if 2 values are not equal.

Suppose Cast Quantity <> Delivery then I want to copy it to a new excel file. All 8 should check then result should come with Head Lines to New File (Sheet1) one after another. I want all result of 8 sheets in one bye one down the New Excel file sheet 1.

 

I would like to check the 2 columns in the worksheet. That is Cast Quantity and Delivery. If Cast Quantity <> Delivery then I would like to copy it to new sheet. (CAST <> DEL)

CAN YOU CHANGE THE MACRO SUITABLE FOR THIS?

 

SL.NO

BEAM MARK

SPAN

DRAWING NO

REV
NO.

Draw

REQD

CAST

REMAIN

STOCK

DEL

REMARKS

Status

QTY

QTY

QTY

1

PS-1509

1

B12-S-PR-0011

1

APP

90

90

0

40

50

 

2

PS-1666

1

B12-S-PR-0011

1

APP

10

10

0

5

5

 

3

PS-1640

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

4

PS-1641

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

5

PS-1642

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

6

PS-1643

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

7

PS-1644

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

8

PS-1645

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

9

PS-1646

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

10

PS-1647

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

11

PS-1648

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

12

PS-1649

1

B12-S-PR-0011

1

APP

4

4

0

0

4

 

13

PS-1509

2

B12-S-PR-0011

1

APP

165

165

0

45

120

 

14

PS-1667

2

B12-S-PR-0011

1

APP

12

12

0

0

12

 

15

PS-1668

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

16

PS-1669

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

17

PS-1670

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

18

PS-1671

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

19

PS-1672

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

20

PS-1673

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

21

PS-1674

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

22

PS-1675

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

23

PS-1676

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

24

PS-1677

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

25

PS-1678

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

26

PS-1679

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

27

PS-1680

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

28

PS-1681

2

B12-S-PR-0011

1

APP

2

2

0

0

2

 

29

PS-511

3

B12-S-PR-0011

1

APP

70

70

0

30

40

 

30

PS-511

3

B12-S-PR-0011

1

APP

182

182

0

107

75

 

31

PS-1682

3

B12-S-PR-0011

1

APP

28

28

0

0

28

 

32

PS-511

4

B12-S-PR-0011

1

APP

252

252

0

0

252

 

33

PS-1682

4

B12-S-PR-0011

1

APP

28

28

0

0

28

 

 

 

 

 

 

 

905

905

0

227

678