Basic range copy, match sheetnames: logic, style, and syntax

[seagreen]

Hi,
I'm just beginning to learn VBA. I am wondering if using an "if" statement or a case statement would be good ways to check two worksheets in two different workbooks to see if their names match? I could use some help correcting my syntax too:

For 1 to ws.count
If Worksheet("one").Name = Worksheet("two").Name
      Worksheet("one").Range("A1:G84").Copy
      Worksheet("two").Range("A1:G84").Paste
Endif
Next ws.Count

I know this is basic stuff but I'm having a little trouble getting the hang of it.
Thanks in advance for your help.

 

[Fazza]

hi,

For two different workbooks, instead of
worksheets("one").name
use workbooks("file one name").worksheets("one").name

The "if" might be fine, depending on exactly what you are doing. There are usually many approaches. what exactly are you wanting to do?

F

 

[jindon]

My guess

For Each wsInBook1 In Workbooks("Book1.xls")
    For Each wsInBook2 In Workbooks("Book2.xls")
        If wsInBook1.Name = wsInBook.Name Then
            wsInBook1.Range("A1:G84").Copy wsInBook2.Range("a1")
            Exit For
        End If
    Next
Next

 

[Fazza]

Great stuff, thanks, jindon.

seagreen, I'd like to encourage to keep asking questions & practicing coding. Learning can be slow initially but becomes easy after a while. And it can be enjoyable and rewarding. best wishes, Fazza

 

[jindon]

should be

For Each wsInBook1 In Workbooks("Book1.xls").Sheets
    For Each wsInBook2 In Workbooks("Book2.xls").Sheets
        If wsInBook1.Name = wsInBook2.Name Then
            wsInBook1.Range("A1:G84").Copy wsInBook2.Range("a1")
            Exit For
        End If
    Next
Next

 

[seagreen]

Thanks Fazza and Jindon! I will keep working at it. I really appreciate all the help! When I tried to adapt your code Jindon, I am getting this error:
"Run time error 438: Object doesn't support this property or method"
On line:

For Each wsInBook1 In Workbooks("ALLCMsMergeJan312009.xls")

I know your code is fine, but do I need to do something else? All I defined were the two workshhets:

Dim wsInBook1 As Worksheet
Dim wsInBook2 As Worksheet

Thanks!

 

[seagreen]

Hi again,
Maybe I figured this last question out. I was trying to run the code from within one of the named workbooks. Maybe I need to have the code in a third workbook. It seems to be working if I do this. Please let me know if I'm making a false assumption.

Thanks.

 

[jindon]

Sub test()
Dim wsInBook1 As Worksheet, wsInBook2 As Worksheet
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ThisWorkbook '<- or use a name e.g. Workbooks("Book1.xls")
Set wb2 = Workbooks("book2.xls")
For Each wsInBook1 In wb1.Sheets
    For Each wsInBook2 In wb2.Sheets
        If wsInBook1.Name = wsInBook2.Name Then
            wsInBook1.Range("A1:G84").Copy wsInBook2.Range("a1")
            Exit For
        End If
    Next
Next
End Sub

 

[seagreen]

Jindon,
I can't tell you how much I appreciate your help. You make it so clear in a way that the books have not. I am getting a "subscript out of range error" at this line :

Set wb2 = Workbooks("microscope1.xls")

Do I need to have the same number and like-named sheets in both workbooks?

Thanks.

 

[seagreen]

I should clarify what I just asked: I have two workbooks: one with four sheets with the same names as four in the other worksheet but one of the workbook has many other sheets in addition to the four that match the smaller workbook.

Thanks.