Hello

I need help as I am not confident in VB although I am keen to learn what
I can.

I would like to copy a range of cells from 2 columns ( which is A2 to B
32 or A2 to slightly less B 29 depending on how many days there are in
the month ), based on another value within the same sheet ( sheet 1 to
another sheet 2.

The cells or the rows of cells in Columns A & B need to be copied if
Column C = Y

The rows in sheets 1 & 2 start at row 2 as I have a header in row 1.

It is only one sheet to another and although sheet 1 will be almost the
same range each time, sheet 2 will need to have each new set of values
appended to the next available empty row.

I appreciate your time and help.

Mark

A bit more information?
Will the source sheet only have the current months data?

You say "based on another value within the same sheet "
Does that refer to the month desired or the number of days in the
month.
Here is a simple macro
Sub copymonth()
Set ss = Sheets("checks")
Set ds = Sheets("sheet4")
slr = ss.Cells(Rows.Count, 1).End(xlUp).Row
dlr = ds.Cells(Rows.Count, 1).End(xlUp).Row + 1
ss.Range(Cells(2, 1), Cells(slr, 2)).Copy ds.Cells(dlr, 1)
ds.Columns.AutoFit
End Sub



On Oct 12, 6:57*am, ML wrote:
Hello

I need help as I am not confident in VB although I am keen to learn what
I can.

I would like to copy a range of cells from 2 columns ( which is A2 to B
32 or A2 to slightly less B 29 depending on how many days there are in
the month ), based on another value within the same sheet ( sheet 1 to
another sheet 2.

The cells or the rows of cells in Columns A & B need to be copied if
Column C = Y

The rows in sheets 1 & 2 start at row 2 as I have a header in row 1.

It is only one sheet to another and although sheet 1 will be almost the
same range each time, sheet 2 will need to have each new set of values
appended to the next available empty row.

I appreciate your time and help.

Mark

Use this instead
Sub copymonth()
Set ss = Sheets("checks")
Set ds = Sheets("sheet4")
slr = ss.Cells(Rows.Count, 1).End(xlUp).Row
dlr = ds.Cells(Rows.Count, 1).End(xlUp).Row + 1
ss.Cells(2, 1).Resize(slr, 2).Copy ds.Cells(dlr, 1)
ds.Columns.AutoFit
End Sub



On Oct 12, 7:38*am, Don Guillett wrote:
A bit more information?
Will the source sheet only have the current months data?

You say "based on another value within the same sheet "
Does that refer to the month desired or the number of days in the
month.
Here is a simple macro
Sub copymonth()
Set ss = Sheets("checks")
Set ds = Sheets("sheet4")
slr = ss.Cells(Rows.Count, 1).End(xlUp).Row
dlr = ds.Cells(Rows.Count, 1).End(xlUp).Row + 1
ss.Range(Cells(2, 1), Cells(slr, 2)).Copy ds.Cells(dlr, 1)
ds.Columns.AutoFit
End Sub

On Oct 12, 6:57*am, ML wrote:







Hello

I need help as I am not confident in VB although I am keen to learn what
I can.

I would like to copy a range of cells from 2 columns ( which is A2 to B
32 or A2 to slightly less B 29 depending on how many days there are in
the month ), based on another value within the same sheet ( sheet 1 to
another sheet 2.

The cells or the rows of cells in Columns A & B need to be copied if
Column C = Y

The rows in sheets 1 & 2 start at row 2 as I have a header in row 1.

It is only one sheet to another and although sheet 1 will be almost the
same range each time, sheet 2 will need to have each new set of values
appended to the next available empty row.

I appreciate your time and help.

Mark

Hello Don

Thank you for your reply.

Each row on the sheet 1 (source sheet) represents 1 day within a month ( 1st
to 31st) Column A = Date, Column B = Score, and in Column C I put a value
of Y.

It will be the same source sheet each time and the same sheet 2 (target
sheet).

So if Column C has a Y, then I need it to filter and show only Y, then
copy/append them to the target sheet.

I hope this better explains for you and thank you for your time and help.

Mark

On Oct 12, 7:01*pm, "ML" wrote:
Hello Don

Thank you for your reply.

Each row on the sheet 1 (source sheet) represents 1 day within a month ( 1st
to 31st) *Column A = Date, Column B = Score, and in Column C I put a value
of Y.

It will be the same source sheet each time and the same sheet 2 (target
sheet).

So if Column C has a Y, then I need it to filter and show only Y, then
copy/append them to the target sheet.

I hope this better explains for you and thank you for your time and help.

Mark
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com

On 13/10/2011 11:01 PM, Don Guillett wrote:
On Oct 12, 7:01 pm, wrote:
Hello Don

Thank you for your reply.

Each row on the sheet 1 (source sheet) represents 1 day within a month ( 1st
to 31st) Column A = Date, Column B = Score, and in Column C I put a value
of Y.

It will be the same source sheet each time and the same sheet 2 (target
sheet).

So if Column C has a Y, then I need it to filter and show only Y, then
copy/append them to the target sheet.

I hope this better explains for you and thank you for your time and help.

Mark
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com


Thank you for your offer Don.

I decided not to worry about the VB side and am just going to do it
manually as my work IT are not crazy about me macking around with VB.

Thank you for your time.

Mark.

Hi ML
If I understand your question, this should do it.
Sub loopY()
Finalrow = Cells(33, 1).End(xlUp).Row
For i = 1 To Finalrow
If Cells(i, 3).Value = "y" Then ' This is a lower case "y" change it if you
want Upcase
Cells(i, 1).Resize(1, 2).Copy Destination:=Sheets("sheet2").Cells(i, 1)
End If
Next i
End Sub
HTH
Cimjet
"ML" wrote in message
ond.com...
On 13/10/2011 11:01 PM, Don Guillett wrote:
On Oct 12, 7:01 pm, wrote:
Hello Don

Thank you for your reply.

Each row on the sheet 1 (source sheet) represents 1 day within a month ( 1st
to 31st) Column A = Date, Column B = Score, and in Column C I put a value
of Y.

It will be the same source sheet each time and the same sheet 2 (target
sheet).

So if Column C has a Y, then I need it to filter and show only Y, then
copy/append them to the target sheet.

I hope this better explains for you and thank you for your time and help.

Mark
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com


Thank you for your offer Don.

I decided not to worry about the VB side and am just going to do it manually
as my work IT are not crazy about me macking around with VB.

Thank you for your time.

Mark.

Just the macro..Use this one.
=============
Sub loopY()
Finalrow = Cells(33, 1).End(xlUp).Row
For i = 1 To Finalrow
If Cells(i, 3).Value = "y" Then Cells(i, 1).Resize(1, 2).Copy
Destination:=Sheets("sheet2").Cells(i, 1)
End If
Next i
End Sub
====================
"Cimjet" wrote in message
...
Hi ML
If I understand your question, this should do it.
Sub loopY()
Finalrow = Cells(33, 1).End(xlUp).Row
For i = 1 To Finalrow
If Cells(i, 3).Value = "y" Then ' This is a lower case "y" change it if you
want Upcase
Cells(i, 1).Resize(1, 2).Copy Destination:=Sheets("sheet2").Cells(i, 1)
End If
Next i
End Sub
HTH
Cimjet
"ML" wrote in message
ond.com...
On 13/10/2011 11:01 PM, Don Guillett wrote:
On Oct 12, 7:01 pm, wrote:
Hello Don

Thank you for your reply.

Each row on the sheet 1 (source sheet) represents 1 day within a month (
1st
to 31st) Column A = Date, Column B = Score, and in Column C I put a value
of Y.

It will be the same source sheet each time and the same sheet 2 (target
sheet).

So if Column C has a Y, then I need it to filter and show only Y, then
copy/append them to the target sheet.

I hope this better explains for you and thank you for your time and help.

Mark
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com


Thank you for your offer Don.

I decided not to worry about the VB side and am just going to do it manually
as my work IT are not crazy about me macking around with VB.

Thank you for your time.

Mark.

This should be all in one line
Cells(i, 1).Resize(1, 2).Copy Destination:=Sheets("sheet2").Cells(i, 1)

"Cimjet" wrote in message
...
Hi ML
If I understand your question, this should do it.
Sub loopY()
Finalrow = Cells(33, 1).End(xlUp).Row
For i = 1 To Finalrow
If Cells(i, 3).Value = "y" Then ' This is a lower case "y" change it if you
want Upcase
Cells(i, 1).Resize(1, 2).Copy Destination:=Sheets("sheet2").Cells(i, 1)
End If
Next i
End Sub
HTH
Cimjet
"ML" wrote in message
ond.com...
On 13/10/2011 11:01 PM, Don Guillett wrote:
On Oct 12, 7:01 pm, wrote:
Hello Don

Thank you for your reply.

Each row on the sheet 1 (source sheet) represents 1 day within a month (
1st
to 31st) Column A = Date, Column B = Score, and in Column C I put a value
of Y.

It will be the same source sheet each time and the same sheet 2 (target
sheet).

So if Column C has a Y, then I need it to filter and show only Y, then
copy/append them to the target sheet.

I hope this better explains for you and thank you for your time and help.

Mark
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com


Thank you for your offer Don.

I decided not to worry about the VB side and am just going to do it manually
as my work IT are not crazy about me macking around with VB.

Thank you for your time.

Mark.

Hi ML
I miss this linesheet 2 will need to have each new set of values
appended to the next available empty row.
So correction on my script, this should work properly.
Sub loopY()
Dim sh2 As Worksheet
Set sh2 = Sheets("Sheet2")
finalrow = Cells(65536, 1).End(xlUp).Row
For i = 1 To finalrow
If Cells(i, 3).Value = "y" Then
Lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(i, 1).Resize(1, 2).Copy Destination:=sh2.Cells(Lastrow + 1, 1)
End If
Next i
End Sub
Cimjet

"ML" wrote in message
ond.com...
On 13/10/2011 11:01 PM, Don Guillett wrote:
On Oct 12, 7:01 pm, wrote:
Hello Don

Thank you for your reply.

Each row on the sheet 1 (source sheet) represents 1 day within a month ( 1st
to 31st) Column A = Date, Column B = Score, and in Column C I put a value
of Y.

It will be the same source sheet each time and the same sheet 2 (target
sheet).

So if Column C has a Y, then I need it to filter and show only Y, then
copy/append them to the target sheet.

I hope this better explains for you and thank you for your time and help.

Mark
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com


Thank you for your offer Don.

I decided not to worry about the VB side and am just going to do it manually
as my work IT are not crazy about me macking around with VB.

Thank you for your time.

Mark.

Hello and thank you Cimjet

Even though my boss does not want me using VB, I tried your code and it
works very nicely which has softened my boss up a little....lol....

I was wondering if it is possible to expand the IF to include a secondary
criteria.

Using a snippet of your code, is it possible to do this:
I know I did not mention Column ("D") prior as it I did not think it
important ( at the time ).

If Cells(i, 4).value = "NotBlank" or Cells(i, 3).value= "Y".

So if Column 4 is blank, then it needs to look at Column 3, if both criteria
match then it copies.

Meaning that if Column ("D") is Blank then it should jump to the next row,
the same is true in the opposite, if ("D") is NotBlank and ("C") ="" then it
to will mean the code should step over to the next row until the end.

I hope this is not too confusing and that I have explained it clearly and or
if it is at all possible.

Thank you again for all your help.

Mark.

Hi ML
I'm not sure what you want but if I understand this should work.
Sub loopY()
Dim sh2 As Worksheet
Set sh2 = Sheets("Sheet2")
finalrow = Cells(65536, 1).End(xlUp).Row
For i = 1 To finalrow
If Cells(i, 3).Value = "y" And Cells(i, 4).Value < 0 Then
Lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(i, 1).Resize(1, 2).Copy Destination:=sh2.Cells(Lastrow + 1, 1)
End If
Next i
End Sub
==========
You need to have a "y" in column C and something in column D to copy.
HTH
Cimjet

"ML" wrote in message
nd.com...
Hello and thank you Cimjet

Even though my boss does not want me using VB, I tried your code and it works
very nicely which has softened my boss up a little....lol....

I was wondering if it is possible to expand the IF to include a secondary
criteria.

Using a snippet of your code, is it possible to do this:
I know I did not mention Column ("D") prior as it I did not think it important
( at the time ).

If Cells(i, 4).value = "NotBlank" or Cells(i, 3).value= "Y".

So if Column 4 is blank, then it needs to look at Column 3, if both criteria
match then it copies.

Meaning that if Column ("D") is Blank then it should jump to the next row, the
same is true in the opposite, if ("D") is NotBlank and ("C") ="" then it to
will mean the code should step over to the next row until the end.

I hope this is not too confusing and that I have explained it clearly and or
if it is at all possible.

Thank you again for all your help.

Mark.

Hello again CimJet

Wow, thank you

This works perfectly and it even impressed my boss.

Touche.

Thx again

Mark.

You're welcome
Regards
cimjet
"ML" wrote in message
nd.com...
Hello again CimJet

Wow, thank you

This works perfectly and it even impressed my boss.

Touche.

Thx again

Mark.