Excel VBA - Copy & Paste range between workbooks with changing names

[Lars Ole Sørensen]

Hi,

I have a report that i want to update every week with VBA code. The source workbook name and the destination workbook name will change every week, however the start of the workbooks names will be the same.

I am trying to copy a range from a source workbook (2017 HAVFISK UKE 12) and paste the range to a destination workbook (DB pr Uke_12_2017_Nordland H). I have tried to make a test code, but i get a error message when i try to run the code. I want to run the code when both workbooks are open in excel.

I have attached part of the source dokument and the destination document. The code is in the document "DB pr Uke_12_2017_Nordland H"

I hope someone can help me with this problem.

Kindly Regards
Lars Ole

 

[Marc L]

Hi Lars Ole !

Bad logic in your If … End If !

A starter (Edit V3) :​

Sub Demo()
         Dim Wb As Workbook, WbSource$
With ThisWorkbook.Worksheets(1)
             WbSource = Year(.[B3].Value) & " HAVFISK " & .Name & ".xlsx"
    For Each Wb In Workbooks
        If StrComp(Wb.Name, WbSource, 1) = 0 Then
            .[A6:D14].Value = Wb.Worksheets(1).[A6:D14].Value
            Exit For
        End If
    Next
End With
          If Wb Is Nothing Then Beep Else Set Wb = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Lars Ole Sørensen]

The code works fine, thank you very much Marc L

And another question. How the code will be if I want to store the code in my PERSONAL.XLSB?

Kindly Regards
Lars Ole

 

[Marc L]

It depends if always only two workbooks are opened
or if destination workbook always start by DB pr Uke_ …

Code in PERSONAL can also start from active workbook
as the destination workbook so that just needs to replace
ThisWorkbook by ActiveWorkbook in code.

With more informations, the source workbook 2017 HAVFISK UKE …
may not ever need to be opened in Excel !

An improvement could be to name source workbook
like destination workbook aka Uke instead of UKE …
​

 

[Lars Ole Sørensen]

I know that I can use ActiveWorkbook instead of ThisWorkbook, but then i have to have DB pr Uke_ active when I run the macro. It may be times when more workbooks are open and the destination workbook will always start by DB pr Uke_. So if it is possible, I think it is preferable to have a code that refer to the partial filename DB pr Uke_.

In my case I will always have the source workbook 2017 HAVFISK UKE … open. This is because I always make a visual check of the document.

Kindly Regards
Lars Ole

 

[Marc L]

Is it normal there are two spaces between DB pr and Uke_ ?
As it could be a trap but avoided like this :​

Sub Demo2()
     Dim Wb As Workbook, Ws As Workbook, WSource$
For Each Wb In Workbooks
      If Wb.Name Like "DB pr *" Then
    With Wb.Worksheets(1)
            WSource = Year(.[B3].Value) & " HAVFISK " & .Name & ".xlsx"
        For Each Ws In Workbooks
            If StrComp(Ws.Name, WSource, 1) = 0 Then
                .[A6:D14].Value = Ws.Worksheets(1).[A6:D14].Value
                Exit For
            End If
        Next
    End With
              If Ws Is Nothing Then Beep Else Set Ws = Nothing
        Exit For
      End If
Next
      If Wb Is Nothing Then Beep Else Set Wb = Nothing
End Sub

You may Like it !
​

 

[Lars Ole Sørensen]

Thanks again for the updated code. It works perfectly.

Yes, its normal with the two spaces between DB pr and Uke_. Thanks for the warning regarding this and that you have taken this into account in the code.

Kindy Regards
Lars Ole

 

[Marc L]

So you can mod the code like​

      If Wb.Name Like "DB pr  U*" Then

or if case never changes for Uke like​

      If Wb.Name Like "DB pr  Uke_*" Then

Optional, no matter if there is no other kind of DB pr workbooks …
​

 

[Lars Ole Sørensen]

Ok, good to know and thanks again for the good and thorough help and explanation.

Kindly Regards
Lars Ole