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