Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy info between 2 specific words
The 1st macro was closer to what you wanted than the 2nd. If ther are more
than 256/4 setctions that need to be copied then you are goig to go past column 256 and an error will occur. the macro could be easily mdoifed when 256 column (IV) is reached the code will wrap back to column A. Change sheet names as required. Sub GetData() Set Sourcesht = Sheets("Sheet1") Set DestSht = Sheets("Sheet2") StartCol = "A" EndCol = "D" SearchWord = "Done" RowCount = 1 StartRow = 0 With Sourcesht LastRow = .Range(StartCol & Rows.Count).End(xlUp).Row Found = False For RowCount = 1 To LastRow Select Case Found Case False: If .Range(StartCol & RowCount) = SearchWord Then Found = True StartRow = RowCount End If Case True If .Range(StartCol & RowCount) = SearchWord Then Set CopyRange = _ .Range(StartCol & StartRow & ":" & _ EndCol & RowCount) With DestSht LastCol = .Cells(1, Columns.Count).End(xlToLeft).Row NewCol = LastCol + 1 CopyRange.Copy _ Destination:=.Cells(1, NewCol) End With Found = False End If End Select Next RowCount End With End Sub "Wolfwalker721" wrote: Thanks this is a big help, to clarify I am trying to search the entire A column where the word "Done" appears many times, then copy Column A-D between each instance of the word "Done" and paste it to another sheet in the first empty colum. "Joel" wrote: This is similar code for columns. I'm not sure if you are looking a one row or moving down the worksheet and looking at all rows. You need to use CELLS instead of RANGE when using columns. "Wolfwalker721" wrote: This works great! Only I am trying to copy the info in the first blank column instead of the first blank row. =) Thanks so much for yout time! "Joel" wrote: Change the sheet names and the start and end columns as required. Sub GetData() Set Sourcesht = Sheets("Sheet1") Set DestSht = Sheets("Sheet2") StartCol = "C" EndCol = "G" SearchWord = "Joel" RowCount = 1 StartRow = 0 With Sourcesht LastRow1 = .Range(StartCol & Rows.Count).End(xlUp).Row Found = False For RowCount = 1 To LastRow1 Select Case Found Case False: If .Range(StartCol & RowCount) = SearchWord Then Found = True StartRow = RowCount End If Case True If .Range(StartCol & RowCount) = SearchWord Then Set CopyRange = _ .Range(StartCol & StartRow & ":" & _ EndCol & RowCount) With DestSht LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow2 + 1 CopyRange.Copy _ Destination:=.Range("A" & NewRow) End With Found = False End If End Select Next RowCount End With End Sub "Wolfwalker721" wrote: Hi, I have been trying to use a macro to find a word in a column, then find the next instance of that word in the same colum, Then copy the information between+4 colums to another sheet, in the first blank column, in the same workbook. I hope this makes sence. I can generaly grasp basic macro commands but I am in over my head on this one. Any help would be appriciated. THANKS in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy info between 2 specific words
This is GREAT! THANKS
"Joel" wrote: The 1st macro was closer to what you wanted than the 2nd. If ther are more than 256/4 setctions that need to be copied then you are goig to go past column 256 and an error will occur. the macro could be easily mdoifed when 256 column (IV) is reached the code will wrap back to column A. Change sheet names as required. Sub GetData() Set Sourcesht = Sheets("Sheet1") Set DestSht = Sheets("Sheet2") StartCol = "A" EndCol = "D" SearchWord = "Done" RowCount = 1 StartRow = 0 With Sourcesht LastRow = .Range(StartCol & Rows.Count).End(xlUp).Row Found = False For RowCount = 1 To LastRow Select Case Found Case False: If .Range(StartCol & RowCount) = SearchWord Then Found = True StartRow = RowCount End If Case True If .Range(StartCol & RowCount) = SearchWord Then Set CopyRange = _ .Range(StartCol & StartRow & ":" & _ EndCol & RowCount) With DestSht LastCol = .Cells(1, Columns.Count).End(xlToLeft).Row NewCol = LastCol + 1 CopyRange.Copy _ Destination:=.Cells(1, NewCol) End With Found = False End If End Select Next RowCount End With End Sub "Wolfwalker721" wrote: Thanks this is a big help, to clarify I am trying to search the entire A column where the word "Done" appears many times, then copy Column A-D between each instance of the word "Done" and paste it to another sheet in the first empty colum. "Joel" wrote: This is similar code for columns. I'm not sure if you are looking a one row or moving down the worksheet and looking at all rows. You need to use CELLS instead of RANGE when using columns. "Wolfwalker721" wrote: This works great! Only I am trying to copy the info in the first blank column instead of the first blank row. =) Thanks so much for yout time! "Joel" wrote: Change the sheet names and the start and end columns as required. Sub GetData() Set Sourcesht = Sheets("Sheet1") Set DestSht = Sheets("Sheet2") StartCol = "C" EndCol = "G" SearchWord = "Joel" RowCount = 1 StartRow = 0 With Sourcesht LastRow1 = .Range(StartCol & Rows.Count).End(xlUp).Row Found = False For RowCount = 1 To LastRow1 Select Case Found Case False: If .Range(StartCol & RowCount) = SearchWord Then Found = True StartRow = RowCount End If Case True If .Range(StartCol & RowCount) = SearchWord Then Set CopyRange = _ .Range(StartCol & StartRow & ":" & _ EndCol & RowCount) With DestSht LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow2 + 1 CopyRange.Copy _ Destination:=.Range("A" & NewRow) End With Found = False End If End Select Next RowCount End With End Sub "Wolfwalker721" wrote: Hi, I have been trying to use a macro to find a word in a column, then find the next instance of that word in the same colum, Then copy the information between+4 colums to another sheet, in the first blank column, in the same workbook. I hope this makes sence. I can generaly grasp basic macro commands but I am in over my head on this one. Any help would be appriciated. THANKS in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy info into empty cells below info, until finds cell with new d | Excel Discussion (Misc queries) | |||
counting occurrence of specific words in another group of words | Excel Programming | |||
search for a specific word and copy the word and the preceeding words until a comma | Excel Programming | |||
Highlight a row if a specific cell is specific numbers/words | Excel Worksheet Functions | |||
how do you paste just specific info and not the hidden info? | Excel Discussion (Misc queries) |