macro - small change

T

Theo

This macor works great - but I only want to copy Row 2. ****HERE****
Can someone help?
Thanks
T

Sub Master()

Dim myBook As Workbook

Dim myCalc As XlCalculation

Dim myShtName As String



With Application

.EnableEvents = False

.DisplayAlerts = False

myCalc = .Calculation

.Calculation = xlCalculationManual

End With



On Error Resume Next

With Application.FileSearch

.NewSearch

'Change this to your directory

.LookIn = "H:\WR Intake"
.SearchSubFolders = False

.FileType = msoFileTypeExcelWorkbooks

If .Execute() > 0 Then

For i = 1 To .FoundFiles.Count

Set myBook = Workbooks.Open(.FoundFiles(i))

myBook.Worksheets(1).Range("A2").CurrentRegion.Copy _
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2)
*****Don't want everything - just row 2 ***********
myBook.Close False

Next i

Else: MsgBox "There were no files found."

End If

End With

With Application

.EnableEvents = True

.DisplayAlerts = True

.Calculation = myCalc

End With



End Sub
 
D

Don Guillett

myBook.Worksheets(1).Range("A2").CurrentRegion.Copy _
try
myBook.Worksheets(1).Range("A2").EntireRow.Copy _
 
T

Theo

Yup! That worked.
The only problem that I have now is that I want it to paste the data as
paste/special values - otherwise it's copying formulas and the data does not
match the original.
I tried adding a statements .....
..PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

but I've either got it in the wrong place, or that's just not the right way
to do it.
Any suggestions?
Thanks Don!
T
 
D

Don Guillett

Always nice to FULLY state your question the FIRST time. Try to copy and
then paste

myBook.Worksheets(1).Range("A2").entirerow.Copy 'No continuation
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2) _
.PasteSpecial Paste:=xlPasteValues
 
T

Theo

I didn't realize I had the question until after I saw the result. Sorry I
took too much of your time.
T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top