Copy Range, Paste Value in New Workbook

[BaggieDan]

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]

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]

Thank you sir,

2 little letters sorted it!

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.