Hi
I have a Worksheet (Say ws1) with data from row 1 down in columns
B,C,D,E, I,J,K and finally L (L contains dates). I would like to
know how to populate another worksheet (say ws2) in the same workbook
with this data from row 3 down (in ws2 as there are 3 rows of fixed
header data). The only data I wish to transfer however are the rows
with Todays date in Column L of ws1.

One thing to note I have tried doing this manually by transferring
rows of data for Todays date, there are dropdown default values in ws2
in say columns F and G, by transferring all the data by row it removes
the dropdown list choices.

Any macro help with this is greatly appreciated

Thanks
Eddie

This is one way:


Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
For Each c In rng
If c.Value = Date Then
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & c.Row & ":E" & c.Row).Copy _
sh2.Range("A" & lr2 + 1)
Range("I" & c.Row & ":K" & c.Row).Copy _
sh2.Range("I" & lr2 + 1)
End If
Next
End Sub




"webels" wrote in message
...
Hi
I have a Worksheet (Say ws1) with data from row 1 down in columns
B,C,D,E, I,J,K and finally L (L contains dates). I would like to
know how to populate another worksheet (say ws2) in the same workbook
with this data from row 3 down (in ws2 as there are 3 rows of fixed
header data). The only data I wish to transfer however are the rows
with Todays date in Column L of ws1.

One thing to note I have tried doing this manually by transferring
rows of data for Todays date, there are dropdown default values in ws2
in say columns F and G, by transferring all the data by row it removes
the dropdown list choices.

Any macro help with this is greatly appreciated

Thanks
Eddie

This will work better. Need to qualify the copy range.

Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
For Each c In rng
If c.Value = Date Then
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
sh1.Range("A" & c.Row & ":E" & c.Row).Copy _
sh2.Range("A" & lr2 + 1)
sh1.Range("I" & c.Row & ":K" & c.Row).Copy _
sh2.Range("I" & lr2 + 1)
End If
Next
End Sub




"JLGWhiz" wrote in message
...
This is one way:


Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
For Each c In rng
If c.Value = Date Then
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & c.Row & ":E" & c.Row).Copy _
sh2.Range("A" & lr2 + 1)
Range("I" & c.Row & ":K" & c.Row).Copy _
sh2.Range("I" & lr2 + 1)
End If
Next
End Sub




"webels" wrote in message
...
Hi
I have a Worksheet (Say ws1) with data from row 1 down in columns
B,C,D,E, I,J,K and finally L (L contains dates). I would like to
know how to populate another worksheet (say ws2) in the same workbook
with this data from row 3 down (in ws2 as there are 3 rows of fixed
header data). The only data I wish to transfer however are the rows
with Todays date in Column L of ws1.

One thing to note I have tried doing this manually by transferring
rows of data for Todays date, there are dropdown default values in ws2
in say columns F and G, by transferring all the data by row it removes
the dropdown list choices.

Any macro help with this is greatly appreciated

Thanks
Eddie

On Jul 20, 7:34*pm, "JLGWhiz" wrote:
This will work better. *Need to qualify the copy range.

Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
* *For Each c In rng
* * *If c.Value = Date Then
* * * *lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
* * * *sh1.Range("A" & c.Row & ":E" & c.Row).Copy _
* * * * *sh2.Range("A" & lr2 + 1)
* * * *sh1.Range("I" & c.Row & ":K" & c.Row).Copy _
* * * * *sh2.Range("I" & lr2 + 1)
* * *End If
* *Next
End Sub

"JLGWhiz" wrote in message

...



This is one way:

Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
* For Each c In rng
* * If c.Value = Date Then
* * * lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
* * * Range("A" & c.Row & ":E" & c.Row).Copy _
* * * * sh2.Range("A" & lr2 + 1)
* * * Range("I" & c.Row & ":K" & c.Row).Copy _
* * * * sh2.Range("I" & lr2 + 1)
* * End If
* Next
End Sub

"webels" wrote in message
...
Hi
I have a Worksheet (Say ws1) with data from row 1 down in columns
B,C,D,E, I,J,K and finally L (L contains dates). *I would *like to
know how to populate another worksheet (say ws2) in the same workbook
with this data from row 3 down (in ws2 as there are 3 rows of fixed
header data). The only data I wish to transfer however are the rows
with Todays date in Column L of ws1.

One thing to note I have tried doing this manually by transferring
rows of data for Todays date, there are dropdown default values in ws2
in say columns F and G, by transferring all the data by row it removes
the dropdown list choices.

Any macro help with this is greatly appreciated

Thanks
Eddie

Hi JLGWhiz
Cant get this to work. Have you any other suggestions?

Eddie

It worked for me in a mock-up, what error messages are you getting, if any.
What does " I can't get it to work " mean?

"webels" wrote:

On Jul 20, 7:34 pm, "JLGWhiz" wrote:
This will work better. Need to qualify the copy range.

Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
For Each c In rng
If c.Value = Date Then
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
sh1.Range("A" & c.Row & ":E" & c.Row).Copy _
sh2.Range("A" & lr2 + 1)
sh1.Range("I" & c.Row & ":K" & c.Row).Copy _
sh2.Range("I" & lr2 + 1)
End If
Next
End Sub

"JLGWhiz" wrote in message

...



This is one way:

Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
For Each c In rng
If c.Value = Date Then
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & c.Row & ":E" & c.Row).Copy _
sh2.Range("A" & lr2 + 1)
Range("I" & c.Row & ":K" & c.Row).Copy _
sh2.Range("I" & lr2 + 1)
End If
Next
End Sub

"webels" wrote in message
...
Hi
I have a Worksheet (Say ws1) with data from row 1 down in columns
B,C,D,E, I,J,K and finally L (L contains dates). I would like to
know how to populate another worksheet (say ws2) in the same workbook
with this data from row 3 down (in ws2 as there are 3 rows of fixed
header data). The only data I wish to transfer however are the rows
with Todays date in Column L of ws1.

One thing to note I have tried doing this manually by transferring
rows of data for Todays date, there are dropdown default values in ws2
in say columns F and G, by transferring all the data by row it removes
the dropdown list choices.

Any macro help with this is greatly appreciated

Thanks
Eddie

Hi JLGWhiz
Cant get this to work. Have you any other suggestions?

Eddie

On Jul 20, 10:09*pm, JLGWhiz
wrote:
It worked for me in a mock-up, what error messages are you getting, if any. *
What does " I can't get it to work " mean?



"webels" wrote:
On Jul 20, 7:34 pm, "JLGWhiz" wrote:
This will work better. *Need to qualify the copy range.

Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
* *For Each c In rng
* * *If c.Value = Date Then
* * * *lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
* * * *sh1.Range("A" & c.Row & ":E" & c.Row).Copy _
* * * * *sh2.Range("A" & lr2 + 1)
* * * *sh1.Range("I" & c.Row & ":K" & c.Row).Copy _
* * * * *sh2.Range("I" & lr2 + 1)
* * *End If
* *Next
End Sub

"JLGWhiz" wrote in message

...

This is one way:

Sub moveCols()
Dim lr As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
lr = sh1.Cells(Rows.Count, 12).End(xlUp).Row
Set rng = sh1.Range("L1:L" & lr)
* For Each c In rng
* * If c.Value = Date Then
* * * lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
* * * Range("A" & c.Row & ":E" & c.Row).Copy _
* * * * sh2.Range("A" & lr2 + 1)
* * * Range("I" & c.Row & ":K" & c.Row).Copy _
* * * * sh2.Range("I" & lr2 + 1)
* * End If
* Next
End Sub

"webels" wrote in message
...
Hi
I have a Worksheet (Say ws1) with data from row 1 down in columns
B,C,D,E, I,J,K and finally L (L contains dates). *I would *like to
know how to populate another worksheet (say ws2) in the same workbook
with this data from row 3 down (in ws2 as there are 3 rows of fixed
header data). The only data I wish to transfer however are the rows
with Todays date in Column L of ws1.

One thing to note I have tried doing this manually by transferring
rows of data for Todays date, there are dropdown default values in ws2
in say columns F and G, by transferring all the data by row it removes
the dropdown list choices.

Any macro help with this is greatly appreciated

Thanks
Eddie

Hi JLGWhiz
Cant get this to work. Have you any other suggestions?

Eddie- Hide quoted text -

- Show quoted text -

Hi JLGWhiz
Sorry for not being more specific. What I mean is that no data was
transferred. No error messages either. What I was hoping for was the
transfer of all the data in columns from ws1 to ws2 as mentioned
originally where Todays date is in Column L. There will be older dates
in column L but I only want those rows with Todays date transferred.
One last thing which i forgot to mention in my original post. the
Worksheet ws2 will grow over time so I need the add the data to the
next free row on this sheet.

Thanks for your help so far
Eddie