VBA code to select only a table I am getting a Run-time error 1004 Method Range of object Global failed

0 votes

I'm trying to create VBA code that will only copy and paste the table from the sheet onto another sheet. I'm receiving the following Run-time error: 1004 The 'Range' method of the '_Global' object failed. My best bet is that I am misrepresenting one or more of the sources. Why am I misusing this? I sincerely appreciate any assistance.

This is the code:

Sub Final_Report()

' Final_Report Macro

   Sheets("Main Table(Atlas)").Select
    Dim rngData As Range
    Dim intRow As Integer
    Dim intColumn As Integer

        intRow = Range("AtlasReport_1_Table_1!11").Row
        intColumn = Range("AtlasReport_1_Table_1!1").Column

        Set rngData = Worksheets("AtlasReport_1_Table_1").UsedRange
        rngData.Offset(intRow - 1, intColumn - 1).Resize(rngData.Rows.Count - intRow + 1, rngData.Columns.Count - intColumn + 1).Select
 
    Selection.Copy
    Sheets("Final").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub
Jan 13, 2023 in Others by Kithuzzz
• 38,020 points
1,062 views

1 answer to this question.

0 votes

No copy/paste, just direct assignment use.Value

Sub Final_Report()
    Dim wb As Workbook
    
    Set wb = ThisWorkbook
    With wb.Worksheets("Main Table(Atlas)").ListObjects("AtlasReport_1_Table_1").Range
        wb.Worksheets("Final").Range("B1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub
answered Jan 13, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Macro VBA code failing to execute a section of the code without an error

You get your code to make a ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,020 points
572 views
0 votes
1 answer

I am trying to run following command But I end up with an error :

Hii Nishant, You are running this command inside ...READ MORE

answered Apr 6, 2020 in Others by Niroj
• 82,880 points
1,810 views
0 votes
1 answer

I am getting Failed to load resource: net::ERR_BLOCKED_BY_CLIENT with Google chrome

These errors are usually generated from an ...READ MORE

answered Feb 16, 2022 in Others by Soham
• 9,710 points
5,344 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,107 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,538 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
828 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
986 views
0 votes
1 answer

Excel VBA - Subscript Out of Range Error (Run Time: Error 9)

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,720 points
967 views
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,720 points
2,208 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP