Cyclic Data Macro Q | Free Copy Macro

[badmrfrosty8]

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.

Structure:
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

 

[badmrfrosty8]

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?

 

[JLGWhiz]

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]

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?

 

[badmrfrosty8]

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|

 

[JLGWhiz]

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 & "" & c.Row).Copy _
ws3.Cells(x, lc3)
y = y + 1
Else
ws1.Range("A" & c.Row & "" & 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]

Thanks, with some simple mods this will work fine. I appreciate your time!

 

[badmrfrosty8]

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!

 

[JLGWhiz]

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.

 

[JLGWhiz]

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.