Newbie help needed please...

  • Thread starter Thread starter ML
  • Start date Start date
M

ML

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
 
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
 
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
 
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
Click to expand...
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com
 
Send your file with a complete explanation and before/after examples
to dguillett1
@gmail.com
Click to expand...

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
 
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
====================
 
This should be all in one line
Cells(i, 1).Resize(1, 2).Copy Destination:=Sheets("sheet2").Cells(i, 1)
 
Hi ML
I miss this line>sheet 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
 
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
 
Hello again CimJet

Wow, thank you

This works perfectly and it even impressed my boss.

Touche.

Thx again

Mark.
 
Back
Top