PastSpecial overwrites where I want to append

Hi there,

I am new at VBA and I am working on my first project.

I have several detailed workbooks from which I want to copy a certain table to a central workbook.
Below is part of the code that I have written.
The problem that I encounter is that the code does not append to the bottom of the table in the central workbook, but overwrites the already existing data. What am I not doing correctly?

The code is run from the central workbook and runs through the several detailed workbooks.
The code is the part where I open a detailed workbook and append the contents of the table "TableFcstDetails" in the detailed workbook into the table "TableFcstCentral" in the central workbook.


'If Forecast Details Workbook Status = "Submitted" and Forecast Details Workbook Process Status = "NOT Procssed"
'Then Append table "TableFcstDetails" from Detail workbook to table "TableFcstCentral" in Central workbook - values only
If TmpDtlsStts = "Submitted" And TmpDtlsPrcssStts = "NOT Processed" Then
TmpCntrlFile = TmpPath & "Forecast - Central - 2013-04-20 VBA MASTER.xlsm"
TmpPathFile = TmpPath & TmpFile

Dim sWb As Workbook
Dim dWb As Workbook
Dim sWs As Worksheet
Dim dWs As Worksheet
Dim sLo As ListObject
Dim dLo As ListObject
Dim sRng, dRng As Range

Set dWb = ThisWorkbook
Set dWs = dWb.Worksheets("Central")
Set dLo = dWs.ListObjects("TableFcstCentral")
Set dRng = dLo.HeaderRowRange.Columns(1).Offset(dLo.ListRows.Count + 1, 0)

'Open Detail workbook
Workbooks.Open Filename:=TmpPathFile

Set sWb = Workbooks(TmpFile)
Set sWs = sWb.Worksheets("Details")
Set sLo = sWs.ListObjects("TableFcstDetails")
Set sRng = sLo.DataBodyRange

sRng.PasteSpecial
dRng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'Update Forecast Details Workbook Process Status to "Processed"
'***Forecast Details Workbook Process Status is in Details worbook worksheet "Local Master Data" cell B17
Workbooks(TmpFile).Sheets("Local Master Data").Range("B17").Value = "Processed"

'Close Detail workbook
Workbooks(TmpFile).Save
Workbooks(TmpFile).Close False

Worksheets("Processing Status").Activate
Application.CutCopyMode = False


Any help will be highly appreciated!

Trying to better explain my issue. Hoping for some help.


The copy and patespecial work OK the first time processing the first detail workbook and table.

sRng.PasteSpecial
dRng.PasteSpecial Paste:=xlPasteValues

It copies the data from the source table to the destination table.

BUT when the code processes the next detail workbook and table the data is NOT appended, but written over the data already in the destination table.

When I look in detail what happens I see that the first data pasted into the destination table is still selected when the data from the second source table is pasted into the destination table.

Is there anybody that can offer some help?
MUCH APPRECIATED.

Purpose,
Please use code tags with your code. It is difficult to read your code with out code tags. If the code is pasting over the same data, it is most likely your next empty cell is not set up properly. Your code is not finding the next empty cell to paste over, so check what is your next empty cell.

Hi AB33,

Thanks for your response.
As I am new to VBA I don't really understand what you mean with your comment about tags, but I have added a snapshot of my code in a picture. I don't now how to create these tekst boxes with formatted code as you see in other threads. Sorry.

The code LINE where is determine the target range is below:

Set dRng = dLo.HeaderRowRange.Columns(1).Offset(dLo.ListRows.Count + 1, 0)

Hope this helps clarify

Regards,
Purpose


Code sample.png

Set dRng = dLo.HeaderRowRange.Columns(1).Offset(dLo.ListRows.Count + 1, 0)

Look at my line. The line is wrapped with code tags.
You have a table and are looking for the last row in that table.
Try to find the last empty cell in the table first For e.g

 LR = Range("A" & Rows.Count).End(xlUp).Row

Gives you the last row in column A and you then add 1 to make it to the next row

Hi AB33,

Thanks again for your response. I think I got it about the code tags - see below.

In essence what I am trying to accomplish is the following:
1) I have a set of detailed workbooks which each have a table called TableFcstDetails;
2) I have a central workbook which has a table called TableFcstCentral;
3) I want to append the data from each table in a detailed workbook to the table in the central workbook.

Any help solving this will be much appreciated.

Regards,
Purpose

    'Empty table with processing status
    Sheets("Processing Status").Range("TablePrcssStts").Delete
    Application.ScreenUpdating = False
    
    Dim TableProcess As Range
    Dim row As Range
    Newrow = 0
    Set TableProcess = Sheets("Central Master Data").Range("TableForecasters")
    'For every row in table "TableForecasters"
    For Each row In [TableForecasters].Rows
            
        'If Forecast Details Workbook Status = "Submitted" and Forecast Details Workbook Process Status = "NOT Procssed"
        'Then Append table "TableFcstDetails" from Detail workbook to table "TableFcstCentral" in Central workbook - values only
        If TmpDtlsStts = "Submitted" And TmpDtlsPrcssStts = "NOT Processed" Then
                        
                        Dim sWb As Workbook
                        Dim dWb As Workbook
                        Dim sWs As Worksheet
                        Dim dWs As Worksheet
                        Dim sLo As ListObject
                        Dim dLo As ListObject
                        Dim sRng, dRng As Range
    
                        Set dWb = ThisWorkbook
                        Set dWs = dWb.Worksheets("Central")
                        Set dLo = dWs.ListObjects("TableFcstCentral")
                        'This is where I determine the target range
                        Set dRng = dLo.HeaderRowRange.Columns(1).Offset(dLo.ListRows.Count + 1, 0)
                        
                        'Open Detail workbook
                        Workbooks.Open Filename:=TmpPathFile
                        
                        Set sWb = Workbooks(TmpFile)
                        Set sWs = sWb.Worksheets("Details")
                        Set sLo = sWs.ListObjects("TableFcstDetails")
                        Set sRng = sLo.DataBodyRange
                        
                        'do the actual copy of the data
                        sRng.Copy
                        dRng.PasteSpecial Paste:=xlPasteValues
                        
                        'Close Detail workbook
                        Workbooks(TmpFile).Save
                        Workbooks(TmpFile).Close False
                        
                        Application.CutCopyMode = False
                
        End If
            
    Next
    Application.ScreenUpdating = True
    MsgBox ("Applicable data has been retrieved.")

You have already got a code, but you are getting an error. Have you tried my last suggestion? Since you are working on lists(Tables), the syntax is different from a normal non-table sheet.