Copy data one excel to another excel
0
I need to copy the data of workbook 1 column C & D row data in to workbook 2 sheet3 but there is an error.
Sub Copy_Paste()
Dim Openfile As String ' Openfile
Dim lstrow As Long
Dim i As Long
lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lstrow
'Copy the data
ThisWorkbook.Sheets("Main").range("C:D", i).Copy
'Activate the destination worksheet
Workbooks.Open Filename:=range("H", i)
Sheets("Sheet3").Activate
'Select the target range
range("B2:C2").Select
'Paste in the target destination
ActiveSheet.PasteSpecial xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Close SaveChanges:=True
Next
End Sub
microsoft-excel vba copy-paste
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
|
show 2 more comments
0
I need to copy the data of workbook 1 column C & D row data in to workbook 2 sheet3 but there is an error.
Sub Copy_Paste()
Dim Openfile As String ' Openfile
Dim lstrow As Long
Dim i As Long
lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lstrow
'Copy the data
ThisWorkbook.Sheets("Main").range("C:D", i).Copy
'Activate the destination worksheet
Workbooks.Open Filename:=range("H", i)
Sheets("Sheet3").Activate
'Select the target range
range("B2:C2").Select
'Paste in the target destination
ActiveSheet.PasteSpecial xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Close SaveChanges:=True
Next
End Sub
microsoft-excel vba copy-paste
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
Which line is giving you the error and what is that error?.
– Mark Fitzgerald
Feb 25 at 9:35
@MarkFitzgerald Application Defined or object defined error (Runtime error 1004)
– Shriharsha Hunsur
Feb 25 at 9:51
Is that inThisWorkbook.Sheets("Main").range("C:D", i).Copy?
– Mark Fitzgerald
Feb 25 at 9:54
@MarkFitzgerald Yes for that line only. It means its a workbook 1 Main sheet i need to copy the data of C & D column particular row and paste into Workbook2 sheet3 B2&C2 accordingly
– Shriharsha Hunsur
Feb 25 at 9:58
Are there separate workbooks for reach copied row? I ask because your code is opening a workbook during every iteration of the loop.
– Mark Fitzgerald
Feb 25 at 10:04
|
show 2 more comments
0
0
0
I need to copy the data of workbook 1 column C & D row data in to workbook 2 sheet3 but there is an error.
Sub Copy_Paste()
Dim Openfile As String ' Openfile
Dim lstrow As Long
Dim i As Long
lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lstrow
'Copy the data
ThisWorkbook.Sheets("Main").range("C:D", i).Copy
'Activate the destination worksheet
Workbooks.Open Filename:=range("H", i)
Sheets("Sheet3").Activate
'Select the target range
range("B2:C2").Select
'Paste in the target destination
ActiveSheet.PasteSpecial xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Close SaveChanges:=True
Next
End Sub
microsoft-excel vba copy-paste
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
I need to copy the data of workbook 1 column C & D row data in to workbook 2 sheet3 but there is an error.
Sub Copy_Paste()
Dim Openfile As String ' Openfile
Dim lstrow As Long
Dim i As Long
lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lstrow
'Copy the data
ThisWorkbook.Sheets("Main").range("C:D", i).Copy
'Activate the destination worksheet
Workbooks.Open Filename:=range("H", i)
Sheets("Sheet3").Activate
'Select the target range
range("B2:C2").Select
'Paste in the target destination
ActiveSheet.PasteSpecial xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Close SaveChanges:=True
Next
End Sub
microsoft-excel vba copy-paste
microsoft-excel vba copy-paste
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
edited Feb 25 at 9:52
Mark Fitzgerald
4021311
4021311
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
asked Feb 25 at 9:18
Shriharsha HunsurShriharsha Hunsur
36
36
Which line is giving you the error and what is that error?.
– Mark Fitzgerald
Feb 25 at 9:35
@MarkFitzgerald Application Defined or object defined error (Runtime error 1004)
– Shriharsha Hunsur
Feb 25 at 9:51
Is that inThisWorkbook.Sheets("Main").range("C:D", i).Copy?
– Mark Fitzgerald
Feb 25 at 9:54
@MarkFitzgerald Yes for that line only. It means its a workbook 1 Main sheet i need to copy the data of C & D column particular row and paste into Workbook2 sheet3 B2&C2 accordingly
– Shriharsha Hunsur
Feb 25 at 9:58
Are there separate workbooks for reach copied row? I ask because your code is opening a workbook during every iteration of the loop.
– Mark Fitzgerald
Feb 25 at 10:04
|
show 2 more comments
Which line is giving you the error and what is that error?.
– Mark Fitzgerald
Feb 25 at 9:35
@MarkFitzgerald Application Defined or object defined error (Runtime error 1004)
– Shriharsha Hunsur
Feb 25 at 9:51
Is that inThisWorkbook.Sheets("Main").range("C:D", i).Copy?
– Mark Fitzgerald
Feb 25 at 9:54
@MarkFitzgerald Yes for that line only. It means its a workbook 1 Main sheet i need to copy the data of C & D column particular row and paste into Workbook2 sheet3 B2&C2 accordingly
– Shriharsha Hunsur
Feb 25 at 9:58
Are there separate workbooks for reach copied row? I ask because your code is opening a workbook during every iteration of the loop.
– Mark Fitzgerald
Feb 25 at 10:04
Which line is giving you the error and what is that error?.
– Mark Fitzgerald
Feb 25 at 9:35
Which line is giving you the error and what is that error?.
– Mark Fitzgerald
Feb 25 at 9:35
@MarkFitzgerald Application Defined or object defined error (Runtime error 1004)
– Shriharsha Hunsur
Feb 25 at 9:51
@MarkFitzgerald Application Defined or object defined error (Runtime error 1004)
– Shriharsha Hunsur
Feb 25 at 9:51
Is that in
ThisWorkbook.Sheets("Main").range("C:D", i).Copy?– Mark Fitzgerald
Feb 25 at 9:54
Is that in
ThisWorkbook.Sheets("Main").range("C:D", i).Copy?– Mark Fitzgerald
Feb 25 at 9:54
@MarkFitzgerald Yes for that line only. It means its a workbook 1 Main sheet i need to copy the data of C & D column particular row and paste into Workbook2 sheet3 B2&C2 accordingly
– Shriharsha Hunsur
Feb 25 at 9:58
@MarkFitzgerald Yes for that line only. It means its a workbook 1 Main sheet i need to copy the data of C & D column particular row and paste into Workbook2 sheet3 B2&C2 accordingly
– Shriharsha Hunsur
Feb 25 at 9:58
Are there separate workbooks for reach copied row? I ask because your code is opening a workbook during every iteration of the loop.
– Mark Fitzgerald
Feb 25 at 10:04
Are there separate workbooks for reach copied row? I ask because your code is opening a workbook during every iteration of the loop.
– Mark Fitzgerald
Feb 25 at 10:04
|
show 2 more comments
1 Answer
1
active
oldest
votes
0
You should really try to avoid the activation and select in your code. Also, there are some things currently in it that are useless or make no sense.
Give this a try. Upon error, make sure that the strFileName is the full path to the workbook not just the filename (refer to here for more info on that):
Option Explicit
Sub Copy_Paste()
Application.ScreenUpdating = False
Dim wb As Workbook, wbPaste As Workbook
Dim ws As Worksheet, wsPaste As Worksheet
Dim strFileName As String
Dim lstrow As Long, i As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets("Main")
lstrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lstrow
'Set data to copy
Set CopyRange = ws.Range(ws.Cells(i, 3), ws.Cells(i, 4))
' Set PasteRange
strFileName = ws.Range("H", i).Value
'Open workbook in which you want to paste
Set wbPaste = Workbooks.Open(strFileName)
'Define the worksheet
Set wsPaste = wbPaste.Sheets("Sheet3")
'Paste
wsPaste.Range(wsPaste.Cells(2, 2), wsPaste.Cells(2, 3)).Value = CopyRange.Value
Next i
Application.ScreenUpdating = True
End Sub
answered Feb 25 at 11:36
LambikLambik
116
changestrFileName = ws.Range("H", i).Value
– Ted D.
Feb 25 at 19:25
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1409181%2fcopy-data-one-excel-to-another-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You should really try to avoid the activation and select in your code. Also, there are some things currently in it that are useless or make no sense.
Give this a try. Upon error, make sure that the strFileName is the full path to the workbook not just the filename (refer to here for more info on that):
Option Explicit
Sub Copy_Paste()
Application.ScreenUpdating = False
Dim wb As Workbook, wbPaste As Workbook
Dim ws As Worksheet, wsPaste As Worksheet
Dim strFileName As String
Dim lstrow As Long, i As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets("Main")
lstrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lstrow
'Set data to copy
Set CopyRange = ws.Range(ws.Cells(i, 3), ws.Cells(i, 4))
' Set PasteRange
strFileName = ws.Range("H", i).Value
'Open workbook in which you want to paste
Set wbPaste = Workbooks.Open(strFileName)
'Define the worksheet
Set wsPaste = wbPaste.Sheets("Sheet3")
'Paste
wsPaste.Range(wsPaste.Cells(2, 2), wsPaste.Cells(2, 3)).Value = CopyRange.Value
Next i
Application.ScreenUpdating = True
End Sub
answered Feb 25 at 11:36
LambikLambik
116
changestrFileName = ws.Range("H", i).Value
– Ted D.
Feb 25 at 19:25
add a comment |
0
You should really try to avoid the activation and select in your code. Also, there are some things currently in it that are useless or make no sense.
Give this a try. Upon error, make sure that the strFileName is the full path to the workbook not just the filename (refer to here for more info on that):
Option Explicit
Sub Copy_Paste()
Application.ScreenUpdating = False
Dim wb As Workbook, wbPaste As Workbook
Dim ws As Worksheet, wsPaste As Worksheet
Dim strFileName As String
Dim lstrow As Long, i As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets("Main")
lstrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lstrow
'Set data to copy
Set CopyRange = ws.Range(ws.Cells(i, 3), ws.Cells(i, 4))
' Set PasteRange
strFileName = ws.Range("H", i).Value
'Open workbook in which you want to paste
Set wbPaste = Workbooks.Open(strFileName)
'Define the worksheet
Set wsPaste = wbPaste.Sheets("Sheet3")
'Paste
wsPaste.Range(wsPaste.Cells(2, 2), wsPaste.Cells(2, 3)).Value = CopyRange.Value
Next i
Application.ScreenUpdating = True
End Sub
answered Feb 25 at 11:36
LambikLambik
116
changestrFileName = ws.Range("H", i).Value
– Ted D.
Feb 25 at 19:25
add a comment |
0
0
0
You should really try to avoid the activation and select in your code. Also, there are some things currently in it that are useless or make no sense.
Give this a try. Upon error, make sure that the strFileName is the full path to the workbook not just the filename (refer to here for more info on that):
Option Explicit
Sub Copy_Paste()
Application.ScreenUpdating = False
Dim wb As Workbook, wbPaste As Workbook
Dim ws As Worksheet, wsPaste As Worksheet
Dim strFileName As String
Dim lstrow As Long, i As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets("Main")
lstrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lstrow
'Set data to copy
Set CopyRange = ws.Range(ws.Cells(i, 3), ws.Cells(i, 4))
' Set PasteRange
strFileName = ws.Range("H", i).Value
'Open workbook in which you want to paste
Set wbPaste = Workbooks.Open(strFileName)
'Define the worksheet
Set wsPaste = wbPaste.Sheets("Sheet3")
'Paste
wsPaste.Range(wsPaste.Cells(2, 2), wsPaste.Cells(2, 3)).Value = CopyRange.Value
Next i
Application.ScreenUpdating = True
End Sub
answered Feb 25 at 11:36
LambikLambik
116
You should really try to avoid the activation and select in your code. Also, there are some things currently in it that are useless or make no sense.
Give this a try. Upon error, make sure that the strFileName is the full path to the workbook not just the filename (refer to here for more info on that):
Option Explicit
Sub Copy_Paste()
Application.ScreenUpdating = False
Dim wb As Workbook, wbPaste As Workbook
Dim ws As Worksheet, wsPaste As Worksheet
Dim strFileName As String
Dim lstrow As Long, i As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets("Main")
lstrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lstrow
'Set data to copy
Set CopyRange = ws.Range(ws.Cells(i, 3), ws.Cells(i, 4))
' Set PasteRange
strFileName = ws.Range("H", i).Value
'Open workbook in which you want to paste
Set wbPaste = Workbooks.Open(strFileName)
'Define the worksheet
Set wsPaste = wbPaste.Sheets("Sheet3")
'Paste
wsPaste.Range(wsPaste.Cells(2, 2), wsPaste.Cells(2, 3)).Value = CopyRange.Value
Next i
Application.ScreenUpdating = True
End Sub
answered Feb 25 at 11:36
LambikLambik
116
answered Feb 25 at 11:36
LambikLambik
116
answered Feb 25 at 11:36
LambikLambik
116
answered Feb 25 at 11:36
LambikLambik
116
116
changestrFileName = ws.Range("H", i).Value
– Ted D.
Feb 25 at 19:25
add a comment |
changestrFileName = ws.Range("H", i).Value
– Ted D.
Feb 25 at 19:25
change
strFileName = ws.Range("H", i).Value– Ted D.
Feb 25 at 19:25
change
strFileName = ws.Range("H", i).Value– Ted D.
Feb 25 at 19:25
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1409181%2fcopy-data-one-excel-to-another-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Which line is giving you the error and what is that error?.
– Mark Fitzgerald
Feb 25 at 9:35
@MarkFitzgerald Application Defined or object defined error (Runtime error 1004)
– Shriharsha Hunsur
Feb 25 at 9:51
Is that in
ThisWorkbook.Sheets("Main").range("C:D", i).Copy?– Mark Fitzgerald
Feb 25 at 9:54
@MarkFitzgerald Yes for that line only. It means its a workbook 1 Main sheet i need to copy the data of C & D column particular row and paste into Workbook2 sheet3 B2&C2 accordingly
– Shriharsha Hunsur
Feb 25 at 9:58
Are there separate workbooks for reach copied row? I ask because your code is opening a workbook during every iteration of the loop.
– Mark Fitzgerald
Feb 25 at 10:04