Okki
asked on
Moving data from sheet1 to sheet2
I have a range of cells (AB1:AB22) on sheet1 that I would like to copy&paste into sheet2.
On sheet2 the target range is on the same row but from Column (D:Z).
Which row on sheet2 the result should end up in depends on the value of "FNR" which is a named cell on sheet1.
The value of "FNR" should be found in column A of sheet2 and the data pasted inte column D of that same row.
I have made macro's doing just this before in Excel4 macro language and I think I would manage to "translate" them into VBA myself but I really don't want to use the Copy&Paste stuff now that I'm getting used to just use "Range(x) = Range(Y).value" instead of "Copy Range(x) then select Range(Y) and paste.
I have named my range (AB1:AB22) "Export" and would like to copy that range (transposed) into column D but not just in any row.
How would I write the code to first determine the right row and then export the range ?
I want this procedure to be done just by pressing a button without me leaving sheet1. It's actually an update of an Excel database we are talking about here. Sheet1 is the form.
What should happen if the code can't find the value of "FNR" in column A of sheet2 ?
Well, I think the best solution would be that it added the missing value to the bottom of column A of sheet2 (as a new post) and continued from there.
I'm sure someone out there can save me a couple of hours of trial & error.
On sheet2 the target range is on the same row but from Column (D:Z).
Which row on sheet2 the result should end up in depends on the value of "FNR" which is a named cell on sheet1.
The value of "FNR" should be found in column A of sheet2 and the data pasted inte column D of that same row.
I have made macro's doing just this before in Excel4 macro language and I think I would manage to "translate" them into VBA myself but I really don't want to use the Copy&Paste stuff now that I'm getting used to just use "Range(x) = Range(Y).value" instead of "Copy Range(x) then select Range(Y) and paste.
I have named my range (AB1:AB22) "Export" and would like to copy that range (transposed) into column D but not just in any row.
How would I write the code to first determine the right row and then export the range ?
I want this procedure to be done just by pressing a button without me leaving sheet1. It's actually an update of an Excel database we are talking about here. Sheet1 is the form.
What should happen if the code can't find the value of "FNR" in column A of sheet2 ?
Well, I think the best solution would be that it added the missing value to the bottom of column A of sheet2 (as a new post) and continued from there.
I'm sure someone out there can save me a couple of hours of trial & error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Okki,
Here is a non-copy-and-paste solution.
Sub WriteTranposedData()
Dim FnrRow As Variant
Dim Arr As Variant
'Try finding the row. Quit if no match was found.
On Error Resume Next
FnrRow = WorksheetFunction.Match(Ra
On Error GoTo 0
If IsEmpty(FnrRow) Then Exit Sub
'Read the transposed range into arr and write it
'to the destination range
Arr = WorksheetFunction.Transpos
Sheets("Sheet2").Cells(Fnr
End Sub
Ture Magnusson
Karlstad, Sweden
Here is a non-copy-and-paste solution.
Sub WriteTranposedData()
Dim FnrRow As Variant
Dim Arr As Variant
'Try finding the row. Quit if no match was found.
On Error Resume Next
FnrRow = WorksheetFunction.Match(Ra
On Error GoTo 0
If IsEmpty(FnrRow) Then Exit Sub
'Read the transposed range into arr and write it
'to the destination range
Arr = WorksheetFunction.Transpos
Sheets("Sheet2").Cells(Fnr
End Sub
Ture Magnusson
Karlstad, Sweden
ASKER
I'm sorry "antrat" but Calacuccia's example worked so nice I almost got tears in my eyes and he did not use the "Copy & Paste" method like I asked for (if possible).
Antrat: Your solution stoped at "i = Range("FNR").Value" and the reason for this might be because the range "FNR" is a merged cell. Did not investigate further though but I still appreciate your efforts.
Calacuccia: Thanks, again! I noticed I had forgotten one cell and the Export range should have been (AB1:AB23) and I'm glad you brought that to my attention.
Antrat: Your solution stoped at "i = Range("FNR").Value" and the reason for this might be because the range "FNR" is a merged cell. Did not investigate further though but I still appreciate your efforts.
Calacuccia: Thanks, again! I noticed I had forgotten one cell and the Export range should have been (AB1:AB23) and I'm glad you brought that to my attention.
ASKER
Sorry Ture, I saw the other answers long before yours and started answering them directly but noticed much later that I hadn't posted my acception.
Calacuccia's answer did all that I asked for so I hope you don't feel sad that he received the points.
I must confess however that I will stick to your method since it (for reasons unknown to me) is really a lot faster on my old Pentium II.
Despite this I will let Calacuccia keep his well earned reward.
Have a nice weekend !
Johan
Sweden
Calacuccia's answer did all that I asked for so I hope you don't feel sad that he received the points.
I must confess however that I will stick to your method since it (for reasons unknown to me) is really a lot faster on my old Pentium II.
Despite this I will let Calacuccia keep his well earned reward.
Have a nice weekend !
Johan
Sweden
Okki
You made a good decision. Calacuccia did well.
antrat
You made a good decision. Calacuccia did well.
antrat
Thanks Okki (& Antrat).
Ture's solution is probably quicker because
1) He uses the WroksheetFunction.Match which could perform a lot better than my Find method
2) The arr = Transpose(Range("Export") and next line, could be a lot quicker than my method looping through each of the 23 cells, although I'm not sure about this.
However, if using Ture's solution, he did not include the case of Range("FNR") not Found (in fact his sub exits in that case). If you need to add data to the last line in that case, I think you'd better use the first part of my macro with the second part of Ture's (if point 2 here above applies in terms of speed).
Cheers & Thanks
Calacuccia
Ture's solution is probably quicker because
1) He uses the WroksheetFunction.Match which could perform a lot better than my Find method
2) The arr = Transpose(Range("Export") and next line, could be a lot quicker than my method looping through each of the 23 cells, although I'm not sure about this.
However, if using Ture's solution, he did not include the case of Range("FNR") not Found (in fact his sub exits in that case). If you need to add data to the last line in that case, I think you'd better use the first part of my macro with the second part of Ture's (if point 2 here above applies in terms of speed).
Cheers & Thanks
Calacuccia
I'm not sure if your named range "FNR" is a single cell or a range of cells. I have assumed it is a single cell in this example. It will transpose your data to column D on sheet2 starting from row "i" which is set by the value in "FNR". You can run it from any sheet. Let me know how it goes
Sub Transpose()
Dim i As Integer
i = Range("FNR").Value
Range("export").Copy
If IsEmpty(i) Then Exit Sub
On Error GoTo Errmsg
Sheets("sheet2").Cells(i, 4).PasteSpecial Transpose:=True
Application.CutCopyMode = False
Exit Sub
Errmsg:
MsgBox "Data won't fit"
Application.CutCopyMode = False
End Sub
good luck
antrat