Run Time Error 1004 App Defined or User Defined Error

[Ebes1099]

I'm getting a Runtime 1004 error on my macro. I orginially recorded the macro, then created a separate sub since I want to run the macro for multiple sheets. The 1 parameter for the Sub is a String that contains the Worksheet name.

What I'm trying to do is copy data from multiple sheets into 1 sheet. There are varying rows of data in each sheet.

Here is the start of the code, the bolded line is where I get the error. I select the A6 where every sheet starts the list I want to copy. Then I want to select the entire list and copy it to paste over. Everything worked when I just recorded a macro for 1 sheet. I did that just to get a basis to create a template so I could do this for every sheet.

Sub CreateOutput(SheetNm As String)
    Dim SheetName As String
    SheetName = SheetNm
    Dim RowCount As Integer
    RowCount = Range("I1").Value
    Dim RowCountAbs As Integer
    
    
    Sheets(SheetName).Select
    ActiveSheet.Range("A6").Select
    [b]ActiveSheet.Range(Selection, Selection.End(x1Down)).Select[/b]
    Selection.Copy
    Sheets("Upload2").Select

 

[PHV]

Replace this:
Sheets(SheetName).Select
ActiveSheet.Range("A6").Select
ActiveSheet.Range(Selection, Selection.End(x1Down)).Select
Selection.Copy
with this:
With Sheets(SheetName)
.Range(.Range("A6"), .Range("A6").End(x1Down)).Copy
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ebes1099]

I'm still getting the same error, now highlighting the line in the With statement.

 

[SkipVought]

Then SheetName contains a value that is NOT a name of one of your sheets.

Why are you even doing this???

    SheetName = SheetNm

If SheetNm is a NUMBER then this is DEFINITELY a problem!

sheetnm = 1
sheetname = sheetnm
then sheets(sheetnm) <> sheets(sheetname)
because
sheets(1) <> sheets("1")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Ebes1099]

I was just doing that out of habit. I took the SheetName variable out and am just using the parameter now. But that wasn't the issue anyway, I still get the Runtime error on the same line.

 

[ItIsHardToProgram]

This works for me:

Sub CreateOutput()

Dim SheetName As String
SheetName = "test"
Dim RowCount As Integer
RowCount = ActiveSheet.Range("I1").Value
Dim RowCountAbs As Integer

Sheets(SheetName).Select
ActiveSheet.Range("A6").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Upload2").Select



End Sub

N.B. it is not x1down but xLdown

Maybe I read your code wrong, but aren't you trying to achieve the following:

ActiveSheet.Range(Selection, Selection.End([b]xLDown[/b])).Select

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Please post the code for the statement that is failing.

Also if you are using a variable, please post the value of the variable, when the statement fails.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Ebes1099]

It works now. I typed in x1 down, not xL down. Thanks.

 

[ItIsHardToProgram]

Good that it works, although I do not quite grasp what you are saying, wasn't x1 down what was written in the first place?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Well, there are quite a few dark corners of vba for me, I seriously thought x1down was not correct, and it had to be xldown ("L"), SkipVought am I missing something?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Ebes1099]

No it is xL down, I meant when I originally typed it in I misread it as x1 down. I did the record macro and was using that as my guide, I misread what it had recorded as x1 when I should have had xL.

I now have it as xL down and it's working.

 

[ItIsHardToProgram]

Ok, you should still take PHV's advice as it improves your macro, and you should definatly take SkipVought's advice, at least if you want to use SheetNM, put it as Cstr(SheetNM), but there is still a risk that SheetNM does not contain a valid Worksheet for your current workbook.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.