0

my skills in Excel VBA come from googling and thus I've ran into an issue. I've used copy and paste and this import code without issue and without having to activate the sheet before so I am unsure why this is erroring out now. I import the data form one workbook into the Macro, and then copy certain columns into another sheet. However, the code only seems to work if the sheet "Cut Report" is active, else it gives a 1004 error (or others). Even with the sheet active, I can see it selecting the rows below the data before switching the sheet to paste.

Sub ImportCut()
' Get workbook...
Dim ws As Worksheet
Dim filter As String
Application.ScreenUpdating = False
Dim targetWorkbook As Workbook, wb As Workbook
Dim Ret As Variant

MsgBox ("Please select the Cut Report.")
Set targetWorkbook = Application.ActiveWorkbook 'The current open Workbook
     ' get the customer workbook
            Caption = "Please Select an input file "
            Ret = Application.GetOpenFilename(filter, , Caption) 'Opens dialog box to find and select file.
        
                If Ret = False Then End 'If no file is selected, program ends.
                
                Set wb = Workbooks.Open(Ret) 'sets selected workbook to wb.
            
                wb.Sheets(1).UsedRange.Copy targetWorkbook.Sheets("Cut Report").Range("A1") 'copies data from selected wb to current open workbook.
                wb.Close 'closes wb
End Sub

Code with Issue, I've tried with "Sheets", "Worksheets", "Workbook("Macro Sheet").Worksheets("Cut Report"). Even testing with Sheets("Cut Report").Range("AC2").Select it gives an error.

Sub CopyOverBasic()
    Sheets("Cut Report").Range("AC2", Range("AC2").End(xlDown)).Copy Sheets("Customer Report").Range("A8")
    Sheets("Cut Report").Range("G2", Range("G1").End(xlDown)).Copy Sheets("Customer Report").Range("B8")
    Sheets("Cut Report").Range("Z2", Range("Z1").End(xlDown)).Copy Sheets("Customer Report").Range("C8")
    Sheets("Cut Report").Range("I2", Range("I1").End(xlDown)).Copy Sheets("Customer Report").Range("D8")
    Application.CutCopyMode = False
End Sub
SmuckZC
  • 1
  • The problem is that `Range("AC2").End(xlDown)` and similar are unqualified, and need a `Sheets("Cut Report").` in front of them. – BigBen Sep 19 '22 at 19:42
  • @BigBen - Added to `Sheets("Cut Report").Range("AC2", Sheets("Cut Report").Range("AC2").End(xlDown)).Copy Worksheets("Tractor Supply Cut Report").Range("A8")` and error 1004 states that the copy area and the paste area are not the same size. Without `Sheets("Cut Report").` and using `Sheets("Cut Report").Activate` at the top of the code that error doesnt come up and it "works" but as stated it still is acting wonky. --- Its just weird that this issue started happening with this code and not others. Edit for clarity – SmuckZC Sep 19 '22 at 20:08
  • You probably should be using `.End(xlUp)` to find the last row as demonstrated [here](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba). – BigBen Sep 19 '22 at 20:10
  • @BigBen - That worked better, however for some reason this `Sheets("Cut Report").Range("AC2", "AC" & Range("A" & Rows.Count).End(xlUp).Row).Copy Worksheets("Tractor Supply Cut Report").Range("A8")` copies about half the data, where `Sheets("Cut Report").Range("G2", "G" & Range("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Tractor Supply Cut Report").Range("B8")` copies the full line. All of the lines should have the same number of data, thus ending on the same row, just different columns. A was used as it will always have data to the last line. -- Thanks for helping me out btw – SmuckZC Sep 19 '22 at 20:30
  • Note that `Range("A" & Rows.Count).End(xlUp).Row)` has unqualified `Range` and `Rows` references. Those need to have the parent worksheet specified. It's a lot easier if you use a `With` statement: `With Sheets("Cut Report")`... `End With`, then inside the `With` block, put a period `.` in front of *every* instance of `Range`, `Cells`, `Rows`, and `Columns`. – BigBen Sep 19 '22 at 20:32
  • 1
    That seems to have worked! Thanks again for working with me on this. – SmuckZC Sep 19 '22 at 20:39

0 Answers0