Hi, the below code works fine when i say
activesheet.paste
but when i replace it with
ActiveSheet.PasteSpecial xlPasteValues
nothing pastes. I have stepped through and it works until I get to the paste line.
Code
Sub Copy_Data()
'PURPOSE: To loop through type 2 report templates and add last months invoice data
Dim myPath As String
Dim mypathYTD As String
Dim mypathInv As String
Dim myFile As String
Dim MyFileYTD As String
Dim myfileinv As String
Dim sht_name_YTD As String
Dim sht_name As String
Dim sht_name_inv As String
Dim lobj_name As String
Dim myExtension As String
Dim sht As Worksheet
Dim FldrPicker As FileDialog
Dim LR, LR_YTD, LR_Inv As Long
Dim acct_lr As Long
Dim R As Range
Dim inv_month As String
Dim inv_start_cell As String
Dim inv_end_cell As String
Dim inv_LR As Long
On Error Resume Next
For Each lobj_temp In wb.Sheets("Financial Data").ListObjects
lobj_temp.DataBodyRange.Delete
Next lobj_temp
Client = Left(wb.Name, InStr(wb.Name, " ") - 1)
For Each sht_inv In wbinv.Worksheets
sht_name = sht_inv.Name
wbinv.Sheets(sht_name).Activate
inv_LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
inv_start_cell = Application.WorksheetFunction.VLookup(Client, ThisWorkbook.Worksheets("LOOKUPS").Range("J2:P100"), 3, False)
inv_end_cell = Application.WorksheetFunction.VLookup(Client, ThisWorkbook.Worksheets("LOOKUPS").Range("J2:P100"), 4, False) & CStr(inv_LR)
For Each lobj In sht_inv.ListObjects
lobj_name = lobj.Name
Range(inv_start_cell & ":" & inv_end_cell).Select
Selection.Copy
temp_rng_strt = Application.WorksheetFunction.VLookup(Client, ThisWorkbook.Worksheets("LOOKUPS").Range("J2:P100"), 5, False)
wb.Sheets("Financial Data").Activate
Range(temp_rng_strt).Select
ActiveSheet.PasteSpecial xlPasteValues
Next lobj
Next sht_inv
wb.Close SaveChanges:=True
wbinv.Close SaveChanges:=False
'Ensure Workbook has closed before moving on to next line of code
DoEvents
lobj_name = ""
Set lobj = Nothing
Set sht = Nothing
Set wb = Nothing
Skip_File:
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
End Sub
Display More
Any help would be appreciated.
Dave