Copy sheet from one workbook to another

[Jakes]

Hi there,

I have made a drawing Issue template, and the first Beta is already released.
However, I have made a updated version, and I would like to have a easy way to update to the newer version.

For example, I have Workbook1.1 withs Sheets A, B,C and D
Now the newer version is Workbook 1.2 With Sheets A,B,C,D and E.

However, the data is actually only stored in Sheets B,C and D in both the workbooks, with no changes at all.

I would like to have a macro in Workbook 1.2 that can copy the entire sheets B,C and D from workbook 1.1 to sheets B,C and D in Workbook 1.2

Note, the workbooks will not necessarily be in the same folder or location.

Is that possible, and if so, how?

Best Regards,

Jakes

[arlu1201]

This is one way. Put this code in a blank module of workbook 1.2

Option Explicit
Dim FName As Variant

Sub copy_files()

FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the 1st workbook")
If FName = "False" Then
    MsgBox "You have not selected a file."
    Exit Sub
Else
    Workbooks.Open Filename:=FName
    FName = ActiveWorkbook.Name
End If

Workbooks(FName).Sheets(Array("B", "C", "D")).copy Before:=ThisWorkbook.Worksheets(2)

End Sub

You can then delete any unwanted sheets.

[Jakes]

Awesome, it is more or less what I was looking for. It works fine, but it does not place the content in the same sheet name, but make a new sheet , leaving the existing one blank. The Sheets MUST be the same name for the forms to work.
Can the above code be adjusted so it replace the current sheet with the new imported one, keeping the same name?

[arlu1201]

Ok that can be done. Can you provide me the exact names of your sheets (if they are not B,C & D) so that you do not have to change them at your end again?

[Jakes]

Cool stuff!

The sheet names are "Issues", "Drawings", "ProjectInfo", "Participants"

Here are the code adapted to work with my sheet names:

Option Explicit
Dim FName As Variant
Sub Rectangle17_Click()

FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the 1st workbook")
If FName = "False" Then
    MsgBox "You have not selected a file."
    Exit Sub
Else
    Workbooks.Open Filename:=FName
    FName = ActiveWorkbook.NAME
End If

Workbooks(FName).Sheets(Array("Issues", "Drawings", "ProjectInfo", "Participants")).Copy Before:=ThisWorkbook.Worksheets(2)

End Sub

[arlu1201]

What do your vlookup formulae show? Some error as the result?

If the above method does not work, we can have a code that will replace the contents of the sheets in 1.2 instead of copying over the sheets.

[Jakes]

What do your vlookup formulae show? Some error as the result?

If the above method does not work, we can have a code that will replace the contents of the sheets in 1.2 instead of copying over the sheets.

everywhere the sheet names should be, I get a #REF! for example =VLOOKUP(P16,#REF!,2,FALSE)

[Jakes]

Ok I have implemented the code below, and it work sort off.

First I use this code to delete the current sheets:

' deletes sheets in the active workbook
    Application.DisplayAlerts = False
    Sheets(Array("Issues", "Participants", "ProjectInfo", "Drawings")).Delete
    Application.DisplayAlerts = True

Then I use this code to import the sheets from a previous version:

 ' Copy new sheets
    FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the 1st workbook")
If FName = "False" Then
    MsgBox "You have not selected a file."
    Exit Sub
Else
    Workbooks.Open Filename:=FName
    FName = ActiveWorkbook.NAME
End If

Workbooks(FName).Sheets(Array("Issues", "Drawings", "ProjectInfo", "Participants")).Copy Before:=ThisWorkbook.Worksheets(2)
End Sub

My problem is however, non of my VLookup formulas work after importing, it does not recognise the new imported sheets, although it have the same name. Is there somewhere something I need to update or refresh?
Cheers,

Jakes

[arlu1201]

So all 4 sheets - Issues, Drawings, ProjectInfo & Participants should be copied over from 1.1 to 1.2?

[Jakes]

Yes
Attached are the workbook for reference. Use the Import data button on the Help sheet

Issue slip Template v1.3.xlsm

[Jakes]

Yes I guess the best is to copy the contents instead of the whole sheet. My Excel VBA is just so limited, I do not know how

[arlu1201]

This is the updated code that you can use -

 
Sub Rectangle17_Click()
Dim FName As Variant
Dim i As Long
Dim lrow As Long
Dim sname As String

FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the 1st workbook")
If FName = "False" Then
    MsgBox "You have not selected a file."
    Exit Sub
Else
    Workbooks.Open Filename:=FName
    FName = ActiveWorkbook.NAME
End If

For i = 1 To Workbooks(FName).Worksheets.Count
    With Workbooks(FName).Worksheets(i)
        If .NAME = "Issues" Or .NAME = "Drawings" Or .NAME = "ProjectInfo" Or .NAME = "Participants" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            sname = .NAME
            ThisWorkbook.Worksheets(sname).Cells.ClearContents
            .Rows("1:" & lrow).Copy ThisWorkbook.Worksheets(sname).Range("A1")
        End If
    End With
Next i

End Sub

Also i observed that your file has lots of modules. You can remove most of them which you do not need and even have multiple code subs in the same module. It will help to keep your file better organized.