ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy rows with Interior Colour Index values 15 and 35 to a new she (https://www.excelbanter.com/excel-programming/422652-copy-rows-interior-colour-index-values-15-35-new-she.html)

manfareed
Copy rows with Interior Colour Index values 15 and 35 to a new she
 
Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,

joel
Copy rows with Interior Colour Index values 15 and 35 to a new she
 
The code will look something like the code below. Add a Worksheet call
Summary.

LastRow = Sheets("Summary").Range("A" & rows.Count).end(Xlup).Row
NewRow = LastRow + 1

For each Sht in sheets
if Sht.Name < "Summary" then
LastRow = Sht.Range("A" & rows.Count).end(Xlup).Row
for RowCount = 1 to LastRow
if Sht.Range("A" & RowCount).Interior.colorindex = 15 or _
Sht.Range("A" & RowCount).Interior.colorindex = 15 then

Sht.Rows(RowCount).Copy _
Destination:=Sheets("Summary").Rows(NewRow)
NewRow = Newrow + 1
end if
next RowCount
end if
next Sht

"manfareed" wrote:

Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,

manfareed
Copy rows with Interior Colour Index values 15 and 35 to a new
 
Hi Joel,

Many Thanks for your quick reply.

Nearly there I just need a subheading before it copies rows with colour
index 15 and 35. Subheading should be same as the "sheet name " from which
the data is being copied from. Sheet name is a place name eg. Birmingham.

How do include this in the code which you so kindly provided.

Thanks

"Joel" wrote:

The code will look something like the code below. Add a Worksheet call
Summary.

LastRow = Sheets("Summary").Range("A" & rows.Count).end(Xlup).Row
NewRow = LastRow + 1

For each Sht in sheets
if Sht.Name < "Summary" then
LastRow = Sht.Range("A" & rows.Count).end(Xlup).Row
for RowCount = 1 to LastRow
if Sht.Range("A" & RowCount).Interior.colorindex = 15 or _
Sht.Range("A" & RowCount).Interior.colorindex = 15 then

Sht.Rows(RowCount).Copy _
Destination:=Sheets("Summary").Rows(NewRow)
NewRow = Newrow + 1
end if
next RowCount
end if
next Sht

"manfareed" wrote:

Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,

joel
Copy rows with Interior Colour Index values 15 and 35 to a new
 
Sorry, I forgot to copy the sheet name like you originally posted

Sub test()

NewRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

For Each Sht In Sheets
If Sht.Name < "Summary" Then
With Sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow
If .Range("A" & RowCount).Interior.ColorIndex = 15 Or _
.Range("A" & RowCount).Interior.ColorIndex = 15 Then

LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n
Sheets("Summary").Range("A" & NewRow) = .Name
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy _
Destination:=Sheets("Summary").Range("B" & NewRow)
NewRow = NewRow + 1
End If
Next RowCount
End With
End If
Next Sht

End Sub


"manfareed" wrote:

Hi Joel,

Many Thanks for your quick reply.

Nearly there I just need a subheading before it copies rows with colour
index 15 and 35. Subheading should be same as the "sheet name " from which
the data is being copied from. Sheet name is a place name eg. Birmingham.

How do include this in the code which you so kindly provided.

Thanks

"Joel" wrote:

The code will look something like the code below. Add a Worksheet call
Summary.

LastRow = Sheets("Summary").Range("A" & rows.Count).end(Xlup).Row
NewRow = LastRow + 1

For each Sht in sheets
if Sht.Name < "Summary" then
LastRow = Sht.Range("A" & rows.Count).end(Xlup).Row
for RowCount = 1 to LastRow
if Sht.Range("A" & RowCount).Interior.colorindex = 15 or _
Sht.Range("A" & RowCount).Interior.colorindex = 15 then

Sht.Rows(RowCount).Copy _
Destination:=Sheets("Summary").Rows(NewRow)
NewRow = Newrow + 1
end if
next RowCount
end if
next Sht

"manfareed" wrote:

Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,

manfareed
Copy rows with Interior Colour Index values 15 and 35 to a new
 
Hi,

I get the following error ...

"Object required (Error 424)"

Its on the following line.

LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n

Thanks

"Joel" wrote:

Sorry, I forgot to copy the sheet name like you originally posted

Sub test()

NewRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

For Each Sht In Sheets
If Sht.Name < "Summary" Then
With Sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow
If .Range("A" & RowCount).Interior.ColorIndex = 15 Or _
.Range("A" & RowCount).Interior.ColorIndex = 15 Then

LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n
Sheets("Summary").Range("A" & NewRow) = .Name
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy _
Destination:=Sheets("Summary").Range("B" & NewRow)
NewRow = NewRow + 1
End If
Next RowCount
End With
End If
Next Sht

End Sub


"manfareed" wrote:

Hi Joel,

Many Thanks for your quick reply.

Nearly there I just need a subheading before it copies rows with colour
index 15 and 35. Subheading should be same as the "sheet name " from which
the data is being copied from. Sheet name is a place name eg. Birmingham.

How do include this in the code which you so kindly provided.

Thanks

"Joel" wrote:

The code will look something like the code below. Add a Worksheet call
Summary.

LastRow = Sheets("Summary").Range("A" & rows.Count).end(Xlup).Row
NewRow = LastRow + 1

For each Sht in sheets
if Sht.Name < "Summary" then
LastRow = Sht.Range("A" & rows.Count).end(Xlup).Row
for RowCount = 1 to LastRow
if Sht.Range("A" & RowCount).Interior.colorindex = 15 or _
Sht.Range("A" & RowCount).Interior.colorindex = 15 then

Sht.Rows(RowCount).Copy _
Destination:=Sheets("Summary").Rows(NewRow)
NewRow = Newrow + 1
end if
next RowCount
end if
next Sht

"manfareed" wrote:

Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,

manfareed
Copy rows with Interior Colour Index values 15 and 35 to a new
 
Hi ,

How would I change the code so that the data is copied as "paste special"
i.e. values only to the "Summary" sheet.

thanks

"Joel" wrote:

Sorry, I forgot to copy the sheet name like you originally posted

Sub test()

NewRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

For Each Sht In Sheets
If Sht.Name < "Summary" Then
With Sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow
If .Range("A" & RowCount).Interior.ColorIndex = 15 Or _
.Range("A" & RowCount).Interior.ColorIndex = 15 Then

LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n
Sheets("Summary").Range("A" & NewRow) = .Name
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy _
Destination:=Sheets("Summary").Range("B" & NewRow)
NewRow = NewRow + 1
End If
Next RowCount
End With
End If
Next Sht

End Sub


"manfareed" wrote:

Hi Joel,

Many Thanks for your quick reply.

Nearly there I just need a subheading before it copies rows with colour
index 15 and 35. Subheading should be same as the "sheet name " from which
the data is being copied from. Sheet name is a place name eg. Birmingham.

How do include this in the code which you so kindly provided.

Thanks

"Joel" wrote:

The code will look something like the code below. Add a Worksheet call
Summary.

LastRow = Sheets("Summary").Range("A" & rows.Count).end(Xlup).Row
NewRow = LastRow + 1

For each Sht in sheets
if Sht.Name < "Summary" then
LastRow = Sht.Range("A" & rows.Count).end(Xlup).Row
for RowCount = 1 to LastRow
if Sht.Range("A" & RowCount).Interior.colorindex = 15 or _
Sht.Range("A" & RowCount).Interior.colorindex = 15 then

Sht.Rows(RowCount).Copy _
Destination:=Sheets("Summary").Rows(NewRow)
NewRow = Newrow + 1
end if
next RowCount
end if
next Sht

"manfareed" wrote:

Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,

joel
Copy rows with Interior Colour Index values 15 and 35 to a new
 
Looks like I had a period instead of a comma
from:
LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n
to:
LastCol = .Cells(RowCount,Columns.Count).End(xlToLeft).Colum n


to make PasteSpecial
from:
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy _
Destination:=Sheets("Summary").Range("B" & NewRow)

to:
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy
Sheets("Summary").Range("B" & NewRow).PasteSpecial _
Paste:=xlPasteValues


"manfareed" wrote:

Hi ,

How would I change the code so that the data is copied as "paste special"
i.e. values only to the "Summary" sheet.

thanks

"Joel" wrote:

Sorry, I forgot to copy the sheet name like you originally posted

Sub test()

NewRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

For Each Sht In Sheets
If Sht.Name < "Summary" Then
With Sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow
If .Range("A" & RowCount).Interior.ColorIndex = 15 Or _
.Range("A" & RowCount).Interior.ColorIndex = 15 Then

LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n
Sheets("Summary").Range("A" & NewRow) = .Name
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy _
Destination:=Sheets("Summary").Range("B" & NewRow)
NewRow = NewRow + 1
End If
Next RowCount
End With
End If
Next Sht

End Sub


"manfareed" wrote:

Hi Joel,

Many Thanks for your quick reply.

Nearly there I just need a subheading before it copies rows with colour
index 15 and 35. Subheading should be same as the "sheet name " from which
the data is being copied from. Sheet name is a place name eg. Birmingham.

How do include this in the code which you so kindly provided.

Thanks

"Joel" wrote:

The code will look something like the code below. Add a Worksheet call
Summary.

LastRow = Sheets("Summary").Range("A" & rows.Count).end(Xlup).Row
NewRow = LastRow + 1

For each Sht in sheets
if Sht.Name < "Summary" then
LastRow = Sht.Range("A" & rows.Count).end(Xlup).Row
for RowCount = 1 to LastRow
if Sht.Range("A" & RowCount).Interior.colorindex = 15 or _
Sht.Range("A" & RowCount).Interior.colorindex = 15 then

Sht.Rows(RowCount).Copy _
Destination:=Sheets("Summary").Rows(NewRow)
NewRow = Newrow + 1
end if
next RowCount
end if
next Sht

"manfareed" wrote:

Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,

manfareed
Copy rows with Interior Colour Index values 15 and 35 to a new
 
Many Thanks...

"Joel" wrote:

Looks like I had a period instead of a comma
from:
LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n
to:
LastCol = .Cells(RowCount,Columns.Count).End(xlToLeft).Colum n


to make PasteSpecial
from:
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy _
Destination:=Sheets("Summary").Range("B" & NewRow)

to:
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy
Sheets("Summary").Range("B" & NewRow).PasteSpecial _
Paste:=xlPasteValues


"manfareed" wrote:

Hi ,

How would I change the code so that the data is copied as "paste special"
i.e. values only to the "Summary" sheet.

thanks

"Joel" wrote:

Sorry, I forgot to copy the sheet name like you originally posted

Sub test()

NewRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

For Each Sht In Sheets
If Sht.Name < "Summary" Then
With Sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow
If .Range("A" & RowCount).Interior.ColorIndex = 15 Or _
.Range("A" & RowCount).Interior.ColorIndex = 15 Then

LastCol = .Cells(RowCount.Columns.Count).End(xlToLeft).Colum n
Sheets("Summary").Range("A" & NewRow) = .Name
.Range(.Range("A" & RowCount), .Cells(RowCount,
LastCol)).Copy _
Destination:=Sheets("Summary").Range("B" & NewRow)
NewRow = NewRow + 1
End If
Next RowCount
End With
End If
Next Sht

End Sub


"manfareed" wrote:

Hi Joel,

Many Thanks for your quick reply.

Nearly there I just need a subheading before it copies rows with colour
index 15 and 35. Subheading should be same as the "sheet name " from which
the data is being copied from. Sheet name is a place name eg. Birmingham.

How do include this in the code which you so kindly provided.

Thanks

"Joel" wrote:

The code will look something like the code below. Add a Worksheet call
Summary.

LastRow = Sheets("Summary").Range("A" & rows.Count).end(Xlup).Row
NewRow = LastRow + 1

For each Sht in sheets
if Sht.Name < "Summary" then
LastRow = Sht.Range("A" & rows.Count).end(Xlup).Row
for RowCount = 1 to LastRow
if Sht.Range("A" & RowCount).Interior.colorindex = 15 or _
Sht.Range("A" & RowCount).Interior.colorindex = 15 then

Sht.Rows(RowCount).Copy _
Destination:=Sheets("Summary").Rows(NewRow)
NewRow = Newrow + 1
end if
next RowCount
end if
next Sht

"manfareed" wrote:

Hi ,

I have a spreadsheet with many "tabs" from which I need to extract to a
summary sheet the rows which have interior colour index value = 15 and 35.
Value 15 refers to the client and 35 to Gross Margin.

Each extract to the summary sheet should have a subheading equal to the tab
name from which it wa extracted i.e.
Birmingham
Client1
Grossmargin
Client 2
Gross Margin
etc.

Manchester
Client1
Gross Margin
etc.

Many Thanks,


All times are GMT +1. The time now is 08:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com