Solved

Moving data from sheet1 to sheet2

Posted on 2000-04-19
7
880 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:Okki
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 100 total points
Comment Utility
Hi Okki,

The following macro should do what you ask for. It uses your named ranges "FNR" and "Export".

Only thing is, your columns will be D to Y (not Z) or E to Z (not D), because of only 22 cells.

The macro is written for D to Y, if E to Z is what you want, change the 3 to 4 in the line
Sheets("Sheet2").Cells(Line, i + 3) = Sheets("Sheet1").Cells(i, 28)

If no matching value for FNR is found in column A of sheet2; the record is added at the end of the list, as you asked.

Sub DataTransfer()
Dim Where As Range, Line As Long
Set Where = Sheets("Sheet2").Columns(1).Find(What:=Range("FNR").Value, Lookat:=xlWhole)
If Not Where Is Nothing Then
Line = Where.Row
Else
Line = Sheets("Sheet2").Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row + 1
End If
For i = 1 To 22
Sheets("Sheet2").Cells(Line, i + 3) = Sheets("Sheet1").Cells(i, 28)
Next i
End Sub

Good Luck
Calacuccia
0
 
LVL 9

Expert Comment

by:antrat
Comment Utility
Hi okki

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

0
 
LVL 22

Expert Comment

by:ture
Comment Utility
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(Range("FNR"), Sheets("Sheet2").Range("A:A"), 0)
  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.Transpose(Range("Export"))
  Sheets("Sheet2").Cells(FnrRow, 4).Resize(1, 22) = Arr
End Sub

Ture Magnusson
Karlstad, Sweden
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 4

Author Comment

by:Okki
Comment Utility
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.

0
 
LVL 4

Author Comment

by:Okki
Comment Utility
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
0
 
LVL 9

Expert Comment

by:antrat
Comment Utility
Okki
You made a good decision. Calacuccia did well.

antrat
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
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
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now