Excel VBA: Workbook one - I would like to cut the range of B9..E11 in "sheet1" and paste to a9..d11 in "sheet1". I need to perform this simple action on multiple workbook files(that are all located in the same folder) that are all setup the the same way.
Thanks so much!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Corrected code:
Public Sub CopyCells()
Dim File As String
File = Dir("C:\full\path\to\folde
Do While Len(File) > 0
Workbooks.Open File, False
ActiveWorkbook.Sheets("She
ActiveWorkbook.Sheets("She
ActiveWorkbook.Sheets("She
ActiveWorkbook.Close True
File = Dir
Loop
End Sub
Kevin
You could try using this macro. It will pop up a dialog to select the folder with the excel files.
Sub ProcessFolder()
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim fso As Object, fldr As Object, fil As Object
Dim sa As Object, shFolder As Object
Dim foldername As String
Set sa = CreateObject("Shell.Applic
Set shFolder = sa.BrowseForFolder(0, "Select Folder Containing Excel Files", 0, "")
If shFolder Is Nothing Then Exit Sub
Set xlApp = New Excel.Application
Set shFolderItem = shFolder.Items.Item
foldername = shFolderItem.Path
Set fso = CreateObject("Scripting.Fi
Set fldr = fso.GetFolder(foldername)
For Each fil In fldr.Files
Set xlWb = xlApp.Workbooks.Open(folde
Set xlWs = xlWb.Sheets("Sheet1")
xlWs.Range("B9:E11").Cut Destination:=xlWs.Range("A
xlWb.Close SaveChanges:=True
Next
Set fil = Nothing
Set fldr = Nothing
Set fso = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
MsgBox "Done"
End Sub
Most of the time you can get 90% of the way there by recording a macro of what you want to do, then looking at the code.
Here's the result of a macro recording of opening a worksheet, copying / pasting the selected area and closing the sheet. Replace the filename and repeat the code for each filename and you're done.
Sub CopyRangeInWorkbook()
'
' CopyRangeInWorkbook Macro
' Macro recorded 13/06/2007 by Tony Bowes
'
' ToDo: replace filename with real workbook
' ToDo: copy / paste code for however many files I have, changing the filename for each one.
Workbooks.Open Filename:="\\bream\Tony-BO
Range("B9:E11").Select
Selection.Copy
Range("A9").Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Business Accounts
Answer for Membership
by: zorvekPosted on 2007-06-12 at 17:42:02ID: 19271066
Try this macro:
Public Sub CopyCells()
Dim File As String
File = Dir("C:\full\path\to\folde
Do While Len(File) > 0
Workbooks.Open File, False
ActiveWorkbook.Sheets("She
ActiveWorkbook.Sheets("She
ActiveWorkbook.Close True
Loop
End Sub
Kevin