Thread: macro to copy range of cells to another sheet

i am new to VBA, i've searched an have been unable to locate exactly what it is i need. i'm trying to find a specific value "NAME" on sheet(Roster) then copy a range of cells (located value say A18 through end of list(end of the list will vary) say the end is J94) to another worksheet(Student Info) starting at say A8

Any Help in this matter would be greatly appriciated, i've come very close in many ways, but never quite right, thanks again for anyone who has the time.

Sure, we are here to help you out. Can you attach a sample workbook so its easier to understand what you need?

First off, thank you for the quick response. this is the workbook in question, along with a cleaned( of sensative Data) template of the data i'm importing. i will add a sheet Student info, where i will then copy the infoi'm searching for to, then i plan to link all the other documents to that sheet. the text doc doesn't have a very standard format, i thought this would be the best approach. thanks again for what ever help you are able to provide.

The file you attached doesn't have the tab "Student Info". Even after seeing the attachments, i am not able to understand what the problem is.

So ideally, we need to copy data from the text file and put it into excel right? And the copy has to be done from the right row onwards so that the other sheets dont get misaligned

Originally Posted by arlu1201

So ideally, we need to copy data from the text file and put it into excel right? And the copy has to be done from the right row onwards so that the other sheets dont get misaligned

Thats right, preferably starting at the line with the word "NAME" in the cell, and ending at the last name in the formatted feilds, the class size hcanges regularly. then i can run other more specific searches to populate the class number ext. but unless i get the Names right, then its somewhat usless. Thanks again for your help, i'm certain its simple, i'm just learning at this point.

I have got this code which enables you to copy data starting from "Name" only.

sub import_data()
Workbooks.OpenText Filename:="D:\Test\TEST ROSTER.txt", Origin:=437, _
        StartRow:=19, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
        (3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array( _
        10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), TrailingMinusNumbers:=True

end sub

Change the path in this code to reflect your path where the file is stored.
There was no specific delimiter i could use, so i used the space delimiter, which resulted in the data getting segregated into many columns. Please provide me a sample of what the data should be under each heading and i can help you with the code to get it sorted out.