Copy a worksheet from one workbook to another workbook based on conditional statements

[Pookiemeister]

Sub CopyWorkbook()

    Dim currentSheet as Worksheet
    Dim sheetIndex as Integer
    sheetIndex = 1

    For Each currentSheet in Worksheets

        Windows("SOURCE WORKBOOK").Activate 
        currentSheet.Select
        currentSheet.Copy Before:=Workbooks("TARGET WORKBOOK").Sheets(sheetIndex) 

        sheetIndex = sheetIndex + 1

    Next currentSheet

End Sub

I am needing to copy the data from one page of a workbook to another. I found this code on another forum but I can't get it to work with my code. The workbook that the data will be coming from is named "Maintenance Orders and Operations.xlsx". I'm not sure if I needed to add the .xlsx and it will be copied to workbook "WO Report Template 4.1.xlsm". The worksheet it will be copied to will be determined by the week number of the month. Example: We are currently in the first week in this month. So the worksheet is named "Past Due Week 1". Also, if the source workbook is named "Maintenance Orders" that should follow under the first conditional part. Thank you.

Sub week_num()
    
      Dim WS_Count As Integer
      Dim I As Integer
      Dim WeekNum As Integer
      
      
      WS_Count = ActiveWorkbook.Worksheets.Count
      WeekNum = Application.WorksheetFunction.RoundUp(Day(Now()) / 7, 0)
      
            
      For I = 1 To WS_Count
      
         If ActiveWorkbook.Worksheets(I).Name = "WO Week " & (WeekNum) Then

         ElseIf ActiveWorkbook.Worksheets(I).Name = "Past Due Week " & (WeekNum) Then

         End If
         
      Next I
    
End Sub

 

[HighAndWilder]

Give this a go on a copy of your data.

It copies the sheet to the beginning of the target workbook.

Let you know if you want it at the end.

Public Sub CopyWorkbook()

On Error GoTo Err_Handler

  With Workbooks("WO Report Template 4.1.xlsm")

    Application.DisplayAlerts = False
    On Error Resume Next
    .Sheets("Past Due Week " & WorksheetFunction.WeekNum(Date)).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    On Error GoTo Err_Handler
    
    Workbooks("Maintenance Orders and Operations.xlsm").Sheets("TestWorksheet").Copy _
      Before:=.Sheets(1)
    
    .Sheets(1).Name = "Past Due Week " & WorksheetFunction.WeekNum(Date)
  
  End With
  
  Workbooks("Maintenance Orders and Operations.xlsm").Activate
    
Exit_Handler:

  Exit Sub

Err_Handler:
  
  MsgBox "There has been an error." & vbCrLf & vbCrLf & _
    "Error No: " & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description, vbOKOnly, "Warning"
  
  Resume Exit_Handler
  
End Sub

 

[Pookiemeister]

I tried this method I saw online but it came back with Runtime error '1004': Application-defined or object-defined error. On the following line of code.

Workbooks("Maintenance Orders and Operations.xlsx").Worksheets(1).Range("A1:X").Copy Workbooks("WO Report Template 4.1.xlsm").Worksheets("Past Due Week " & WeekNum).Range("A1")

Here is all of it so far.

Sub Week_Num_2()
    
    WeekNum = Application.WorksheetFunction.RoundUp(Day(Now()) / 7, 0)

    Workbooks("Maintenance Orders and Operations.xlsx").Worksheets(1).Range("A1:X").Copy Workbooks("WO Report Template 4.1.xlsm").Worksheets("Past Due Week " & WeekNum).Range("A1")
        
End Sub

 

[HighAndWilder]

I tried this method I saw online but it came back with Runtime error '1004': Application-defined or object-defined error. On the following line of code.

Workbooks("Maintenance Orders and Operations.xlsx").Worksheets(1).Range("A1:X").Copy Workbooks("WO Report Template 4.1.xlsm").Worksheets("Past Due Week " & WeekNum).Range("A1")

Here is all of it so far.

Sub Week_Num_2()
   
    WeekNum = Application.WorksheetFunction.RoundUp(Day(Now()) / 7, 0)

    Workbooks("Maintenance Orders and Operations.xlsx").Worksheets(1).Range("A1:X").Copy Workbooks("WO Report Template 4.1.xlsm").Worksheets("Past Due Week " & WeekNum).Range("A1")
       
End Sub

Does this line ALWAYS give you the correct week number?
WeekNum = Application.WorksheetFunction.RoundUp(Day(Now()) / 7, 0)

Best to use the specific function WEEKNUM to get the week number?

Will the data ALWAYS come from the first sheet?

What range does Range("A1:X") give you?

Do you want all of the data from the first sheet?

Does the target worksheet ALWAYS exist?

 

[Pookiemeister]

Does this line ALWAYS give you the correct week number?
WeekNum = Application.WorksheetFunction.RoundUp(Day(Now()) / 7, 0

Yes, for what I need it for.

Will the data ALWAYS come from the first sheet?

From Workbooks("Maintenance Orders and Operations.xlsx"). This workbook only contains one sheet. The actually sheet name is "SAPUI5 Export".

What range does Range("A1:X") give you?

I don't understand this question but the information needed will start from Range("A1:X#"). This data will change daily so the end row will change, that is why there is no number next to the letter "X" in the range. As the weeks increase, the end range will increase. Example below.
WeekNum=1 the range maybe Range("A1:X88").
WeekNum=5 the range maybe Range("A1:X175")

Do you want all of the data from the first sheet?

Yes

Does the target worksheet ALWAYS exist?

Yes

 

[HighAndWilder]

Yes, for what I need it for.

From Workbooks("Maintenance Orders and Operations.xlsx"). This workbook only contains one sheet. The actually sheet name is "SAPUI5 Export".

I don't understand this question but the information needed will start from Range("A1:X#"). This data will change daily so the end row will change, that is why there is no number next to the letter "X" in the range. As the weeks increase, the end range will increase. Example below.
WeekNum=1 the range maybe Range("A1:X88").
WeekNum=5 the range maybe Range("A1:X175")

Yes

Yes

If that is all the case then which part of this code failed?

        Sub Week_Num_2()
   
WeekNum = Application.WorksheetFunction.RoundUp(Day(Now()) / 7, 0)

Workbooks("Maintenance Orders and Operations.xlsx").Worksheets(1).Range("A1:X").Copy Workbooks("WO Report Template 4.1.xlsm").Worksheets("Past Due Week " & WeekNum).Range("A1")

End Sub