Mixing
Excel VBA - Routine that finds on one sheet and paste on another moving destination cells down
Really appreciated any help on this!
I have 3 columns on 11 excel sheets that need to be copied to a unique destination sheet.
The eleven sheets refer to months, and I need to find a row referring to that month on the new worbook in order to paste, moving cells down, and then search for the next month and do the same, until all 11 months are copied.
What I have:
Sub PopulateFileTOupload()
' variables
Dim strFileToSave As String
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim rngToCopy1 As Range, rngToCopy2 As Range, rngToCopy3 As Range
Dim dt As String, wbNam As String, wbDir As String
Dim FoundCell As Range, FirstAddr As String, fnd As String, i As Long
' ================ SOURCE ================
Set wbSource = Workbooks.Open("C:UsersMLOURENCDocuments041_PRORATA_ANNUAL_CONTRACTS_UPLOAD.xls")
Set wsSource = wbSource.Worksheets("Month1")
' ================ COPY & PASTE ================
' source range1
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown))
Set rngToCopy2 = wsSource.Range("N1", wsSource.Range("N1").End(xlDown))
Set rngToCopy3 = wsSource.Range("P1", wsSource.Range("P1").End(xlDown))
Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls")
' Paste range1
' DON 't know....
' ================ SAVE ================
wbNam = "0041_PRORATA_ANNUAL_CONTRACTS_UPLOAD_READY_"
dt = Format(CStr(Now), "dd_mm_yyyy_hh_mm")
wbTarget.SaveAs Filename:=wbNam & dt
' ================ CLOSE ================'
Application.DisplayAlerts = False
wbTarget.Close
Application.DisplayAlerts = True
End Sub
excel vba
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
add a comment |
Really appreciated any help on this!
I have 3 columns on 11 excel sheets that need to be copied to a unique destination sheet.
The eleven sheets refer to months, and I need to find a row referring to that month on the new worbook in order to paste, moving cells down, and then search for the next month and do the same, until all 11 months are copied.
What I have:
Sub PopulateFileTOupload()
' variables
Dim strFileToSave As String
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim rngToCopy1 As Range, rngToCopy2 As Range, rngToCopy3 As Range
Dim dt As String, wbNam As String, wbDir As String
Dim FoundCell As Range, FirstAddr As String, fnd As String, i As Long
' ================ SOURCE ================
Set wbSource = Workbooks.Open("C:UsersMLOURENCDocuments041_PRORATA_ANNUAL_CONTRACTS_UPLOAD.xls")
Set wsSource = wbSource.Worksheets("Month1")
' ================ COPY & PASTE ================
' source range1
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown))
Set rngToCopy2 = wsSource.Range("N1", wsSource.Range("N1").End(xlDown))
Set rngToCopy3 = wsSource.Range("P1", wsSource.Range("P1").End(xlDown))
Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls")
' Paste range1
' DON 't know....
' ================ SAVE ================
wbNam = "0041_PRORATA_ANNUAL_CONTRACTS_UPLOAD_READY_"
dt = Format(CStr(Now), "dd_mm_yyyy_hh_mm")
wbTarget.SaveAs Filename:=wbNam & dt
' ================ CLOSE ================'
Application.DisplayAlerts = False
wbTarget.Close
Application.DisplayAlerts = True
End Sub
excel vba
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
wbSource.wsSource.Range("F1:F4").Copyto copy andwbDestination.wsDestination.Range("A1").Insert Shift:=xlDownto paste
– Nathan_Sav
Nov 21 '18 at 9:57
add a comment |
Really appreciated any help on this!
I have 3 columns on 11 excel sheets that need to be copied to a unique destination sheet.
The eleven sheets refer to months, and I need to find a row referring to that month on the new worbook in order to paste, moving cells down, and then search for the next month and do the same, until all 11 months are copied.
What I have:
Sub PopulateFileTOupload()
' variables
Dim strFileToSave As String
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim rngToCopy1 As Range, rngToCopy2 As Range, rngToCopy3 As Range
Dim dt As String, wbNam As String, wbDir As String
Dim FoundCell As Range, FirstAddr As String, fnd As String, i As Long
' ================ SOURCE ================
Set wbSource = Workbooks.Open("C:UsersMLOURENCDocuments041_PRORATA_ANNUAL_CONTRACTS_UPLOAD.xls")
Set wsSource = wbSource.Worksheets("Month1")
' ================ COPY & PASTE ================
' source range1
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown))
Set rngToCopy2 = wsSource.Range("N1", wsSource.Range("N1").End(xlDown))
Set rngToCopy3 = wsSource.Range("P1", wsSource.Range("P1").End(xlDown))
Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls")
' Paste range1
' DON 't know....
' ================ SAVE ================
wbNam = "0041_PRORATA_ANNUAL_CONTRACTS_UPLOAD_READY_"
dt = Format(CStr(Now), "dd_mm_yyyy_hh_mm")
wbTarget.SaveAs Filename:=wbNam & dt
' ================ CLOSE ================'
Application.DisplayAlerts = False
wbTarget.Close
Application.DisplayAlerts = True
End Sub
excel vba
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
Really appreciated any help on this!
I have 3 columns on 11 excel sheets that need to be copied to a unique destination sheet.
The eleven sheets refer to months, and I need to find a row referring to that month on the new worbook in order to paste, moving cells down, and then search for the next month and do the same, until all 11 months are copied.
What I have:
Sub PopulateFileTOupload()
' variables
Dim strFileToSave As String
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim rngToCopy1 As Range, rngToCopy2 As Range, rngToCopy3 As Range
Dim dt As String, wbNam As String, wbDir As String
Dim FoundCell As Range, FirstAddr As String, fnd As String, i As Long
' ================ SOURCE ================
Set wbSource = Workbooks.Open("C:UsersMLOURENCDocuments041_PRORATA_ANNUAL_CONTRACTS_UPLOAD.xls")
Set wsSource = wbSource.Worksheets("Month1")
' ================ COPY & PASTE ================
' source range1
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown))
Set rngToCopy2 = wsSource.Range("N1", wsSource.Range("N1").End(xlDown))
Set rngToCopy3 = wsSource.Range("P1", wsSource.Range("P1").End(xlDown))
Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls")
' Paste range1
' DON 't know....
' ================ SAVE ================
wbNam = "0041_PRORATA_ANNUAL_CONTRACTS_UPLOAD_READY_"
dt = Format(CStr(Now), "dd_mm_yyyy_hh_mm")
wbTarget.SaveAs Filename:=wbNam & dt
' ================ CLOSE ================'
Application.DisplayAlerts = False
wbTarget.Close
Application.DisplayAlerts = True
End Sub
excel vba
excel vba
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
asked Nov 21 '18 at 9:44
MarioLourencoMarioLourenco
82
82
wbSource.wsSource.Range("F1:F4").Copyto copy andwbDestination.wsDestination.Range("A1").Insert Shift:=xlDownto paste
– Nathan_Sav
Nov 21 '18 at 9:57
add a comment |
wbSource.wsSource.Range("F1:F4").Copyto copy andwbDestination.wsDestination.Range("A1").Insert Shift:=xlDownto paste
– Nathan_Sav
Nov 21 '18 at 9:57
wbSource.wsSource.Range("F1:F4").Copy to copy and wbDestination.wsDestination.Range("A1").Insert Shift:=xlDown to paste– Nathan_Sav
Nov 21 '18 at 9:57
wbSource.wsSource.Range("F1:F4").Copy to copy and wbDestination.wsDestination.Range("A1").Insert Shift:=xlDown to paste– Nathan_Sav
Nov 21 '18 at 9:57
add a comment |
1 Answer
1
active
oldest
votes
You need to layout a bit more information about your destination book, in order to point out what you can use to identify the row to copy your data to, however...
Lets say the data in your destination workbook is formatted something like this, all in one sheet if my understanding of your question is right:
January
Data
Data
Data
Data
February
Data
Data
Data
Data
ETC ETC
Data
Data
Data
Data
The basic steps would be:
Identify the row number where you want to add data
Dim monthRow As Long
monthRow = wbTarget.Sheets(1).Range("A:A").Find("January:", LookIn:=xlValues).Row
Check number of rows in your original data
Dim janRows As Long
janRows = rngToCopy1.rows.count
Insert that many free rows in your target book
wbTarget.Sheets(1).Rows(monthRow + 1 & ":" & monthRow + janRows).EntireRow.Insert
Transfer data across
wbTarget.Sheets(1).Range("B" & monthRow + 1 & ":B" & monthRow + janRows) = rngToCopy1
There are various ways to do this (more than one way to skin a cat), but this would be easiest i think.
I hope this helps, if not i`ll be happy to assist further.
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
Hi DarXyde, thank you so much! Looks I'm heading in the right direction. It is placing the cursor in the correct row, but not pasting data. This is now what I have:
– MarioLourenco
Nov 22 '18 at 11:24
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown)) Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls") monthRow = wbTarget.Sheets("UAN upload").Range("J:J").Find("1*MONTHANNUAL*", LookIn:=xlValues).Row month1Rows = rngToCopy1.Rows.Count wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 7).EntireRow.Insert wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 7) = rngToCopy1
– MarioLourenco
Nov 22 '18 at 11:27
rngToCopy1 could actually be all data in the source sheet...
– MarioLourenco
Nov 22 '18 at 11:28
Try adding.valueto therngToCopy1(ie:rngToCopy1.value). Also, you might need to play with the range to be inserted, i think it does insert 1 to much...wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 6).EntireRow.InsertandwbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 6) = rngToCopy1.value
– DarXyde
Nov 22 '18 at 12:27
1
Thank you so much for your precious help :)
– MarioLourenco
Nov 22 '18 at 14:17
|
show 1 more comment
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53409193%2fexcel-vba-routine-that-finds-on-one-sheet-and-paste-on-another-moving-destinat%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
You need to layout a bit more information about your destination book, in order to point out what you can use to identify the row to copy your data to, however...
Lets say the data in your destination workbook is formatted something like this, all in one sheet if my understanding of your question is right:
January
Data
Data
Data
Data
February
Data
Data
Data
Data
ETC ETC
Data
Data
Data
Data
The basic steps would be:
Identify the row number where you want to add data
Dim monthRow As Long
monthRow = wbTarget.Sheets(1).Range("A:A").Find("January:", LookIn:=xlValues).Row
Check number of rows in your original data
Dim janRows As Long
janRows = rngToCopy1.rows.count
Insert that many free rows in your target book
wbTarget.Sheets(1).Rows(monthRow + 1 & ":" & monthRow + janRows).EntireRow.Insert
Transfer data across
wbTarget.Sheets(1).Range("B" & monthRow + 1 & ":B" & monthRow + janRows) = rngToCopy1
There are various ways to do this (more than one way to skin a cat), but this would be easiest i think.
I hope this helps, if not i`ll be happy to assist further.
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
Hi DarXyde, thank you so much! Looks I'm heading in the right direction. It is placing the cursor in the correct row, but not pasting data. This is now what I have:
– MarioLourenco
Nov 22 '18 at 11:24
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown)) Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls") monthRow = wbTarget.Sheets("UAN upload").Range("J:J").Find("1*MONTHANNUAL*", LookIn:=xlValues).Row month1Rows = rngToCopy1.Rows.Count wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 7).EntireRow.Insert wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 7) = rngToCopy1
– MarioLourenco
Nov 22 '18 at 11:27
rngToCopy1 could actually be all data in the source sheet...
– MarioLourenco
Nov 22 '18 at 11:28
Try adding.valueto therngToCopy1(ie:rngToCopy1.value). Also, you might need to play with the range to be inserted, i think it does insert 1 to much...wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 6).EntireRow.InsertandwbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 6) = rngToCopy1.value
– DarXyde
Nov 22 '18 at 12:27
1
Thank you so much for your precious help :)
– MarioLourenco
Nov 22 '18 at 14:17
|
show 1 more comment
You need to layout a bit more information about your destination book, in order to point out what you can use to identify the row to copy your data to, however...
Lets say the data in your destination workbook is formatted something like this, all in one sheet if my understanding of your question is right:
January
Data
Data
Data
Data
February
Data
Data
Data
Data
ETC ETC
Data
Data
Data
Data
The basic steps would be:
Identify the row number where you want to add data
Dim monthRow As Long
monthRow = wbTarget.Sheets(1).Range("A:A").Find("January:", LookIn:=xlValues).Row
Check number of rows in your original data
Dim janRows As Long
janRows = rngToCopy1.rows.count
Insert that many free rows in your target book
wbTarget.Sheets(1).Rows(monthRow + 1 & ":" & monthRow + janRows).EntireRow.Insert
Transfer data across
wbTarget.Sheets(1).Range("B" & monthRow + 1 & ":B" & monthRow + janRows) = rngToCopy1
There are various ways to do this (more than one way to skin a cat), but this would be easiest i think.
I hope this helps, if not i`ll be happy to assist further.
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
Hi DarXyde, thank you so much! Looks I'm heading in the right direction. It is placing the cursor in the correct row, but not pasting data. This is now what I have:
– MarioLourenco
Nov 22 '18 at 11:24
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown)) Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls") monthRow = wbTarget.Sheets("UAN upload").Range("J:J").Find("1*MONTHANNUAL*", LookIn:=xlValues).Row month1Rows = rngToCopy1.Rows.Count wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 7).EntireRow.Insert wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 7) = rngToCopy1
– MarioLourenco
Nov 22 '18 at 11:27
rngToCopy1 could actually be all data in the source sheet...
– MarioLourenco
Nov 22 '18 at 11:28
Try adding.valueto therngToCopy1(ie:rngToCopy1.value). Also, you might need to play with the range to be inserted, i think it does insert 1 to much...wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 6).EntireRow.InsertandwbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 6) = rngToCopy1.value
– DarXyde
Nov 22 '18 at 12:27
1
Thank you so much for your precious help :)
– MarioLourenco
Nov 22 '18 at 14:17
|
show 1 more comment
You need to layout a bit more information about your destination book, in order to point out what you can use to identify the row to copy your data to, however...
Lets say the data in your destination workbook is formatted something like this, all in one sheet if my understanding of your question is right:
January
Data
Data
Data
Data
February
Data
Data
Data
Data
ETC ETC
Data
Data
Data
Data
The basic steps would be:
Identify the row number where you want to add data
Dim monthRow As Long
monthRow = wbTarget.Sheets(1).Range("A:A").Find("January:", LookIn:=xlValues).Row
Check number of rows in your original data
Dim janRows As Long
janRows = rngToCopy1.rows.count
Insert that many free rows in your target book
wbTarget.Sheets(1).Rows(monthRow + 1 & ":" & monthRow + janRows).EntireRow.Insert
Transfer data across
wbTarget.Sheets(1).Range("B" & monthRow + 1 & ":B" & monthRow + janRows) = rngToCopy1
There are various ways to do this (more than one way to skin a cat), but this would be easiest i think.
I hope this helps, if not i`ll be happy to assist further.
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
You need to layout a bit more information about your destination book, in order to point out what you can use to identify the row to copy your data to, however...
Lets say the data in your destination workbook is formatted something like this, all in one sheet if my understanding of your question is right:
January
Data
Data
Data
Data
February
Data
Data
Data
Data
ETC ETC
Data
Data
Data
Data
The basic steps would be:
Identify the row number where you want to add data
Dim monthRow As Long
monthRow = wbTarget.Sheets(1).Range("A:A").Find("January:", LookIn:=xlValues).Row
Check number of rows in your original data
Dim janRows As Long
janRows = rngToCopy1.rows.count
Insert that many free rows in your target book
wbTarget.Sheets(1).Rows(monthRow + 1 & ":" & monthRow + janRows).EntireRow.Insert
Transfer data across
wbTarget.Sheets(1).Range("B" & monthRow + 1 & ":B" & monthRow + janRows) = rngToCopy1
There are various ways to do this (more than one way to skin a cat), but this would be easiest i think.
I hope this helps, if not i`ll be happy to assist further.
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
edited Nov 22 '18 at 12:32
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
answered Nov 21 '18 at 12:58
DarXydeDarXyde
24026
24026
Hi DarXyde, thank you so much! Looks I'm heading in the right direction. It is placing the cursor in the correct row, but not pasting data. This is now what I have:
– MarioLourenco
Nov 22 '18 at 11:24
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown)) Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls") monthRow = wbTarget.Sheets("UAN upload").Range("J:J").Find("1*MONTHANNUAL*", LookIn:=xlValues).Row month1Rows = rngToCopy1.Rows.Count wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 7).EntireRow.Insert wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 7) = rngToCopy1
– MarioLourenco
Nov 22 '18 at 11:27
rngToCopy1 could actually be all data in the source sheet...
– MarioLourenco
Nov 22 '18 at 11:28
Try adding.valueto therngToCopy1(ie:rngToCopy1.value). Also, you might need to play with the range to be inserted, i think it does insert 1 to much...wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 6).EntireRow.InsertandwbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 6) = rngToCopy1.value
– DarXyde
Nov 22 '18 at 12:27
1
Thank you so much for your precious help :)
– MarioLourenco
Nov 22 '18 at 14:17
|
show 1 more comment
Hi DarXyde, thank you so much! Looks I'm heading in the right direction. It is placing the cursor in the correct row, but not pasting data. This is now what I have:
– MarioLourenco
Nov 22 '18 at 11:24
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown)) Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls") monthRow = wbTarget.Sheets("UAN upload").Range("J:J").Find("1*MONTHANNUAL*", LookIn:=xlValues).Row month1Rows = rngToCopy1.Rows.Count wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 7).EntireRow.Insert wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 7) = rngToCopy1
– MarioLourenco
Nov 22 '18 at 11:27
rngToCopy1 could actually be all data in the source sheet...
– MarioLourenco
Nov 22 '18 at 11:28
Try adding.valueto therngToCopy1(ie:rngToCopy1.value). Also, you might need to play with the range to be inserted, i think it does insert 1 to much...wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 6).EntireRow.InsertandwbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 6) = rngToCopy1.value
– DarXyde
Nov 22 '18 at 12:27
1
Thank you so much for your precious help :)
– MarioLourenco
Nov 22 '18 at 14:17
Hi DarXyde, thank you so much! Looks I'm heading in the right direction. It is placing the cursor in the correct row, but not pasting data. This is now what I have:
– MarioLourenco
Nov 22 '18 at 11:24
Hi DarXyde, thank you so much! Looks I'm heading in the right direction. It is placing the cursor in the correct row, but not pasting data. This is now what I have:
– MarioLourenco
Nov 22 '18 at 11:24
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown)) Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls") monthRow = wbTarget.Sheets("UAN upload").Range("J:J").Find("1*MONTHANNUAL*", LookIn:=xlValues).Row month1Rows = rngToCopy1.Rows.Count wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 7).EntireRow.Insert wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 7) = rngToCopy1
– MarioLourenco
Nov 22 '18 at 11:27
Set rngToCopy1 = wsSource.Range("E1", wsSource.Range("E1").End(xlDown)) Set wbTarget = Workbooks.Open("C:UsersMLOURENCDesktopUP_FRONT S&D041_PT2.Anual-Template041_PRORATA ANNUAL CONTRACTS_UPLOAD_TEMPLATE.xls") monthRow = wbTarget.Sheets("UAN upload").Range("J:J").Find("1*MONTHANNUAL*", LookIn:=xlValues).Row month1Rows = rngToCopy1.Rows.Count wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 7).EntireRow.Insert wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 7) = rngToCopy1
– MarioLourenco
Nov 22 '18 at 11:27
rngToCopy1 could actually be all data in the source sheet...
– MarioLourenco
Nov 22 '18 at 11:28
rngToCopy1 could actually be all data in the source sheet...
– MarioLourenco
Nov 22 '18 at 11:28
Try adding
.value to the rngToCopy1 (ie: rngToCopy1.value). Also, you might need to play with the range to be inserted, i think it does insert 1 to much... wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 6).EntireRow.Insert and wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 6) = rngToCopy1.value– DarXyde
Nov 22 '18 at 12:27
Try adding
.value to the rngToCopy1 (ie: rngToCopy1.value). Also, you might need to play with the range to be inserted, i think it does insert 1 to much... wbTarget.Sheets("UAN upload").Rows(monthRow + 7 & ":" & monthRow + month1Rows + 6).EntireRow.Insert and wbTarget.Sheets("UAN upload").Range("E" & monthRow + 7 & ":E" & monthRow + month1Rows + 6) = rngToCopy1.value– DarXyde
Nov 22 '18 at 12:27
1
1
Thank you so much for your precious help :)
– MarioLourenco
Nov 22 '18 at 14:17
Thank you so much for your precious help :)
– MarioLourenco
Nov 22 '18 at 14:17
|
show 1 more comment
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.
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%2fstackoverflow.com%2fquestions%2f53409193%2fexcel-vba-routine-that-finds-on-one-sheet-and-paste-on-another-moving-destinat%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
wbSource.wsSource.Range("F1:F4").Copyto copy andwbDestination.wsDestination.Range("A1").Insert Shift:=xlDownto paste– Nathan_Sav
Nov 21 '18 at 9:57