I am trying to create a weekly timecard using a copy of data filtered from a worksheet table (40 rows 15 Columns) and pasted into the Timecard file, and then using that destination file to save as the new weekly timecard file for an individual. I'm missing something somewhere having tried so many different solutions, but I just cant get the copied data to paste, and now just at a loss on what to try next for a resolution.
Any help greatly received.
Code
Public Sub Create_Timecard()
'
' Create a Timecard from user input box containing open TaskDataBase entries
'
Dim WorkflowRTE_07 As Workbook
Dim TimecardRTE0 As Workbook
Dim Tasks As Worksheet
Dim TargetSheet As Worksheet
'
Dim WorkflowRTEPath As String
Dim TimecardRTEPath As String
'
Dim TimecardWEDate As String, TimecardEstimID As String, TimecardFilename As String
Dim TimecardTable As ListObject
Dim Lastrow As Long
'
'>>> Define workbooks paths
WorkflowRTEPath = "C:\Users\Deb\Documents\_EXCEL\WorkflowRTE\WorkflowRTE_07.xlsx"
TimecardRTEPath = "C:\Users\Deb\Documents\_EXCEL\WorkflowRTE\TimecardRTE0.xlsx"
'
'>>> Set a reference to the target Workbook and Sheets use Workbooks.Open(Filename)if closed
Set TimecardRTE0 = Workbooks.Open(TimecardRTEPath)
Workbooks("WorkflowRTE_07").Activate '>>> Macro being run from this workbook so Open not required
'
'>>> Get user input to name weekly estimators individual timesheet
TimecardWEDate = InputBox("Timecard Week-ending Date required", "Enter the timecard week ending date....", "YYYYMMDD")
TimecardEstimID = InputBox("Timecard User ID required. Enter the Esimators Timecard ID ie: Estim99", "Create Timecard for Estimator", "Key Timecard ID here....")
TimecardFilename = TimecardWEDate & "_" & TimecardEstimID
MsgBox ("Creating timesheet - " & TimecardFilename)
'
With Tasks
'>>> Remove any filters from the sheet
ActiveSheet.AutoFilterMode = False
'
'>>> Get last row of database
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox ("last row of database is " & Lastrow)
'
'>>> Specifying the complete address is the key part
With Range(Cells(1, 1), Cells(Lastrow, 15))
'
'>>> Specify .AutoFilter Field:=YOURFIELDNUMBER, Criteria1:=YOURCRITERIA
ActiveSheet.ListObjects("TaskDataBase").Range.AutoFilter Field:=5, Criteria1:=TimecardEstimID
ActiveSheet.ListObjects("TaskDataBase").Range.AutoFilter Field:=12, Criteria1:="=In Progress", Operator:=xlOr, Criteria2:="=Not Started"
'
'>>> Now copy and paste each section (column) of the data into the timecardmaster file.
'Range(Cells(2, 1), Cells(Lastrow, 1)).SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks("TimecardRTE0").Worksheets("Timesheet").Range("C2")
'Range(Cells(2, 2), Cells(Lastrow, 2)).SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks("TimecardRTE0").Worksheets("Timesheet").Range("D2")
'Range(Cells(2, 5), Cells(Lastrow, 5)).SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks("TimecardRTE0").Worksheets("Timesheet").Range("B2")
End With
End With
End Sub
' Then need to add code to save the new timecard
Display More