Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - small change
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.Cop y _ 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - small change
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 "Don Guillett" wrote: myBook.Worksheets(1).Range("A2").CurrentRegion.Cop y _ try myBook.Worksheets(1).Range("A2").EntireRow.Copy _ -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... 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.Cop y _ 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - small change
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... 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 "Don Guillett" wrote: myBook.Worksheets(1).Range("A2").CurrentRegion.Cop y _ try myBook.Worksheets(1).Range("A2").EntireRow.Copy _ -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... 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.Cop y _ 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - small change
I didn't realize I had the question until after I saw the result. Sorry I
took too much of your time. T "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... 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 "Don Guillett" wrote: myBook.Worksheets(1).Range("A2").CurrentRegion.Cop y _ try myBook.Worksheets(1).Range("A2").EntireRow.Copy _ -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... 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.Cop y _ 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - small change
Is it now working as desired? -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... I didn't realize I had the question until after I saw the result. Sorry I took too much of your time. T "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... 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 "Don Guillett" wrote: myBook.Worksheets(1).Range("A2").CurrentRegion.Cop y _ try myBook.Worksheets(1).Range("A2").EntireRow.Copy _ -- Don Guillett Microsoft MVP Excel SalesAid Software "Theo" wrote in message ... 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.Cop y _ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you change current small text in a cell to CAPITALS? | Excel Discussion (Misc queries) | |||
Need Help - Small Macro | Excel Programming | |||
Scroll Bar / Spin Button Small Change value | Excel Programming | |||
How to change cases from small to capital in all cells? | Setting up and Configuration of Excel | |||
How do I change the Excel 2000 sheet tab size? It's too small. | Excel Discussion (Misc queries) |