VBA to copy from one Excel sheet to another

Question

I have a workbook with filtered Table. What I want to do is to open another workbook, look if there is any data there and if not copy the data from the table, paste it there and close that book.

I have the below code which i am trying but just not coming right, could anybody please try help? I keep getting subscript out of range error.

Sub Macro1()
'
' Macro1 Macro


    Dim ws As Worksheet, csv As Workbook, cCount As Long, cName As String
    Dim wb1 As Excel.Workbook
    Set wb1 = Workbooks.Open("C:\Users\matroux\Documents\TTC\TTC.xlsx")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set csv = ActiveWorkbook
    cName = csv.Name
    csv.ActiveSheet.ListObjects("Table1").Range.Copy
    
    wb1.Activate
    
    If wb1.Range("A2") = "" Then
    
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        
    Else
    End If
    
    
    csv.Close

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    
End Sub

If the error is on line `csv.ActiveSheet.ListObjects("Table1").Range.Copy` Then the active workbook is probably the workbook you want to copy to not from. Might want to read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and avoid `ActiveSheet` and `ActiveWorkbook` — Warcupine, May 07 '21 at 17:19
Yea i want to copy from the table to wb1 which is the other workbook. — Lalaland, May 07 '21 at 17:27
`wb1.Range("A2")` is error, because Workbook has not Range property. It's Worksheet's property - see https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.range — Алексей Р, May 07 '21 at 17:28
its telling me that csv.ActiveSheet.ListObjects("Table1").Range.Copy is the issue — Lalaland, May 07 '21 at 17:44
When you open `wb1` it becomes the active sheet. So `wb1` and `csv` are referring to the same workbook. — Warcupine, May 07 '21 at 17:52
thats the thing what im stuck with, when i have csv workbook i see the table. — Lalaland, May 07 '21 at 18:05

score 0 · Answer 1 · answered May 07 '21 at 18:33

Our problem is using ActiveSheet and ActiveWorkbook. Both are problematic, since they can be any open workbook or any sheet in any open workbook. If it's at all possible, declare these explicitly instead of relying on being at the right place at the right time.

VBA to copy from one Excel sheet to another

1 Answers1