An Excel question via email
I have one workbook, workbook1 where i have saved all the names of worksheets saved in my workbook. These are in range of B2:B20
Now I want to run a loop which will select the name from the range and copy all data from that worksheet to another workbook, workbook2 with same worksheet name.
Note that I took some liberties with the file names. And I only did five sheets because I didn’t want to create 20 sheets.
Dim rCell As Range
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim wsDest As Worksheet
Set wbDest = Workbooks(“CopyDest.xls”)
For Each rCell In ThisWorkbook.Worksheets(“Main”).Range(“B2:B6”).Cells
On Error Resume Next
Set wsSource = ThisWorkbook.Worksheets(rCell.Value)
Set wsDest = wbDest.Worksheets(rCell.Value)
On Error GoTo 0
If Not wsSource Is Nothing And Not wsDest Is Nothing Then
wsSource.UsedRange.Copy wsDest.Range(wsSource.UsedRange.Address)
End If
Next rCell
End Sub
And now the excruciatingly detailed explanation:
….
End Sub
Sub is the keyword that tells VBA you are writing a subprocedure. If you were writing a function, you would use the Function keyword. Functions are used to return a value and subprocedures are used for everything else. The name of the subprocedure comes after the Sub keyword. The name must start with a letter and can’t contain any spaces. You can see that from the above that I prefer to capitalize the first letter of each word for easier reading. If I had arguments, they would be inside the parentheses. But I don’t, so the parentheses are empty.
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim wsDest As Worksheet
Dim comes from dimension. I’m telling VBA to save some memory for me because I’m going to be storing stuff later. I’ve created four variables; one Range variable, one Workbook variable, and two Worksheet variables. These variables will be give me an easy way to refer to certain objects later. I do all my Dimming at the top of my procedure. I also use a lower-case, one or two letter prefix. It reminds me what kind of variable it is and helps me keep my variables unique. Variable names follow the same rules as Subprocedure names.
Here I’m setting my first variable. I have a workbook open named CopyDest.xls. I’m using the Workbooks property to point to that workbook and I’m storing it in wbDest so I can more easily refer to it later. I need the Set keyword because wbDest is an object variable. All of my variables are object variables. Examples of non-object variables are Long, Double, and String. You don’t need Set if you’re assigning a value to one of these kinds of variables.
….
Next rCell
This is called a For Each loop. It uses an object variable, rCell in this case, to loop through a bunch of objects. Every time this loop is executed, rCell becomes a different cell. To identify the range I want to loop through, I start with ThisWorkbook. ThisWorkbook points to the workbook where the code lives. I could have used ActiveWorkbook to point to the workbook that’s currently visible or I could have used Workbooks(“CopySource.xls”) if I wanted to identify the workbook by name.
The little phrase that starts with ThisWorkbook is a common way to refer to objects in Excel VBA. It’s a string of objects separated by dots. It says: Start with the Workbook that contains this code. Then go to all the Worksheets in that workbook and find the one named “Main”. On the Main Worksheet, point to the range B2:B6 and return all the Cells that are in that range (as opposed to Rows or Columns or Areas). Now I loop through all the cells in that range and rCell takes on the identity of each cell one at a time.
Set wsSource = ThisWorkbook.Worksheets(rCell.Value)
Set wsDest = wbDest.Worksheets(rCell.Value)
On Error GoTo 0
On Error Resume Next means that if VBA encounters a problem, ignore it and keep going. On Error Goto 0 says that if VBA encounters an error, stop and ask me what to do. If ThisWorkbook doesn’t have a Worksheet with the name that’s in rCell, it will produce an error. I want to ignore that for now and I’ll check later if it worked or not.
Again I’m setting some object variables so that I can refer to those objects later. Notice how I’m using wbDest, an object variable I set earlier in the procedure. I didn’t have to use a variable. I could have written Set wsDest = Workbooks("CopyDest.xls").Worksheets(rCell.Value). I use object variables because it’s easier to read. It’s also easier to change if I refer to that object in a bunch of different places.
…..
End If
This is called an If block. If the condition is True, everything between If and End If is executed. If the condition is false, the procedure skips to the line just below End If and ignores everything in the block. I could also have ElseIf and Else in the block if I needed them. I don’t in this case.
If an object variable hasn’t been Set, it has a special value: Nothing. If either of these are Nothing, then don’t try to work with them. It’s a little cumbersome in VBA because we generally only care if an object variable is something so we have to test for Not Nothing. Earlier when I had the On Error statements, I told the procedure to ignore it if the sheets didn’t exist. Here is where I’m checking that the sheets actually exist before I try to do anything with them.
Finally, some code that actually does something. wsSource is object variable I set earlier. I access the UsedRange property which returns all the cells that are being used in that sheet and I Copy them (just like Edit – Copy in the UI). The argument to the Copy method is the Destination, or where I want to Copy to. In this case, I want to copy to a range on wsDest, another object variable I set up earlier. Where on wsDest? I want to copy to the same place it was on wsSource. So I use the Range property and I pass it a string that identifies the range. To get that string, I access the Address property of UsedRange. Address returns a string that looks like “A1:D15? but for the actual range I’m pointing to.
I told you it would be excruciating.
Should you reset wsSource and wsDest to Nothing after the copy to ensure the ‘Is Nothing’ tests in the following iteration are valid?
Kudos to you for taking the time and space to clearly explain in good detail all the parts. So often someone provides a solution but I have little understanding of what instructions are doing what, and why. It is especially annoying when people use abbreviations without identifying what they stand for. We are not all VBA masters and so we need detailed explanations like this to help us learn.
Please keep up this kind of great work.
Similar to Rick’s point, you might want to clean the destination sheet with the statement wsDest.UsedRange.ClearContents, before pasting in case the new used range is smaller.
I believe the mailer may actually have wanted to create a new set of individual workbooks each containing a copy of one of the worksheets from the original workbook…
Dim cell As Range
Dim Rng As Range
Dim wks_source As Worksheet
Dim wks_destination As Worksheet
Dim wkb_destination As Workbook
For Each cell In Selection
‘The source Workbook is called Summary
Set wks_source = Workbooks(“Summary”).Sheets(cell.Value)
Set wkb_destination = Workbooks.Add
Set wks_destination = wkb_destination.Sheets(1)
wks_source.Copy wks_destination
Next cell
End Sub
Just as an aside, this For..Next loop should do the same thing your For..Next loop does, but without using the On Error trap…
Set wsSource = ThisWorkbook.Worksheets(rCell.Value)
If Not wsSource Is Nothing Then
Set wsDest = wbDest.Worksheets(rCell.Value)
If Not wsDest Is Nothing Then
wsSource.UsedRange.Copy wsDest.Range(wsSource.UsedRange.Address)
End If
End If
Next rCell
@Dick, In your explanation of the On Error messages, you wrote this… “On Error Goto 0 says that if VBA encounters an error, stop and ask me what to do”. You wrote that sentence just a little bit too quickly when you composed your explanations as that is not what that statement does; rather, to quote the Help Files for the On Error Statement, it “Disables any enabled error handler in the current procedure.”
If I interpret the original question correctly the next code may be sufficient:
On Error Resume Next
For Each cl In ThisWorkbook.Sheets(“Main”).Range(“B2:B6”).SpecialCells(2, 2)
ThisWorkbook.Sheets(cl.Value).Copy
Next
End Sub
A sheet name must be a Textstring
So we only look at cells in the range B2:B6 that contain strings: SpecialCells(2, 2); that reduces the risks of non-existent sheetnames.
In the unlikely event however that a worksheet does not exist the ‘on error’ instruction prevents the copy-method from executing and continues the code on the next line.
By copying a sheet without destination a new workbook will be produced with only 1 sheet with the same name as the copied one, containing all content of that sheet. (using ‘usedrange’ is unneccessary)
There’s no need for ‘on error goto 0’, because the scope of the ‘on error’ instruction is limited to this procedure.
I know that was painful to write but it is GREATLY appreciated. It is difficult to learn when steps are skipped. I will be recommending your work to friends.