Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
Hi,
I've got what seems like a simple problem here, but it has caused me some serious pain trying to code; disclaimer, I'm a macro rookie. As a way to give back to you guys for your help in advance, I've included at the bottom a simple macro I wrote to copy one cell from every n'th row of data to a column in a new sheet. Took me 3 hours (said I was a rookie), so I hope that someone finds it useful! Glad you are still with me here. I'm going to describe the problem next. It's just a copy-paste macro, but I am pretty wordy, so please don't freak out that the description is long. So I have two workbooks and I want to copy data to a new workbook. It's a lookup problem; the first sheet says which data to look up and the second contains the data. I want to copy the data to a new book. Structu The first sheet has info grouped in clumps of 6 rows from row 2 on. I want to pull 1 column entry from each row, a string (letters and numbers), I'll call it ID. (It's column B for you detail oriented people). The data sheet has ID's listed all down column A. I want to look up the ID I just pulled. Then copy column data from 4 columns in that row to a row in a new book. I want to do that for all the rows in the first sheet---but there's a catch. I only want to start a new row in the new sheet after every 6 ID lookups. (Therefore each row has 24 entries). That's it! Ideas: This section describes what I have tried... it's failed, or I wouldn't be here! However, if you don't feel like writing the whole thing, and I certainly don't blame you if that's your attitude, then please read this section and answer some of the specific questions I've posed in here. Thanks! So I tried a counter and while loop to get to the end of the first sheet (the 'what to look up' sheet) a la: lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row counter=2 while counter < lastrow+1 .... wend which seemed to work. But I couldn't figure out how to assign a temporary variable to the string I want to look up. Is it something like this? A=sheets(1).cells(counter,B) then I tried to open the workbook workbooks.open "name" and got in massive syntax issues with vlookup. B=vlookup(A, range, column of interest, FALSE) How do I define the range to go from A2 to the bottom right of the sheet? Then I tried to copy B to a new workbook with coordinates (counter mod 6 + 1, 1) and had no clue how to do that. Anyway, someone shoot some pointers my way! Thanks for reading. Here's a macro I wrote which will copy a cell from every n'th row of data starting from startrow and ending at the bottom of the sheet, go ahead and use it if you want to. Sub copysixthrow() 'i want to copy every _nth_ row n= 'starting row startrow= 'column of interest column= Application.ScreenUpdating = False lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'ceiling not necessarily an integer because im bad at coding Ceiling = (lastrow - startrow+1) / n Count = 1 While Count < Ceiling + 1 Sheets(1).Cells(n * Count -n+startrow, column).Copy _ Destination:=Sheets(2).Cells(1 + Count, 1) Count = Count + 1 Wend Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
Whoops, I made a mistake in my ideas section. I don't want to paste to that
mod thing... i think i want to divide counter by 6 and truncate for my row. How do I do that? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
You need to clarify "I want to pull 1 column entry from each row, a string
(letters and numbers), " Do you want to select an item in column B and then find all occurrences in column A, or do you want to start with the first item in column B and chech column A for all occurrences of Each item in column B, Or do you want to select a particular item in column B and find the first occurrence of the item in column A and then do the copy and paste or whatever to get it to the other sheet. It looks like you want to check each item in column B for a match in column A, but it is not clear if you want all matches or just the first one. "badmrfrosty8" wrote: Hi, I've got what seems like a simple problem here, but it has caused me some serious pain trying to code; disclaimer, I'm a macro rookie. As a way to give back to you guys for your help in advance, I've included at the bottom a simple macro I wrote to copy one cell from every n'th row of data to a column in a new sheet. Took me 3 hours (said I was a rookie), so I hope that someone finds it useful! Glad you are still with me here. I'm going to describe the problem next. It's just a copy-paste macro, but I am pretty wordy, so please don't freak out that the description is long. So I have two workbooks and I want to copy data to a new workbook. It's a lookup problem; the first sheet says which data to look up and the second contains the data. I want to copy the data to a new book. Structu The first sheet has info grouped in clumps of 6 rows from row 2 on. I want to pull 1 column entry from each row, a string (letters and numbers), I'll call it ID. (It's column B for you detail oriented people). The data sheet has ID's listed all down column A. I want to look up the ID I just pulled. Then copy column data from 4 columns in that row to a row in a new book. I want to do that for all the rows in the first sheet---but there's a catch. I only want to start a new row in the new sheet after every 6 ID lookups. (Therefore each row has 24 entries). That's it! Ideas: This section describes what I have tried... it's failed, or I wouldn't be here! However, if you don't feel like writing the whole thing, and I certainly don't blame you if that's your attitude, then please read this section and answer some of the specific questions I've posed in here. Thanks! So I tried a counter and while loop to get to the end of the first sheet (the 'what to look up' sheet) a la: lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row counter=2 while counter < lastrow+1 ... wend which seemed to work. But I couldn't figure out how to assign a temporary variable to the string I want to look up. Is it something like this? A=sheets(1).cells(counter,B) then I tried to open the workbook workbooks.open "name" and got in massive syntax issues with vlookup. B=vlookup(A, range, column of interest, FALSE) How do I define the range to go from A2 to the bottom right of the sheet? Then I tried to copy B to a new workbook with coordinates (counter mod 6 + 1, 1) and had no clue how to do that. Anyway, someone shoot some pointers my way! Thanks for reading. Here's a macro I wrote which will copy a cell from every n'th row of data starting from startrow and ending at the bottom of the sheet, go ahead and use it if you want to. Sub copysixthrow() 'i want to copy every _nth_ row n= 'starting row startrow= 'column of interest column= Application.ScreenUpdating = False lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'ceiling not necessarily an integer because im bad at coding Ceiling = (lastrow - startrow+1) / n Count = 1 While Count < Ceiling + 1 Sheets(1).Cells(n * Count -n+startrow, column).Copy _ Destination:=Sheets(2).Cells(1 + Count, 1) Count = Count + 1 Wend Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
So in book 1 column B exists ID numbers. I want to store each of these (I
think) as a temporary variable inside a loop, then open book 2, and vlookup for an exact match of the temporary variable in column A. Then I need to copy the value of columns of interest in book 2 to a row in book 3---with data corresponding to 6 id numbers forming one row. is this helpful? "JLGWhiz" wrote: You need to clarify "I want to pull 1 column entry from each row, a string (letters and numbers), " Do you want to select an item in column B and then find all occurrences in column A, or do you want to start with the first item in column B and chech column A for all occurrences of Each item in column B, Or do you want to select a particular item in column B and find the first occurrence of the item in column A and then do the copy and paste or whatever to get it to the other sheet. It looks like you want to check each item in column B for a match in column A, but it is not clear if you want all matches or just the first one. "badmrfrosty8" wrote: Hi, I've got what seems like a simple problem here, but it has caused me some serious pain trying to code; disclaimer, I'm a macro rookie. As a way to give back to you guys for your help in advance, I've included at the bottom a simple macro I wrote to copy one cell from every n'th row of data to a column in a new sheet. Took me 3 hours (said I was a rookie), so I hope that someone finds it useful! Glad you are still with me here. I'm going to describe the problem next. It's just a copy-paste macro, but I am pretty wordy, so please don't freak out that the description is long. So I have two workbooks and I want to copy data to a new workbook. It's a lookup problem; the first sheet says which data to look up and the second contains the data. I want to copy the data to a new book. Structu The first sheet has info grouped in clumps of 6 rows from row 2 on. I want to pull 1 column entry from each row, a string (letters and numbers), I'll call it ID. (It's column B for you detail oriented people). The data sheet has ID's listed all down column A. I want to look up the ID I just pulled. Then copy column data from 4 columns in that row to a row in a new book. I want to do that for all the rows in the first sheet---but there's a catch. I only want to start a new row in the new sheet after every 6 ID lookups. (Therefore each row has 24 entries). That's it! Ideas: This section describes what I have tried... it's failed, or I wouldn't be here! However, if you don't feel like writing the whole thing, and I certainly don't blame you if that's your attitude, then please read this section and answer some of the specific questions I've posed in here. Thanks! So I tried a counter and while loop to get to the end of the first sheet (the 'what to look up' sheet) a la: lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row counter=2 while counter < lastrow+1 ... wend which seemed to work. But I couldn't figure out how to assign a temporary variable to the string I want to look up. Is it something like this? A=sheets(1).cells(counter,B) then I tried to open the workbook workbooks.open "name" and got in massive syntax issues with vlookup. B=vlookup(A, range, column of interest, FALSE) How do I define the range to go from A2 to the bottom right of the sheet? Then I tried to copy B to a new workbook with coordinates (counter mod 6 + 1, 1) and had no clue how to do that. Anyway, someone shoot some pointers my way! Thanks for reading. Here's a macro I wrote which will copy a cell from every n'th row of data starting from startrow and ending at the bottom of the sheet, go ahead and use it if you want to. Sub copysixthrow() 'i want to copy every _nth_ row n= 'starting row startrow= 'column of interest column= Application.ScreenUpdating = False lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'ceiling not necessarily an integer because im bad at coding Ceiling = (lastrow - startrow+1) / n Count = 1 While Count < Ceiling + 1 Sheets(1).Cells(n * Count -n+startrow, column).Copy _ Destination:=Sheets(2).Cells(1 + Count, 1) Count = Count + 1 Wend Application.ScreenUpdating = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
To further clarify, I have an ID string in each row of sheet 1 that is
composed of both letters and numbers. Sheet 2 has as column entries information that corresponds to each ID string, so for example a sample row would be ID | price | weight| etc... I want to match on the ID, then pull some corresponding entries from columns in sheet 2 such as price for ID #1, weight for ID #1, etc. The final product should be an array with entries for 6 id numbers in each row. example: price for ID1 | weight for ID1 | ...| price for ID2 | weight for 2 | ....|id_k info|... |id6 info| |ROW BREAK| "badmrfrosty8" wrote: So in book 1 column B exists ID numbers. I want to store each of these (I think) as a temporary variable inside a loop, then open book 2, and vlookup for an exact match of the temporary variable in column A. Then I need to copy the value of columns of interest in book 2 to a row in book 3---with data corresponding to 6 id numbers forming one row. is this helpful? "JLGWhiz" wrote: You need to clarify "I want to pull 1 column entry from each row, a string (letters and numbers), " Do you want to select an item in column B and then find all occurrences in column A, or do you want to start with the first item in column B and chech column A for all occurrences of Each item in column B, Or do you want to select a particular item in column B and find the first occurrence of the item in column A and then do the copy and paste or whatever to get it to the other sheet. It looks like you want to check each item in column B for a match in column A, but it is not clear if you want all matches or just the first one. "badmrfrosty8" wrote: Hi, I've got what seems like a simple problem here, but it has caused me some serious pain trying to code; disclaimer, I'm a macro rookie. As a way to give back to you guys for your help in advance, I've included at the bottom a simple macro I wrote to copy one cell from every n'th row of data to a column in a new sheet. Took me 3 hours (said I was a rookie), so I hope that someone finds it useful! Glad you are still with me here. I'm going to describe the problem next. It's just a copy-paste macro, but I am pretty wordy, so please don't freak out that the description is long. So I have two workbooks and I want to copy data to a new workbook. It's a lookup problem; the first sheet says which data to look up and the second contains the data. I want to copy the data to a new book. Structu The first sheet has info grouped in clumps of 6 rows from row 2 on. I want to pull 1 column entry from each row, a string (letters and numbers), I'll call it ID. (It's column B for you detail oriented people). The data sheet has ID's listed all down column A. I want to look up the ID I just pulled. Then copy column data from 4 columns in that row to a row in a new book. I want to do that for all the rows in the first sheet---but there's a catch. I only want to start a new row in the new sheet after every 6 ID lookups. (Therefore each row has 24 entries). That's it! Ideas: This section describes what I have tried... it's failed, or I wouldn't be here! However, if you don't feel like writing the whole thing, and I certainly don't blame you if that's your attitude, then please read this section and answer some of the specific questions I've posed in here. Thanks! So I tried a counter and while loop to get to the end of the first sheet (the 'what to look up' sheet) a la: lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row counter=2 while counter < lastrow+1 ... wend which seemed to work. But I couldn't figure out how to assign a temporary variable to the string I want to look up. Is it something like this? A=sheets(1).cells(counter,B) then I tried to open the workbook workbooks.open "name" and got in massive syntax issues with vlookup. B=vlookup(A, range, column of interest, FALSE) How do I define the range to go from A2 to the bottom right of the sheet? Then I tried to copy B to a new workbook with coordinates (counter mod 6 + 1, 1) and had no clue how to do that. Anyway, someone shoot some pointers my way! Thanks for reading. Here's a macro I wrote which will copy a cell from every n'th row of data starting from startrow and ending at the bottom of the sheet, go ahead and use it if you want to. Sub copysixthrow() 'i want to copy every _nth_ row n= 'starting row startrow= 'column of interest column= Application.ScreenUpdating = False lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'ceiling not necessarily an integer because im bad at coding Ceiling = (lastrow - startrow+1) / n Count = 1 While Count < Ceiling + 1 Sheets(1).Cells(n * Count -n+startrow, column).Copy _ Destination:=Sheets(2).Cells(1 + Count, 1) Count = Count + 1 Wend Application.ScreenUpdating = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
This is somewhat of a clunge, but see if it does what you want. I might have
the sheet numbers screwed around, but you can work with that. Sub makeNewList() Dim lr1 As Long, lr2 As Long, lr3 As Long Dim x As Long, y As Long Set ws1 = ThisWorkbook.Sheets(1) Set ws2 = ThisWorkbook.Sheets(2) Set ws3 = ThisWorkbook.Sheets(3) lr2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row x = 2 y = 1 For Each ID In ws2.Range("B2:B" & lr2) For Each c In ws1.Range("A2:A" & lr1) If ID = c Then With ws3 lc3 = .Cells(x, Columns.Count).End(xlToLeft).Column If lc3 = 1 Then ws1.Range("A" & c.Row & ":D" & c.Row).Copy _ ws3.Cells(x, lc3) y = y + 1 Else ws1.Range("A" & c.Row & ":D" & c.Row).Copy _ ws3.Cells(x, lc3 + 1) y = y + 1 End If If y = 7 Then x = x + 1 y = 1 End If End With End If Next Next End Sub "badmrfrosty8" wrote: To further clarify, I have an ID string in each row of sheet 1 that is composed of both letters and numbers. Sheet 2 has as column entries information that corresponds to each ID string, so for example a sample row would be ID | price | weight| etc... I want to match on the ID, then pull some corresponding entries from columns in sheet 2 such as price for ID #1, weight for ID #1, etc. The final product should be an array with entries for 6 id numbers in each row. example: price for ID1 | weight for ID1 | ...| price for ID2 | weight for 2 | ...|id_k info|... |id6 info| |ROW BREAK| "badmrfrosty8" wrote: So in book 1 column B exists ID numbers. I want to store each of these (I think) as a temporary variable inside a loop, then open book 2, and vlookup for an exact match of the temporary variable in column A. Then I need to copy the value of columns of interest in book 2 to a row in book 3---with data corresponding to 6 id numbers forming one row. is this helpful? "JLGWhiz" wrote: You need to clarify "I want to pull 1 column entry from each row, a string (letters and numbers), " Do you want to select an item in column B and then find all occurrences in column A, or do you want to start with the first item in column B and chech column A for all occurrences of Each item in column B, Or do you want to select a particular item in column B and find the first occurrence of the item in column A and then do the copy and paste or whatever to get it to the other sheet. It looks like you want to check each item in column B for a match in column A, but it is not clear if you want all matches or just the first one. "badmrfrosty8" wrote: Hi, I've got what seems like a simple problem here, but it has caused me some serious pain trying to code; disclaimer, I'm a macro rookie. As a way to give back to you guys for your help in advance, I've included at the bottom a simple macro I wrote to copy one cell from every n'th row of data to a column in a new sheet. Took me 3 hours (said I was a rookie), so I hope that someone finds it useful! Glad you are still with me here. I'm going to describe the problem next. It's just a copy-paste macro, but I am pretty wordy, so please don't freak out that the description is long. So I have two workbooks and I want to copy data to a new workbook. It's a lookup problem; the first sheet says which data to look up and the second contains the data. I want to copy the data to a new book. Structu The first sheet has info grouped in clumps of 6 rows from row 2 on. I want to pull 1 column entry from each row, a string (letters and numbers), I'll call it ID. (It's column B for you detail oriented people). The data sheet has ID's listed all down column A. I want to look up the ID I just pulled. Then copy column data from 4 columns in that row to a row in a new book. I want to do that for all the rows in the first sheet---but there's a catch. I only want to start a new row in the new sheet after every 6 ID lookups. (Therefore each row has 24 entries). That's it! Ideas: This section describes what I have tried... it's failed, or I wouldn't be here! However, if you don't feel like writing the whole thing, and I certainly don't blame you if that's your attitude, then please read this section and answer some of the specific questions I've posed in here. Thanks! So I tried a counter and while loop to get to the end of the first sheet (the 'what to look up' sheet) a la: lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row counter=2 while counter < lastrow+1 ... wend which seemed to work. But I couldn't figure out how to assign a temporary variable to the string I want to look up. Is it something like this? A=sheets(1).cells(counter,B) then I tried to open the workbook workbooks.open "name" and got in massive syntax issues with vlookup. B=vlookup(A, range, column of interest, FALSE) How do I define the range to go from A2 to the bottom right of the sheet? Then I tried to copy B to a new workbook with coordinates (counter mod 6 + 1, 1) and had no clue how to do that. Anyway, someone shoot some pointers my way! Thanks for reading. Here's a macro I wrote which will copy a cell from every n'th row of data starting from startrow and ending at the bottom of the sheet, go ahead and use it if you want to. Sub copysixthrow() 'i want to copy every _nth_ row n= 'starting row startrow= 'column of interest column= Application.ScreenUpdating = False lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'ceiling not necessarily an integer because im bad at coding Ceiling = (lastrow - startrow+1) / n Count = 1 While Count < Ceiling + 1 Sheets(1).Cells(n * Count -n+startrow, column).Copy _ Destination:=Sheets(2).Cells(1 + Count, 1) Count = Count + 1 Wend Application.ScreenUpdating = True End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
Thanks, with some simple mods this will work fine. I appreciate your time!
"JLGWhiz" wrote: This is somewhat of a clunge, but see if it does what you want. I might have the sheet numbers screwed around, but you can work with that. Sub makeNewList() Dim lr1 As Long, lr2 As Long, lr3 As Long Dim x As Long, y As Long Set ws1 = ThisWorkbook.Sheets(1) Set ws2 = ThisWorkbook.Sheets(2) Set ws3 = ThisWorkbook.Sheets(3) lr2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row x = 2 y = 1 For Each ID In ws2.Range("B2:B" & lr2) For Each c In ws1.Range("A2:A" & lr1) If ID = c Then With ws3 lc3 = .Cells(x, Columns.Count).End(xlToLeft).Column If lc3 = 1 Then ws1.Range("A" & c.Row & ":D" & c.Row).Copy _ ws3.Cells(x, lc3) y = y + 1 Else ws1.Range("A" & c.Row & ":D" & c.Row).Copy _ ws3.Cells(x, lc3 + 1) y = y + 1 End If If y = 7 Then x = x + 1 y = 1 End If End With End If Next Next End Sub "badmrfrosty8" wrote: To further clarify, I have an ID string in each row of sheet 1 that is composed of both letters and numbers. Sheet 2 has as column entries information that corresponds to each ID string, so for example a sample row would be ID | price | weight| etc... I want to match on the ID, then pull some corresponding entries from columns in sheet 2 such as price for ID #1, weight for ID #1, etc. The final product should be an array with entries for 6 id numbers in each row. example: price for ID1 | weight for ID1 | ...| price for ID2 | weight for 2 | ...|id_k info|... |id6 info| |ROW BREAK| "badmrfrosty8" wrote: So in book 1 column B exists ID numbers. I want to store each of these (I think) as a temporary variable inside a loop, then open book 2, and vlookup for an exact match of the temporary variable in column A. Then I need to copy the value of columns of interest in book 2 to a row in book 3---with data corresponding to 6 id numbers forming one row. is this helpful? "JLGWhiz" wrote: You need to clarify "I want to pull 1 column entry from each row, a string (letters and numbers), " Do you want to select an item in column B and then find all occurrences in column A, or do you want to start with the first item in column B and chech column A for all occurrences of Each item in column B, Or do you want to select a particular item in column B and find the first occurrence of the item in column A and then do the copy and paste or whatever to get it to the other sheet. It looks like you want to check each item in column B for a match in column A, but it is not clear if you want all matches or just the first one. "badmrfrosty8" wrote: Hi, I've got what seems like a simple problem here, but it has caused me some serious pain trying to code; disclaimer, I'm a macro rookie. As a way to give back to you guys for your help in advance, I've included at the bottom a simple macro I wrote to copy one cell from every n'th row of data to a column in a new sheet. Took me 3 hours (said I was a rookie), so I hope that someone finds it useful! Glad you are still with me here. I'm going to describe the problem next. It's just a copy-paste macro, but I am pretty wordy, so please don't freak out that the description is long. So I have two workbooks and I want to copy data to a new workbook. It's a lookup problem; the first sheet says which data to look up and the second contains the data. I want to copy the data to a new book. Structu The first sheet has info grouped in clumps of 6 rows from row 2 on. I want to pull 1 column entry from each row, a string (letters and numbers), I'll call it ID. (It's column B for you detail oriented people). The data sheet has ID's listed all down column A. I want to look up the ID I just pulled. Then copy column data from 4 columns in that row to a row in a new book. I want to do that for all the rows in the first sheet---but there's a catch. I only want to start a new row in the new sheet after every 6 ID lookups. (Therefore each row has 24 entries). That's it! Ideas: This section describes what I have tried... it's failed, or I wouldn't be here! However, if you don't feel like writing the whole thing, and I certainly don't blame you if that's your attitude, then please read this section and answer some of the specific questions I've posed in here. Thanks! So I tried a counter and while loop to get to the end of the first sheet (the 'what to look up' sheet) a la: lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row counter=2 while counter < lastrow+1 ... wend which seemed to work. But I couldn't figure out how to assign a temporary variable to the string I want to look up. Is it something like this? A=sheets(1).cells(counter,B) then I tried to open the workbook workbooks.open "name" and got in massive syntax issues with vlookup. B=vlookup(A, range, column of interest, FALSE) How do I define the range to go from A2 to the bottom right of the sheet? Then I tried to copy B to a new workbook with coordinates (counter mod 6 + 1, 1) and had no clue how to do that. Anyway, someone shoot some pointers my way! Thanks for reading. Here's a macro I wrote which will copy a cell from every n'th row of data starting from startrow and ending at the bottom of the sheet, go ahead and use it if you want to. Sub copysixthrow() 'i want to copy every _nth_ row n= 'starting row startrow= 'column of interest column= Application.ScreenUpdating = False lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'ceiling not necessarily an integer because im bad at coding Ceiling = (lastrow - startrow+1) / n Count = 1 While Count < Ceiling + 1 Sheets(1).Cells(n * Count -n+startrow, column).Copy _ Destination:=Sheets(2).Cells(1 + Count, 1) Count = Count + 1 Wend Application.ScreenUpdating = True End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
Sorry to bother you again; just one more question. Was there anything in
this code that will only work for small data sets? I've gotten it to work for a small test sheet absolutely perfectly, but when I run the exact same code on a monster sheet (250k rows in sheet 1, 2k rows in sheet 2) I get a type mismatch error on the third line of this: For Each ID In ws1.Range("B2:B" & lr2) For Each c In ws2.Range("A2:A" & lr1) If ID = c Then What should I change to get this to run on a big sheet? Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
The size of the database should not cause the type mismatch error. However,
datatypes in the range very well could. You need to identify exactly where the error occurs and check the data types for that range. "badmrfrosty8" wrote: Sorry to bother you again; just one more question. Was there anything in this code that will only work for small data sets? I've gotten it to work for a small test sheet absolutely perfectly, but when I run the exact same code on a monster sheet (250k rows in sheet 1, 2k rows in sheet 2) I get a type mismatch error on the third line of this: For Each ID In ws1.Range("B2:B" & lr2) For Each c In ws2.Range("A2:A" & lr1) If ID = c Then What should I change to get this to run on a big sheet? Thanks! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic Data Macro Q | Free Copy Macro
You could try changing to this:
For Each ID In ws1.Range("B2:B" & lr2) For Each c In ws2.Range("A2:A" & lr1) If ID.Value = c.Value Then That would take any formulas out of the mix. That could be the cause of your type mismatch. "badmrfrosty8" wrote: Sorry to bother you again; just one more question. Was there anything in this code that will only work for small data sets? I've gotten it to work for a small test sheet absolutely perfectly, but when I run the exact same code on a monster sheet (250k rows in sheet 1, 2k rows in sheet 2) I get a type mismatch error on the third line of this: For Each ID In ws1.Range("B2:B" & lr2) For Each c In ws2.Range("A2:A" & lr1) If ID = c Then What should I change to get this to run on a big sheet? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cyclic copy | Excel Programming | |||
Graphing cyclic data | Excel Discussion (Misc queries) | |||
Macro Free Workbook? | Excel Programming | |||
Cyclic Error replaced by blank or something ? would Macro help? | Excel Programming | |||
Macro to find the next free line | Excel Programming |