Transfer data between two worksheets

[webels]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[webels]

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

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

Eddie

 

[JLGWhiz]

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]

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?







- 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

 

[JLGWhiz]

The macro does exactly what you described. Suggest you review the macro to
make sure that sheet names match your file. Worksheets(1) might not be
equivalent to ws1 and Worksheets(2) might not be equivalent to ws2. Also,
if you retyped the macro instead of copying and pasting it, then you need to
check for typo's. But since you said there were no error messages,
apparently it compiles OK. I think the problem is in the worksheet names.

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?







- 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

 

[JLGWhiz]

This is how I tested the code.
On Sheet 1:
I put data in columns A:E and I:K for several rows.
I put dates in column L for all rows with data with
only a few with today's date.
For the code:
I put the macro in Module1 of the VBE window.
Result:
I ran the macro and it copied only those columns with
data for the rows with today's date in column L to
Sheet 2.

One other thing to check. If your dates are not the
same data type, i.e. either dates or text, then you
will not get the data copied. The code assumes that
they are data type Date. But you should get a type
mismatch error if they are not the same type. You
can step through the code one line at a time using F8
and use the mouseover to check tooltips for the values
of the variables c and rng to see if they are what you
expect.

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?







- 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

 

[webels]

This is how I tested the code.
On Sheet 1:
I put data in columns A:E and I:K for several rows.
I put dates in column L for all rows with data with
only a few with today's date.
For the code:
I put the macro in Module1 of the VBE window.
Result:
I ran the macro and it copied only those columns with
data for the rows with today's date in column L to
Sheet 2.

One other thing to check.  If your dates are not the
same data type, i.e. either dates or text, then you
will not get the data copied.  The code assumes that
they are data type Date.  But you should get a type
mismatch error if they are not the same type.  You
can step through the code one line at a time using F8
and use the mouseover to check tooltips for the values
of the variables c and rng to see if they are what you
expect.






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

Hey JLGWhiz
That works perfect and exactly what I need, stepping through the code
really makes it clear too thanks for this tip.
You have been very helpful
Eddie

 

[webels]

This is how I tested the code.
On Sheet 1:
I put data in columns A:E and I:K for several rows.
I put dates in column L for all rows with data with
only a few with today's date.
For the code:
I put the macro in Module1 of the VBE window.
Result:
I ran the macro and it copied only those columns with
data for the rows with today's date in column L to
Sheet 2.

One other thing to check.  If your dates are not the
same data type, i.e. either dates or text, then you
will not get the data copied.  The code assumes that
they are data type Date.  But you should get a type
mismatch error if they are not the same type.  You
can step through the code one line at a time using F8
and use the mouseover to check tooltips for the values
of the variables c and rng to see if they are what you
expect.






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

Oh yeah it was actually the worksheet names by the way

 

[JLGWhiz]

Glad it worked for you.

Regards

This is how I tested the code.
On Sheet 1:
I put data in columns A:E and I:K for several rows.
I put dates in column L for all rows with data with
only a few with today's date.
For the code:
I put the macro in Module1 of the VBE window.
Result:
I ran the macro and it copied only those columns with
data for the rows with today's date in column L to
Sheet 2.

One other thing to check. If your dates are not the
same data type, i.e. either dates or text, then you
will not get the data copied. The code assumes that
they are data type Date. But you should get a type
mismatch error if they are not the same type. You
can step through the code one line at a time using F8
and use the mouseover to check tooltips for the values
of the variables c and rng to see if they are what you
expect.






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

Oh yeah it was actually the worksheet names by the way