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 in Others by Kithuzzz
• 28,900 points
84 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 by narikkadan
• 53,520 points

Related Questions In Others

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,840 points
1,058 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,700 points
2,287 views
0 votes
1 answer

VBA How to extract the date and time of arrival of a answered email

Use MailItem.ReceivedTime property. I hope this helps you ...READ MORE

answered Jan 9 in Others by narikkadan
• 53,520 points
42 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
533 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
2,415 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
• 22,960 points
117 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,630 points
300 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 in Others by narikkadan
• 53,520 points
47 views
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 53,520 points
82 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