VBA Coding Help - somewhat complex

[The Whale]

Hey Guys,

I need some help on setting up a heavy macro that I'm basically trying to put together in pieces. I need some coding that will sort through column A for a chunk of cells with data in them under a cell with "SERIES" in it.

What code can I insert to make it so that for all given populated cells after "Series" but before the next cell labeled "Series" I can have the data in a given column (let's say column G) and the same row as the original cell referenced in column A. I then need to have that cell's data copied/inserted into another worksheet by referencing a date (year more specifically) in column A of the new worksheet that matches column N (same row as original cell reference) in the original worksheet.

I know that this is a rather complex request and that I may be phrasing this in a confusing way however any help would be greatly appreciated if you'll bear with me!

Thanks,
The Whale

 

[VoG]

Maybe like this

Sub anystuff()
Dim F1 As Range, F2 As Range
Set F1 = Columns("A").Find(what:="Series", LookIn:=xlValues, lookat:=xlValues)
Set F2 = Columns("A").Find(what:="Series1", after:=F1, LookIn:=xlValues, lookat:=xlValues)
'
'F1 and F2 define your renge
'
End Sub

 

[The Whale]

could you clarify a bit on how exactly to format the inputs for this function? I'm a bit new to the coding that you're showing... help much appreciated

 

[Jerry Sullivan]

could you clarify a bit on how exactly to format the inputs for this function? I'm a bit new to the coding that you're showing... help much appreciated

I'm not clearly understanding which rows from Column G you are want to copy once you find the text "SERIES".
If you post a screen shot it will help.

Using your description, I've taken a guess as to what the sheet looks like.
Even if this is incorrect, hopefully the image plus the corresponding code example below will help you follow one way to apply the code VoG provided.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Parts
2	SERIES: A						Specs							2001
3	Part 1						11111
4	Part 2						22222
5	Part 3						33333
6	SERIES: B						Specs							2004
7	Part 1						11111
8	Part 2						22222
9	Part 3						33333
Sheet1

Sub anystuff()
    Dim F1 As Range, F2 As Range
    Dim sYear As String, lRow As Long
   
'---Find first instance of "SERIES"
    Set F1 = Columns("A").Find(What:="SERIES", LookIn:=xlValues, LookAt:=xlPart)
    
'---Find second instance of "SERIES"
    Set F2 = Columns("A").Find(What:="SERIES", After:=F1, _
        LookIn:=xlValues, LookAt:=xlPart)
    
'---Get year in Col N
    sYear = Cells(F1.Row, "N")
    
'---Find matching row on other sheet
    lRow = Sheets("OtherSheet").Columns("A").Find(What:=sYear, _
            LookIn:=xlValues, LookAt:=xlWhole).Row
    
'---Copy Series data in Column G to Column B of Other Sheet
    Range(F1, F2.Offset(-1)).Offset(0, 6).Copy
    Sheets("OtherSheet").Cells(lRow, "B").PasteSpecial (xlPasteValues)
    
    Application.CutCopyMode = False
End Sub

After running macro, data is copied to the other sheet at Year 2001...

Excel Workbook
	A	B	C
1	Year
2	2000
3
4
5
6
7	2001	Specs
8		11111
9		22222
10		33333
11
12	2002
OtherSheet