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.

 

[jindon]

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.

That means the string in the brackets, microscope1.xls, is wrong.

 

[seagreen]

Thanks Jindon. I guess I needed to have both workbooks open. I had "Microsocope1.xls" closed. It worked after I opened it. Thanks!!

 

[jindon]

If you want to copy only the values without the format then you don't need to open this case.

Sub testNotOpen()
Dim myFolder As String, fn As String, myRange As String
Dim myRows As Long, myCols As Long, myDest As String
myFolder = "c:\test\"  '　← folder path
fn = "microscope1.xls"  '　← file name
myRange = "A1:G84"    '　← range address to be extracted
myDest = "A1"            '　← address on the destination sheet
With Range(myRange)
    myRows = .Rows.Count
    myCols = .Columns.Count
End With
For Each ws In Sheets
    With ws.Range(myDest).Resize(myRows, myCols)
        .Formula = "='" & myFolder & "[" & fn & "]" & ws.Name & "'!" & Split(myRange,":")(0)
        .Value = .Value
    End With
Next
End Sub

 

[seagreen]

This is great Jindon,

I'm going to try this new code out tomorrow morning. The earlier example you gave is working perfectly!