Help on best method to copy paste from one wb to another wb

[kit99]

The code below is run from ThisWorkbook. It opens another 4 wb's (Gr1.xlsm, Report1.xls, Report2.xls and Report3.xls), and copies data from wb Report 1-3 into 3 different sheets in wb Gr1.

Vba works, but I'm looking for a better way of writing it. One issue is to avoid as much use of .activate, .select and .selection as possible. Another is to shorten the code if possible. Is it better to use definition on every wb and sh at the top of the code? If so, how to write this?
When copying, I need to use cells.select or something similar 'cause I'm overwriting what ever information that already exists in the sheets I'm pasting data to.

My vba skills is clearly not what it ought to be... Anyone that can help me on how to change this code?

fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath
 
Set wb = Workbooks.Open(ThisWorkbook.Path & "\Gr1.xlsm")
    Sheets(«Re1").Visible = True
    Sheets(«Re2").Visible = True
    Sheets(«Re3").Visible = True
                     
'New reports from wb Report1-3 in to wb Gr1, sh Re1-3
            Set wb = Workbooks.Open(fPath & "\R2\" & "Report1" & ".xls")
            wb.Sheets(1).Select
            Cells.Select
            Selection.Copy
            wb.Close False
            On Error GoTo 0
        Windows("Gr1.xlsm").Activate
        Sheets(«Re1").Select
        Cells.Select
        ActiveSheet.Paste
        On Error GoTo 0
       
            Set wb = Workbooks.Open(fPath & "\R2\" & "Report2" & ".xls")
            wb.Sheets(1).Select
            Cells.Select
            Selection.Copy
            wb.Close False
            On Error GoTo 0
        Windows("Gr1.xlsm").Activate
        Sheets(«Re2").Select
        Cells.Select
        ActiveSheet.Paste
        On Error GoTo 0
       
            Set wb = Workbooks.Open(fPath & "\R2\" & "Report3" & ".xls")
            wb.Sheets(1).Select
            Cells.Select
            Selection.Copy
            wb.Close False
            On Error GoTo 0
        Windows("Gr1.xlsm").Activate
        Sheets(«Re3").Select
        Cells.Select
        ActiveSheet.Paste
        On Error GoTo 0

 

[rlv01]

You might have to play around with this since I did not have your workbook to test it, but here's one way

Option Explicit   ' put this at the top of your code module to force explicit declaration of all variables used. 

Sub kit99()
    Dim fPath As String
    Dim WB As Workbook, GRWB As Workbook
    Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet

    fPath = ThisWorkbook.Path
    If Right(fPath, 1) = "\" Then
        fPath = Left(fPath, Len(fPath) - 1)
    End If

    Set GRWB = Workbooks.Open(ThisWorkbook.Path & "\Gr1.xlsm")
    With GRWB
        Set WS1 = .Worksheets("Re1")
        Set WS2 = .Worksheets("Re2")
        Set WS3 = .Worksheets("Re3")
    End With

    'New reports from wb Report1-3 in to wb Gr1, sh Re1-3
    Set WB = Workbooks.Open(fPath & "\R2\" & "Report1" & ".xls")
    WS1.Cells.ClearContents  ' comment out this line if you don't want to clear the existing data first
    WB.Sheets(1).Cells.Copy WS1.Range("A1")
    WB.Close False

    Set WB = Workbooks.Open(fPath & "\R2\" & "Report2" & ".xls")
    WS2.Cells.ClearContents  ' comment out this line if you don't want to clear the existing data first
    WB.Sheets(1).Cells.Copy WS2.Range("A1")
    WB.Close False

    Set WB = Workbooks.Open(fPath & "\R2\" & "Report3" & ".xls")
    WS3.Cells.ClearContents  ' comment out this line if you don't want to clear the existing data first
    WB.Sheets(1).Cells.Copy WS3.Range("A1")
    WB.Close False
End Sub

 

[kit99]

You might have to play around with this since I did not have your workbook to test it, but here's one way

Option Explicit   ' put this at the top of your code module to force explicit declaration of all variables used. 

Sub kit99()
    Dim fPath As String
    Dim WB As Workbook, GRWB As Workbook
    Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet

    fPath = ThisWorkbook.Path
    If Right(fPath, 1) = "\" Then
        fPath = Left(fPath, Len(fPath) - 1)
    End If

    Set GRWB = Workbooks.Open(ThisWorkbook.Path & "\Gr1.xlsm")
    With GRWB
        Set WS1 = .Worksheets("Re1")
        Set WS2 = .Worksheets("Re2")
        Set WS3 = .Worksheets("Re3")
    End With

    'New reports from wb Report1-3 in to wb Gr1, sh Re1-3
    Set WB = Workbooks.Open(fPath & "\R2\" & "Report1" & ".xls")
    WS1.Cells.ClearContents  ' comment out this line if you don't want to clear the existing data first
    WB.Sheets(1).Cells.Copy WS1.Range("A1")
    WB.Close False

    Set WB = Workbooks.Open(fPath & "\R2\" & "Report2" & ".xls")
    WS2.Cells.ClearContents  ' comment out this line if you don't want to clear the existing data first
    WB.Sheets(1).Cells.Copy WS2.Range("A1")
    WB.Close False

    Set WB = Workbooks.Open(fPath & "\R2\" & "Report3" & ".xls")
    WS3.Cells.ClearContents  ' comment out this line if you don't want to clear the existing data first
    WB.Sheets(1).Cells.Copy WS3.Range("A1")
    WB.Close False
End Sub

I've just tested your code, and it works fantastic! Thanks a lot for helping out.
But I'm a bit puzzled as well...
I'm quite certain I once learned that I had to open up hidden sheets before I could write to them, but you're not doing so...?
Or is it something going on here that I can't see (wouldn't be the first time)?

 

[rlv01]

If a sheet is hidden, but not protected you can still write to it.

If you were trying to activate it first, i.e.

WS1.activate

That would be a different story. Then you would need to unhide it first. You can do a lot of things with macros to worksheets in a multi-sheet workbook without bringing each sheet front and center to look at first.

 

[kit99]

If a sheet is hidden, but not protected you can still write to it.

If you were trying to activate it first, i.e.

WS1.activate

That would be a different story. Then you would need to unhide it first. You can do a lot of things with macros to worksheets in a multi-sheet workbook without bringing each sheet front and center to look at first.

Holy c...
I've got a lot of wb's with 25-30 sheets that I'm unhiding/hiding right now to write to them. There's a lot of lines of code I now can get rid of!
Thanks again!