Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Moving data from sheet1 to sheet2

Posted on 2000-04-19
7
Medium Priority
?
937 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 400 total points
ID: 2732913
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
ID: 2733060
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
ID: 2733943
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 4

Author Comment

by:Okki
ID: 2734454
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
ID: 2734535
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
ID: 2734587
Okki
You made a good decision. Calacuccia did well.

antrat
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2734623
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

927 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