Import causes a 1004 runtime error whats the issue?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I have the stranged issue right now.
The Story: my Makro was running fine but I needed to export it to another file and import(including formats) the needed data (long story) sounds easy enough, I create a new file (xlsb like the original) export and import the module and rename the tabs accordingly. I create 2 "open file" dialogs and to import the data from other files. Import works everything fine.

But the following code as well as even a very simple Sub (just for cleaning a sheet) Throws 1004 exceptions as soons as anything that uses a method (i guess) appears. Here is the code for the import just in case it matters.

Public Sub Import()



Dim VarDateiPfad As String

Dim YSource As Workbook

Dim XSource As Workbook

Dim Destination As Workbook



Set Destination = ActiveWorkbook



'Startet "Import" Dialog und legt ausgewählte Datei in "VarDateiPfad" ab

VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set YSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)





    YSource.Sheets("Y").UsedRange.Copy

        Destination.Sheets("Y").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        YSource.Close





VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set XSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)



    XlisteSource.Sheets("X").UsedRange.Copy

        Destination.Sheets("X").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        XSource.Close

Hi Thanks you both, unfortunatly this is just an error I made. I changed some of the names because i didnt wont to put the real names online (paranoid i guess) but i checked all varaibles names any way and they fit.

Depending on which sub i execute its:

Worksheets("Transfer").Range(Cells(VarZeilenTransfer, 1), Cells(VarZeilenTransfer, VarSpaltenTransfer)).Interior.Color = RGB(229, 229, 229)

OR:

VarZeilenTransfer = Worksheets("Transfer").Cells(Rows.Count, 1).End(xlUp).Offset(1).row

The varaibles/sheet names arent the issue here either(checked them). Also the Sheets are not read-only or anything. And as stated the excel file is completly new but since i copied (exp /impo) the code from a working source I guess the issue isnt within the code itself (i assume). Strange issue...
Sorry if my discriptio isnt very good, please feel free to ask.

edited Nov 22 '18 at 11:28

asked Nov 22 '18 at 9:39

G.M

176

Which line causes the error? and what is XlisteSource

– krish KM
Nov 22 '18 at 9:55

Maybe put Option Explicit before your code. It's possible that this line XlisteSource.Sheets("X").UsedRange.Copy should be XSource.Sheets("X").UsedRange.Copy as XlisteSource does not appear to be declared elsewhere.

– chillin
Nov 22 '18 at 10:41

I just tried Option Explicit. This maybe the issue. I used global variables (Public X as integer) and they seem to not work anymore :O. Any idea how this could happen? do i have to mark the region i used global varaibles in like an End public or something like that?

– G.M
Nov 22 '18 at 11:30

#1 If you get a message about an undeclared variable (due to Option Explicit), declare the variable in the appropriate place in the code. Do this until all variables are declared. #2 Step through each line in the code withF8 . If you encounter run-time 1004, check in the local window if the object (in the line that you're currently on) has been instantiated, or debug.print in the immediate window to see if the object is nothing. Also, tell us which line(s) is causing the error. Since you've exported and reimported the module, you can safely amend/work on this copy of the code.

– chillin
Nov 22 '18 at 19:06

Thanks alot i guess ifigured it, one of my global variables was not declared correctly.... stupid me... thx

– G.M
Nov 23 '18 at 8:37

add a comment |

Public Sub Import()



Dim VarDateiPfad As String

Dim YSource As Workbook

Dim XSource As Workbook

Dim Destination As Workbook



Set Destination = ActiveWorkbook



'Startet "Import" Dialog und legt ausgewählte Datei in "VarDateiPfad" ab

VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set YSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)





    YSource.Sheets("Y").UsedRange.Copy

        Destination.Sheets("Y").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        YSource.Close





VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set XSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)



    XlisteSource.Sheets("X").UsedRange.Copy

        Destination.Sheets("X").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        XSource.Close

Depending on which sub i execute its:

Worksheets("Transfer").Range(Cells(VarZeilenTransfer, 1), Cells(VarZeilenTransfer, VarSpaltenTransfer)).Interior.Color = RGB(229, 229, 229)

OR:

VarZeilenTransfer = Worksheets("Transfer").Cells(Rows.Count, 1).End(xlUp).Offset(1).row

edited Nov 22 '18 at 11:28

asked Nov 22 '18 at 9:39

G.M

176

Which line causes the error? and what is XlisteSource

– krish KM
Nov 22 '18 at 9:55

Maybe put Option Explicit before your code. It's possible that this line XlisteSource.Sheets("X").UsedRange.Copy should be XSource.Sheets("X").UsedRange.Copy as XlisteSource does not appear to be declared elsewhere.

– chillin
Nov 22 '18 at 10:41

I just tried Option Explicit. This maybe the issue. I used global variables (Public X as integer) and they seem to not work anymore :O. Any idea how this could happen? do i have to mark the region i used global varaibles in like an End public or something like that?

– G.M
Nov 22 '18 at 11:30

#1 If you get a message about an undeclared variable (due to Option Explicit), declare the variable in the appropriate place in the code. Do this until all variables are declared. #2 Step through each line in the code withF8 . If you encounter run-time 1004, check in the local window if the object (in the line that you're currently on) has been instantiated, or debug.print in the immediate window to see if the object is nothing. Also, tell us which line(s) is causing the error. Since you've exported and reimported the module, you can safely amend/work on this copy of the code.

– chillin
Nov 22 '18 at 19:06

Thanks alot i guess ifigured it, one of my global variables was not declared correctly.... stupid me... thx

– G.M
Nov 23 '18 at 8:37

add a comment |

Public Sub Import()



Dim VarDateiPfad As String

Dim YSource As Workbook

Dim XSource As Workbook

Dim Destination As Workbook



Set Destination = ActiveWorkbook



'Startet "Import" Dialog und legt ausgewählte Datei in "VarDateiPfad" ab

VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set YSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)





    YSource.Sheets("Y").UsedRange.Copy

        Destination.Sheets("Y").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        YSource.Close





VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set XSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)



    XlisteSource.Sheets("X").UsedRange.Copy

        Destination.Sheets("X").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        XSource.Close

Depending on which sub i execute its:

Worksheets("Transfer").Range(Cells(VarZeilenTransfer, 1), Cells(VarZeilenTransfer, VarSpaltenTransfer)).Interior.Color = RGB(229, 229, 229)

OR:

VarZeilenTransfer = Worksheets("Transfer").Cells(Rows.Count, 1).End(xlUp).Offset(1).row

edited Nov 22 '18 at 11:28

asked Nov 22 '18 at 9:39

G.M

176

Public Sub Import()



Dim VarDateiPfad As String

Dim YSource As Workbook

Dim XSource As Workbook

Dim Destination As Workbook



Set Destination = ActiveWorkbook



'Startet "Import" Dialog und legt ausgewählte Datei in "VarDateiPfad" ab

VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set YSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)





    YSource.Sheets("Y").UsedRange.Copy

        Destination.Sheets("Y").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        YSource.Close





VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)



'Öffnet die ausgewählte Datei

Set XSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)



    XlisteSource.Sheets("X").UsedRange.Copy

        Destination.Sheets("X").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        XSource.Close

Depending on which sub i execute its:

Worksheets("Transfer").Range(Cells(VarZeilenTransfer, 1), Cells(VarZeilenTransfer, VarSpaltenTransfer)).Interior.Color = RGB(229, 229, 229)

OR:

VarZeilenTransfer = Worksheets("Transfer").Cells(Rows.Count, 1).End(xlUp).Offset(1).row

excel vba excel-vba runtime-error ms-error-1004

edited Nov 22 '18 at 11:28

asked Nov 22 '18 at 9:39

G.M

176

edited Nov 22 '18 at 11:28

asked Nov 22 '18 at 9:39

G.M

176

edited Nov 22 '18 at 11:28

asked Nov 22 '18 at 9:39

G.M

176

asked Nov 22 '18 at 9:39

G.M

176

asked Nov 22 '18 at 9:39

G.M

176

Which line causes the error? and what is XlisteSource

– krish KM
Nov 22 '18 at 9:55

Maybe put Option Explicit before your code. It's possible that this line XlisteSource.Sheets("X").UsedRange.Copy should be XSource.Sheets("X").UsedRange.Copy as XlisteSource does not appear to be declared elsewhere.

– chillin
Nov 22 '18 at 10:41

I just tried Option Explicit. This maybe the issue. I used global variables (Public X as integer) and they seem to not work anymore :O. Any idea how this could happen? do i have to mark the region i used global varaibles in like an End public or something like that?

– G.M
Nov 22 '18 at 11:30

#1 If you get a message about an undeclared variable (due to Option Explicit), declare the variable in the appropriate place in the code. Do this until all variables are declared. #2 Step through each line in the code withF8 . If you encounter run-time 1004, check in the local window if the object (in the line that you're currently on) has been instantiated, or debug.print in the immediate window to see if the object is nothing. Also, tell us which line(s) is causing the error. Since you've exported and reimported the module, you can safely amend/work on this copy of the code.

– chillin
Nov 22 '18 at 19:06

Thanks alot i guess ifigured it, one of my global variables was not declared correctly.... stupid me... thx

– G.M
Nov 23 '18 at 8:37

add a comment |

Which line causes the error? and what is XlisteSource

– krish KM
Nov 22 '18 at 9:55

Maybe put Option Explicit before your code. It's possible that this line XlisteSource.Sheets("X").UsedRange.Copy should be XSource.Sheets("X").UsedRange.Copy as XlisteSource does not appear to be declared elsewhere.

– chillin
Nov 22 '18 at 10:41

I just tried Option Explicit. This maybe the issue. I used global variables (Public X as integer) and they seem to not work anymore :O. Any idea how this could happen? do i have to mark the region i used global varaibles in like an End public or something like that?

– G.M
Nov 22 '18 at 11:30

#1 If you get a message about an undeclared variable (due to Option Explicit), declare the variable in the appropriate place in the code. Do this until all variables are declared. #2 Step through each line in the code withF8 . If you encounter run-time 1004, check in the local window if the object (in the line that you're currently on) has been instantiated, or debug.print in the immediate window to see if the object is nothing. Also, tell us which line(s) is causing the error. Since you've exported and reimported the module, you can safely amend/work on this copy of the code.

– chillin
Nov 22 '18 at 19:06

Thanks alot i guess ifigured it, one of my global variables was not declared correctly.... stupid me... thx

– G.M
Nov 23 '18 at 8:37

Which line causes the error? and what is XlisteSource

– krish KM
Nov 22 '18 at 9:55

Maybe put Option Explicit before your code. It's possible that this line XlisteSource.Sheets("X").UsedRange.Copy should be XSource.Sheets("X").UsedRange.Copy as XlisteSource does not appear to be declared elsewhere.

– chillin
Nov 22 '18 at 10:41

I just tried Option Explicit. This maybe the issue. I used global variables (Public X as integer) and they seem to not work anymore :O. Any idea how this could happen? do i have to mark the region i used global varaibles in like an End public or something like that?

– G.M
Nov 22 '18 at 11:30

#1 If you get a message about an undeclared variable (due to Option Explicit), declare the variable in the appropriate place in the code. Do this until all variables are declared. #2 Step through each line in the code withF8 . If you encounter run-time 1004, check in the local window if the object (in the line that you're currently on) has been instantiated, or debug.print in the immediate window to see if the object is nothing. Also, tell us which line(s) is causing the error. Since you've exported and reimported the module, you can safely amend/work on this copy of the code.

– chillin
Nov 22 '18 at 19:06

Thanks alot i guess ifigured it, one of my global variables was not declared correctly.... stupid me... thx

– G.M
Nov 23 '18 at 8:37

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53427870%2fimport-causes-a-1004-runtime-error-whats-the-issue%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Cfrgtkky