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