Tremendous thank you to MoshiM! This person was very patient and extremely helpful. Though I am very new to macros and still quite lost, I learned a lot in this process. Final code that made this happen:
"Transfer Well Test" within current month. Still needs work to make it execute more efficiently, but does it's job.
[TABLE="width: 500"]
<tbody>[TR]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Transfer_Well_Test()
'
' Transfer_Well_Test Macro
'[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'
Range("A47:N53").Select
Selection.Copy
Sheets("3").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("4").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("5").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("6").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("7").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("8").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("9").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("10").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("11").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("12").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("13").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("14").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("15").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("16").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("17").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("18").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("19").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("20").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("21").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("22").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("23").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("24").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("25").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("26").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("27").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("28").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("29").Select
Range("A47:N53").Select
ActiveSheet.Paste
Sheets("30").Select
Range("A47:N53").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("1").Select
ActiveWindow.SmallScroll Down:=27
Range("A47:N53").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("2").Select
Application.CutCopyMode = False
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub CopyModules(wbSource As Workbook, wbTarget As Workbook)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Dim vbcompSource As VBComponent, vbcompTarget As VBComponent[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Dim sText As String, nType As Long[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
For Each vbcompSource In wbSource.VBProject.VBComponents
nType = vbcompSource.Type
If nType < 100 Then '100=vbext_ct_Document -- the only module type we would not want to copy
Set vbcompTarget = wbTarget.VBProject.VBComponents.Add(nType)
sText = vbcompSource.CodeModule.Lines(1, vbcompSource.CodeModule.CountOfLines)
vbcompTarget.CodeModule.AddFromString (sText)
vbcompTarget.Name = vbcompSource.Name
End If
Next vbcompSource[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End Sub
Sub Update_SITP()
'
' Update_SITP Macro
'[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'
Range("L39:N44").Select
Selection.Copy
Sheets("3").Select
Range("L39:N40").Select
ActiveSheet.Paste
Sheets("4").Select
Range("L39:N44").Select
ActiveSheet.Paste
Sheets("5").Select
ActiveSheet.Paste
Sheets("6").Select
ActiveSheet.Paste
Sheets("7").Select
ActiveSheet.Paste
Sheets("8").Select
ActiveSheet.Paste
Sheets("9").Select
ActiveSheet.Paste
Sheets("10").Select
ActiveSheet.Paste
Sheets("11").Select
ActiveSheet.Paste
Sheets("12").Select
ActiveSheet.Paste
Sheets("13").Select
ActiveSheet.Paste
Sheets("14").Select
ActiveSheet.Paste
Sheets("15").Select
ActiveSheet.Paste
Sheets("16").Select
ActiveSheet.Paste
Sheets("17").Select
ActiveSheet.Paste
Sheets("18").Select
ActiveSheet.Paste
Sheets("19").Select
ActiveSheet.Paste
Sheets("20").Select
ActiveSheet.Paste
Sheets("21").Select
ActiveSheet.Paste
Sheets("22").Select
ActiveSheet.Paste
Sheets("23").Select
ActiveSheet.Paste
Sheets("24").Select
ActiveSheet.Paste
Sheets("25").Select
ActiveSheet.Paste
Sheets("26").Select
ActiveSheet.Paste
Sheets("27").Select
ActiveSheet.Paste
Sheets("28").Select
ActiveSheet.Paste
Sheets("29").Select
ActiveSheet.Paste
Sheets("30").Select
ActiveSheet.Paste
Range("L39:N44").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=9
Range("P48:R53").Select
Application.CutCopyMode = False
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("L39:N44").Select
Selection.Copy
Sheets("1").Select
Range("L39:N44").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
End Sub
[/FONT]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
Prepare Next Month's Daily Production Report, move over latest Well Test Data, move over Macros, and Update Shut-in Tubing Pressure
[TABLE="width: 500"]
<tbody>[TR]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Month_Stuff()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Dim Target_Month_Days, Target_Month_Day_1 As Date, X As Long, WS1 As Worksheet, New_WB As Workbook, _
FileN As String, ERR_WS As Worksheet, RRR As Range
Transfer_Well_Test RRR[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Target_Month_Day_1 = DateSerial(Year(Date), Month(Date) + 1, 1) 'first day of next month[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
'Target_Month_Day_1 = DateSerial(2019, 12, 1) 'first day of Target Month if needed[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Target_Month_Days = DateSerial(Year(Target_Month_Day_1), Month(Target_Month_Day_1) + 1, 1) - Target_Month_Day_1
'first day of (Target_Month_Day_1 + 1 MONTH) - Target_Month_Day_1 = number of days in Tareget Month
Set New_WB = Workbooks.Add 'create new workbook and store object in a variable[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Set WS1 = New_WB.Worksheets(1) 'first worksheet of the newly created workbook[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Application.EnableEvents = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
On Error Resume Next[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
For X = 1 To Target_Month_Days 'loop 1 to the number of days found in the target month[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Set ERR_WS = ThisWorkbook.Worksheets(CStr(X)) 'this will generate an error if it doesn't exist
If Err.Number = 0 Then 'if the worksheet exists then copy it to the new Workbook
If X = 1 Then
ERR_WS.Copy After:=WS1
Else
ERR_WS.Copy Before:=WS1
End If
Else 'If it doesn't exit then create it within the new workbook and rename it
With New_WB.Worksheets.Add
.Move Before:=WS1
.Name = CStr(X)
.Range(RRR.Address) = RRR
End With
Err.Clear 'clear the generated error
End If[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Next X[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With New_WB[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
ThisWorkbook.Worksheets("Monthly Report").Copy Before:=.Worksheets(1) 'Copy Monthly Report to new Worksheet
.Worksheets("2").Range("C4:D4") = DateSerial(Year(Target_Month_Day_1), Month(Target_Month_Day_1), 2) 'second day of the next month[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
.Worksheets("Monthly Report").Range("B10") = DateSerial(Year(Target_Month_Day_1), Month(Target_Month_Day_1), 1) 'First Day of upcoming Month
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With Application[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
.EnableEvents = True
.DisplayAlerts = False
WS1.Delete
.DisplayAlerts = True
End With
CopyModules ThisWorkbook, New_WB[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]FileN = ThisWorkbook.Path & "" & "Aspen DPR " & Month(Target_Month_Day_1) & "-" & Mid(Year(Target_Month_Day_1), 3, 2) & ".xlsb"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
New_WB.SaveAs Filename:=FileN, FileFormat:=xlExcel12[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Transfer_Well_Test(ByRef RR As Range)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
'SAVE BEFORE USING THIS TO MAKE SURE IT DOES WHAT YOU WANT PROPERLY[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Dim WS As Worksheet[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Set RR = ActiveWorkbook.ActiveSheet.Range("A47:N53") 'store this range in a variable[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
For Each WS In ActiveWorkbook.Worksheets
With WS
If Not WS Is ActiveWorkbook.ActiveSheet And IsNumeric(.Name) Then
If CLng(.Name) <= 31 Then .Range("A47:N53") = RR
End If
End With
Next[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub CopyModules(wbSource As Workbook, wbTarget As Workbook)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Dim vbcompSource As VBComponent, vbcompTarget As VBComponent
Dim sText As String, nType As Long
For Each vbcompSource In wbSource.VBProject.VBComponents
nType = vbcompSource.Type
If nType < 100 Then '100=vbext_ct_Document -- the only module type we would not want to copy
Set vbcompTarget = wbTarget.VBProject.VBComponents.Add(nType)
sText = vbcompSource.CodeModule.Lines(1, vbcompSource.CodeModule.CountOfLines)
vbcompTarget.CodeModule.AddFromString (sText)
vbcompTarget.Name = vbcompSource.Name
End If
Next vbcompSource
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>