Hi All,

I am sure this is not as difficult as my man flu addled brain is making it.

I have a number of worksheets in a workbook that have the relevant
information I want to copy in the same range (A71:I71). I then have a
seperate workbook that I want to paste the values onto the next blank row, so
I can make a summary page.

The following code is what I have cobbled together from various posts (Kudos
to those who may recognise bits of their code! I know some of it came from
Ron db)

The source ranges will be present on all of the worksheets within the
workbook but will all have different names and the number of worksheets will
differ everytime this macro is run. The code seems to fall down as it only
copy & pastes from the first worksheet and then seems to ignore the rest!

The source worksheets are in a password protected workbook (that bit works
fine!)

Any help greatly appreciated.

Sub CopyDetails()

Dim wbk As Workbook
Dim sh As Worksheet
Dim Sourcewb As Workbook

Set wbk = Workbooks.Open(Filename:="filelocation", UpdateLinks:=True,
Password:="password")
Set Sourcewb = ThisWorkbook

For Each sh In Sourcewb.Worksheets
Range("A71:I71").Copy Destination:=Workbooks("Report").Worksheets("TM
Overview").Cells(Rows.Count, 1).End(xlUp)(2)
Next sh

End Sub

Make sure you pick up the values in the correct sheet:

Sh.Range("A71:I71").Copy _
Destination:=Workbooks("Report").Worksheets("TM Overview") _
.Cells(Rows.Count, 1).End(xlUp)(2)

Those unqualified ranges will point at the active sheet.



BaggieDan wrote:
>
> Hi All,
>
> I am sure this is not as difficult as my man flu addled brain is making it.
>
> I have a number of worksheets in a workbook that have the relevant
> information I want to copy in the same range (A71:I71). I then have a
> seperate workbook that I want to paste the values onto the next blank row, so
> I can make a summary page.
>
> The following code is what I have cobbled together from various posts (Kudos
> to those who may recognise bits of their code! I know some of it came from
> Ron db)
>
> The source ranges will be present on all of the worksheets within the
> workbook but will all have different names and the number of worksheets will
> differ everytime this macro is run. The code seems to fall down as it only
> copy & pastes from the first worksheet and then seems to ignore the rest!
>
> The source worksheets are in a password protected workbook (that bit works
> fine!)
>
> Any help greatly appreciated.
>
> Sub CopyDetails()
>
> Dim wbk As Workbook
> Dim sh As Worksheet
> Dim Sourcewb As Workbook
>
> Set wbk = Workbooks.Open(Filename:="filelocation", UpdateLinks:=True,
> Password:="password")
> Set Sourcewb = ThisWorkbook
>
> For Each sh In Sourcewb.Worksheets
> Range("A71:I71").Copy Destination:=Workbooks("Report").Worksheets("TM
> Overview").Cells(Rows.Count, 1).End(xlUp)(2)
> Next sh
>
> End Sub

--

Dave Peterson

Thank you sir,

2 little letters sorted it!

"Dave Peterson" wrote:

> Make sure you pick up the values in the correct sheet:
>
> Sh.Range("A71:I71").Copy _
> Destination:=Workbooks("Report").Worksheets("TM Overview") _
> .Cells(Rows.Count, 1).End(xlUp)(2)
>
> Those unqualified ranges will point at the active sheet.
>
>
>
> BaggieDan wrote:
> >
> > Hi All,
> >
> > I am sure this is not as difficult as my man flu addled brain is making it.
> >
> > I have a number of worksheets in a workbook that have the relevant
> > information I want to copy in the same range (A71:I71). I then have a
> > seperate workbook that I want to paste the values onto the next blank row, so
> > I can make a summary page.
> >
> > The following code is what I have cobbled together from various posts (Kudos
> > to those who may recognise bits of their code! I know some of it came from
> > Ron db)
> >
> > The source ranges will be present on all of the worksheets within the
> > workbook but will all have different names and the number of worksheets will
> > differ everytime this macro is run. The code seems to fall down as it only
> > copy & pastes from the first worksheet and then seems to ignore the rest!
> >
> > The source worksheets are in a password protected workbook (that bit works
> > fine!)
> >
> > Any help greatly appreciated.
> >
> > Sub CopyDetails()
> >
> > Dim wbk As Workbook
> > Dim sh As Worksheet
> > Dim Sourcewb As Workbook
> >
> > Set wbk = Workbooks.Open(Filename:="filelocation", UpdateLinks:=True,
> > Password:="password")
> > Set Sourcewb = ThisWorkbook
> >
> > For Each sh In Sourcewb.Worksheets
> > Range("A71:I71").Copy Destination:=Workbooks("Report").Worksheets("TM
> > Overview").Cells(Rows.Count, 1).End(xlUp)(2)
> > Next sh
> >
> > End Sub
>
> --
>
> Dave Peterson
> .
>