Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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. "webels" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
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. "webels" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
On Jul 21, 5:50*pm, "JLGWhiz" wrote:
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. "webels" wrote in message ... 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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
On Jul 21, 5:50*pm, "JLGWhiz" wrote:
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. "webels" wrote in message ... 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 Oh yeah it was actually the worksheet names by the way |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer data between two worksheets
Glad it worked for you.
Regards "webels" wrote in message ... On Jul 21, 5:50 pm, "JLGWhiz" wrote: 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. "webels" wrote in message ... 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 Oh yeah it was actually the worksheet names by the way |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer data from multiple worksheets | Excel Discussion (Misc queries) | |||
transfer data between worksheets | Excel Programming | |||
transfer data between worksheets | Excel Programming | |||
Transfer data between worksheets | Excel Programming | |||
Transfer data between worksheets | Excel Programming |