Argthtjtr

Haven't done any programming in 10 years and are now trying to automate some administrative work. I need to create a CSV file from a certain data set.
Running below code stand alone using this code to select the range:

Set rng = Sheets("HAOD").Range("A2", Cells(Range("a1000000").End(xlUp).row, Range("xfd1").End(xlToLeft).Column))

works fine but if I call the sub from my main program I get error 1004 and the debugger points to my range selection. I tried using this code for the range selection instead, I use this in a VBA vlookup function I found online and it works perfectly there:

rng = .Range("A2:" & tblEnd & .Cells(.Rows.Count, tblEnd).End(xlUp).row).Value

But when using this it first complains about the .-reference and when I add Sheets("HAOD") I get the 1004 error again. This time even when running the sub stand alone. What am I doing wrong?

Sub CreateCheckITfile()



    Call PopulateHAODtab

    Call SaveAsCSV



End Sub



Sub SaveAsCSV()



Dim myFile As String

Dim rng As Range

Dim cellValue As Variant

Dim i As Integer

Dim j As Integer



myFile = Application.DefaultFilePath & "HAOD.csv"



Set rng = Sheets("HAOD").Range("A2", Cells(Range("a1000000").End(xlUp).row, Range("xfd1").End(xlToLeft).Column))



Open myFile For Output Lock Write As #1



For i = 1 To rng.Rows.Count

    Dim fileRow As String

    fileRow = ""

    For j = 1 To rng.Columns.Count

        cellValue = rng.Cells(i, j).Value



        If j = rng.Columns.Count Then

            fileRow = fileRow & cellValue

            Print #1, fileRow

        Else

            fileRow = fileRow & cellValue & ","

        End If

    Next j

Next i



Close #1

End Sub

All references need to specify the sheet, otherwise they default to the active sheet

 Set rng = Sheets("HAOD").Range("A2",  Sheets("HAOD").Cells( Sheets("HAOD").Range("a1000000").End(xlUp).row,  Sheets("HAOD").Range("xfd1").End(xlToLeft).Column))

Personally, I prefer to do it in little steps that way it lets me step through and see if everything is working as intended. This is kind of similar to the second version of what you tried, but I thought it would be more clear if the address property was used instead of tblend. Using a With block also helps keep it looking tidier.

Try replacing your set statement with this:

With Sheets("HAOD")

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

    lastcol = .Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

    Set Rng = .Range("A2:" & .Cells(lastrow, lastcol).Address)

End With