moving data

[Guest]

I have a massive list of raw data that I am wanting to put into individual
sheets. i.e
All those rows that start in column A with Powercar1 into sheet called
Power, same for WaterCar1 into Water etc. Snip it below of data
Is this possible.....I've had a look at the forum and can't find any clues
to help me.
I'm not wanting to copy and paste
If I put a formula into the first column of each sheet to lookup the
reference, I get the same data repeated...........it does not bring each in.
If necessary data can be added to the raw data if it helps putting it into
seperate sheets. Please help


Column A Column B Column C

POWERCAR0 36 WF05XXGBB56T88939
POWERCAR0 36 WF05XXGBB56T89898
BTCAR0 35 WF05XXGBB56T89963
GOVERNMENT DEPARTMENTSCAR1 117 WF05XXGBB56Y39111
WATERCAR1 117 WF05XXGBB56Y39978
GOVERNMENT DEPARTMENTSCAR1 117 WF05XXGBB56Y40092
POWERCAR1 111 WF05XXGBB56Y45247
POWERCAR1 96 WF05XXGBB56Y48127
AFFILIATESCAR1 106 WF05XXGBB56Y48299

 

[Otto Moehrbach]

I take it that the sheet names are the entries in Column A to the left of
"CAR". Is that right? Are all the sheet names included in your snippet of
data? Do all the sheets already exist?
I also take it that you don't want to change the original list of raw data,
that you want to just copy it. Is that right? Otto

 

[Otto Moehrbach]

Here is a macro that will do what you want. For this macro to operate
correctly the following conditions must exist:
The active sheet is the sheet that contains your raw data.
The raw data is in Columns A:C as you said.
There must a list of all the sheet names in some column of some sheet in
your file and that list must be named "SheetList".
Note that this macro copies Columns B:C and pastes that data into Column A
of the appropriate sheets.
Sub CopyData()
Dim ShtName As Range
Dim RngColA As Range
Dim i As Range
Dim Dest As Range
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each ShtName In Range("SheetList")
With Sheets(ShtName.Value)
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
For Each i In RngColA
If Left(i, Len(ShtName.Value)) = ShtName.Value Then
i.Offset(, 1).Resize(, 2).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
Next ShtName
End Sub

 

[Guest]

Otto

Many thanks for that. Couple of pointers as I'm newish to coding.
I have the sheets already created i.e Power, Water etc.
The data i need moving from the raw data to the relevant sheet is from
column M to BF.
WHn i have tried to run this, I get the debug error at 'For Each ShtName In
Range("SheetList")'. Do I need to write the names of the sheets into the
coding
Please help as I really would like to uderstand this, and this will help for
future stuff I need to do

 

[Otto Moehrbach]

The code logic flow is this: The code loops through all the sheet names.
For each sheet name, it looks through all the data in Column A looking for
any entry that starts with that one sheet name. Every time it finds such an
entry, it does the copying. When it has gone through all of Column A, it
repeats the process with the next sheet name.
In order for this to work, the code needs to know the names of all the
sheets. There are several ways to tell the code what the sheet names are.
I chose to put all the names in a list in some out-of-the-way column, in
some out-of-the-way sheet, and I named that list "SheetList". The line of
code that you cite as the error line starts a "For" loop through all the
sheet names in "SheetList". It is therefore necessary that you enter a list
of the sheet names somewhere and name that list "SheetList".
It is also possible that you are a victim of the line wrapping that happens
with these messages. Perhaps the code lines got wrapped in transmission and
you copied what you saw. Excel will error out if a code line that is
supposed to be one line is actually 2 lines.
You say that you want columns M:BF copied but you don't say what the
destination column is. I'll assume it's still Column A. The macro below
reflects that change.
I still have the little file that I used for this. If you wish me to
send it to you, email me direct and I'll send it to you. My email address
is (e-mail address removed). Remove the "nop" from this address. HTH Otto
Sub CopyData()
Dim ShtName As Range
Dim RngColA As Range
Dim i As Range
Dim Dest As Range
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each ShtName In Range("SheetList")
With Sheets(ShtName.Value)
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
For Each i In RngColA
If Left(i, Len(ShtName.Value)) = ShtName.Value Then
i.Offset(, 12).Resize(, 46).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
Next ShtName
End Sub