Copy data from 3rd worksheets and pasting to active worksheet

Hi
I need to run code that will copy data from cells B9:B1000 located on the
third worksheet Before (to the left) the activeworksheet ...and then...paste
the data into cells F9:F1000 of the Active worksheet.
In case this helps.......
The name of this third worksheet is "Details"..however, more sets of these
worksheets can be added (via macro) to this template file and I will need to
run this code from them as well.. for ex:.. when the second set of
worksheets are added..the third worksheet before the activeworksheet that is
running this code will be named Details (2) ...and so on..as more sets are
added.

I would use formulas to do this..but I need to be able to determine the last
entry in column F of the active worksheet..and formulas count even if the
results = nothing...and there could be data in cells B9:B20 or B9:B1000.. it
just depends..on that data entered...so the formulas will interfear with
this...

Any help is greatly appreciated...
Thanks in advance,
Kimberly
..

Assume the copies will be moving to the right starting with Column F as you
copy them:

Dim sh as Worksheet
Dim i as Long
Dim sh1 as Worksheet
set sh = Activesheet
i = 6 ' column F
for each sh1 in Worksheets
if Instr(1,sh.Name,"detail",vbTextCompare) then
sh1.Range("B9:B1000").Copy Destination:= _
sh.Cells(9,1)
i = i + 1
End if
Next

--
Regards,
Tom Ogilvy

"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> Hi
> I need to run code that will copy data from cells B9:B1000 located on the
> third worksheet Before (to the left) the activeworksheet ...and
then...paste
> the data into cells F9:F1000 of the Active worksheet.
> In case this helps.......
> The name of this third worksheet is "Details"..however, more sets of these
> worksheets can be added (via macro) to this template file and I will need
to
> run this code from them as well.. for ex:.. when the second set of
> worksheets are added..the third worksheet before the activeworksheet that
is
> running this code will be named Details (2) ...and so on..as more sets are
> added.
>
> I would use formulas to do this..but I need to be able to determine the
last
> entry in column F of the active worksheet..and formulas count even if the
> results = nothing...and there could be data in cells B9:B20 or B9:B1000..
it
> just depends..on that data entered...so the formulas will interfear with
> this...
>
> Any help is greatly appreciated...
> Thanks in advance,
> Kimberly
> .
>
>
>
>

Typo:

Sub ABCD()
Dim sh As Worksheet
Dim i As Long
Dim sh1 As Worksheet
Set sh = ActiveSheet
i = 6 ' column F
For Each sh1 In Worksheets
If InStr(1, sh1.Name, "detail", vbTextCompare) Then
sh1.Range("B9:B1000").Copy Destination:= _
sh.Cells(9, i)
i = i + 1
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Assume the copies will be moving to the right starting with Column F as
you
> copy them:
>
> Dim sh as Worksheet
> Dim i as Long
> Dim sh1 as Worksheet
> set sh = Activesheet
> i = 6 ' column F
> for each sh1 in Worksheets
> if Instr(1,sh.Name,"detail",vbTextCompare) then
> sh1.Range("B9:B1000").Copy Destination:= _
> sh.Cells(9,1)
> i = i + 1
> End if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
> "KimberlyC" <[email protected]> wrote in message
> news:[email protected]...
> > Hi
> > I need to run code that will copy data from cells B9:B1000 located on
the
> > third worksheet Before (to the left) the activeworksheet ...and
> then...paste
> > the data into cells F9:F1000 of the Active worksheet.
> > In case this helps.......
> > The name of this third worksheet is "Details"..however, more sets of
these
> > worksheets can be added (via macro) to this template file and I will
need
> to
> > run this code from them as well.. for ex:.. when the second set of
> > worksheets are added..the third worksheet before the activeworksheet
that
> is
> > running this code will be named Details (2) ...and so on..as more sets
are
> > added.
> >
> > I would use formulas to do this..but I need to be able to determine the
> last
> > entry in column F of the active worksheet..and formulas count even if
the
> > results = nothing...and there could be data in cells B9:B20 or
B9:B1000..
> it
> > just depends..on that data entered...so the formulas will interfear with
> > this...
> >
> > Any help is greatly appreciated...
> > Thanks in advance,
> > Kimberly
> > .
> >
> >
> >
> >
>
>

Thanks!!

This is working....Except...

When I add another set of the worksheets to the template, this code that now
runs on the new active worksheet picks up column B (cells 9:1000) on the
both Details (Details, and Details (2)) worksheets and paste the data into
col F and G (respectively) on the new active worksheet.

I only need the code to pick up the data on the the Details worksheet that
is closest to this (counting 3 worksheets over to the left)
activeworksheet.....and paste it into column F of this activeworksheet.

The Details worksheets will always be three worksheets before the
activeworksheet that I need to run this code on.

The workbook has the following worksheets in it:

Info, OP, DESC, Summary,Adjustments, Details, OW, OT, Deduction, MISC, SUB

When the second set of worksheets are added to the workbook, it looks like
this:

Info, OP, DESC, Summary, Adjustments, Details, OW, OT, Deduction,
Summary(2), Adjustments (2), Details (2), OW (2), OT (2), Deduction (2),
MISC, SUB

And.. more worksheet sets can be added...adding (3) to them.

The code below is placed behind a command button on the "Deduction"/s
worksheets and needs to only reference the "Details" worksheet that is the
closest to it on the left side (which is 3 worksheets over to the left or
before it).

Not sure if all that info helps!!

But.. any further help would be greatly appreciated!!

Thanks in advance!!

Kimberly

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Typo:
>
> Sub ABCD()
> Dim sh As Worksheet
> Dim i As Long
> Dim sh1 As Worksheet
> Set sh = ActiveSheet
> i = 6 ' column F
> For Each sh1 In Worksheets
> If InStr(1, sh1.Name, "detail", vbTextCompare) Then
> sh1.Range("B9:B1000").Copy Destination:= _
> sh.Cells(9, i)
> i = i + 1
> End If
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Assume the copies will be moving to the right starting with Column F as
> you
> > copy them:
> >
> > Dim sh as Worksheet
> > Dim i as Long
> > Dim sh1 as Worksheet
> > set sh = Activesheet
> > i = 6 ' column F
> > for each sh1 in Worksheets
> > if Instr(1,sh.Name,"detail",vbTextCompare) then
> > sh1.Range("B9:B1000").Copy Destination:= _
> > sh.Cells(9,1)
> > i = i + 1
> > End if
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "KimberlyC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi
> > > I need to run code that will copy data from cells B9:B1000 located on
> the
> > > third worksheet Before (to the left) the activeworksheet ...and
> > then...paste
> > > the data into cells F9:F1000 of the Active worksheet.
> > > In case this helps.......
> > > The name of this third worksheet is "Details"..however, more sets of
> these
> > > worksheets can be added (via macro) to this template file and I will
> need
> > to
> > > run this code from them as well.. for ex:.. when the second set of
> > > worksheets are added..the third worksheet before the activeworksheet
> that
> > is
> > > running this code will be named Details (2) ...and so on..as more sets
> are
> > > added.
> > >
> > > I would use formulas to do this..but I need to be able to determine
the
> > last
> > > entry in column F of the active worksheet..and formulas count even if
> the
> > > results = nothing...and there could be data in cells B9:B20 or
> B9:B1000..
> > it
> > > just depends..on that data entered...so the formulas will interfear
with
> > > this...
> > >
> > > Any help is greatly appreciated...
> > > Thanks in advance,
> > > Kimberly
> > > .
> > >
> > >
> > >
> > >
> >
> >
>
>

Private Sub CommandButton1_Click()
me.previous.previous.previous.Range("B9:B1000").Copy _
Me.Range("F9")
End Sub

--
Regards,
Tom Ogilvy

"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> Thanks!!
>
> This is working....Except...
>
> When I add another set of the worksheets to the template, this code that
now
> runs on the new active worksheet picks up column B (cells 9:1000) on the
> both Details (Details, and Details (2)) worksheets and paste the data into
> col F and G (respectively) on the new active worksheet.
>
> I only need the code to pick up the data on the the Details worksheet that
> is closest to this (counting 3 worksheets over to the left)
> activeworksheet.....and paste it into column F of this activeworksheet.
>
> The Details worksheets will always be three worksheets before the
> activeworksheet that I need to run this code on.
>
> The workbook has the following worksheets in it:
>
> Info, OP, DESC, Summary,Adjustments, Details, OW, OT, Deduction, MISC, SUB
>
> When the second set of worksheets are added to the workbook, it looks like
> this:
>
> Info, OP, DESC, Summary, Adjustments, Details, OW, OT, Deduction,
> Summary(2), Adjustments (2), Details (2), OW (2), OT (2), Deduction (2),
> MISC, SUB
>
> And.. more worksheet sets can be added...adding (3) to them.
>
> The code below is placed behind a command button on the "Deduction"/s
> worksheets and needs to only reference the "Details" worksheet that is the
> closest to it on the left side (which is 3 worksheets over to the left or
> before it).
>
> Not sure if all that info helps!!
>
> But.. any further help would be greatly appreciated!!
>
> Thanks in advance!!
>
> Kimberly
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Typo:
> >
> > Sub ABCD()
> > Dim sh As Worksheet
> > Dim i As Long
> > Dim sh1 As Worksheet
> > Set sh = ActiveSheet
> > i = 6 ' column F
> > For Each sh1 In Worksheets
> > If InStr(1, sh1.Name, "detail", vbTextCompare) Then
> > sh1.Range("B9:B1000").Copy Destination:= _
> > sh.Cells(9, i)
> > i = i + 1
> > End If
> > Next
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > Assume the copies will be moving to the right starting with Column F
as
> > you
> > > copy them:
> > >
> > > Dim sh as Worksheet
> > > Dim i as Long
> > > Dim sh1 as Worksheet
> > > set sh = Activesheet
> > > i = 6 ' column F
> > > for each sh1 in Worksheets
> > > if Instr(1,sh.Name,"detail",vbTextCompare) then
> > > sh1.Range("B9:B1000").Copy Destination:= _
> > > sh.Cells(9,1)
> > > i = i + 1
> > > End if
> > > Next
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "KimberlyC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi
> > > > I need to run code that will copy data from cells B9:B1000 located
on
> > the
> > > > third worksheet Before (to the left) the activeworksheet ...and
> > > then...paste
> > > > the data into cells F9:F1000 of the Active worksheet.
> > > > In case this helps.......
> > > > The name of this third worksheet is "Details"..however, more sets of
> > these
> > > > worksheets can be added (via macro) to this template file and I will
> > need
> > > to
> > > > run this code from them as well.. for ex:.. when the second set of
> > > > worksheets are added..the third worksheet before the activeworksheet
> > that
> > > is
> > > > running this code will be named Details (2) ...and so on..as more
sets
> > are
> > > > added.
> > > >
> > > > I would use formulas to do this..but I need to be able to determine
> the
> > > last
> > > > entry in column F of the active worksheet..and formulas count even
if
> > the
> > > > results = nothing...and there could be data in cells B9:B20 or
> > B9:B1000..
> > > it
> > > > just depends..on that data entered...so the formulas will interfear
> with
> > > > this...
> > > >
> > > > Any help is greatly appreciated...
> > > > Thanks in advance,
> > > > Kimberly
> > > > .
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>