• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste data into specified collumns

MalR

Member
Hi guys. Can you help me with a macro that will place data into specified columns so I can import it into a card file in MYOB.
Basically I paste a row of data into a worksheet but I need the macro to take this data and line it up in with certain columns as shown.
I enclose a file to demonstrate.
The data will always look like this but sometimes some of the cells may be blank eg we may only get one phone number instead of both or there may be no email. So if one of the cells is missing the macro should leave it blank.
I do not need the macro to import. I only need it to paste into the specified columns.
Thanks for your help
Mal
 

Attachments

  • Import template.xlsx
    10.3 KB · Views: 11
IMHO you do not need a macro. A formula would do it.
-Create a Sheet1 with the yellow headers and paste the raw values there
-Create a Sheet2 with ALL the headers and use a index/match or a offset/match combo to fetch the data
i.e.
=IFERROR(OFFSET(Sheet1!$A$1;ROW();MATCH(Sheet2!A$1;Sheet1!$A$1:$J$1;0);1;1);"")

and copy down/across
 
Hi, MalR!
In case that iferror's solution isn't suitable for you if you require VBA code, please upload a new sample file with the input & output data layouts taking care that line numbers match with your description.
Regards!
 
IMHO you do not need a macro. A formula would do it.
-Create a Sheet1 with the yellow headers and paste the raw values there
-Create a Sheet2 with ALL the headers and use a index/match or a offset/match combo to fetch the data
i.e.
=IFERROR(OFFSET(Sheet1!$A$1;ROW();MATCH(Sheet2!A$1;Sheet1!$A$1:$J$1;0);1;1);"")

and copy down/across

Thanks a lot iferror I am having a look at it. Haven't quite got it to work yet.
Sir JB7 thanks also for your offer. You are always quick to respond.
Guys, I am trialling the suggestion of iferror and will post a reply at a later date.
I appreciate your help
Regards
Mal
 
Thanks again for your help and quick responses guys.
I could not get the Iferror formula to work. Something wrong with the deliminators. I played around with it but finalized on a very simple solution to get the cells to line up in the columns that I require.
I paste/transpose the data into my template. My template picks up the data and pastes it automatically into each column which is decided by cell equals cell formulas. I then copy the aligned data and paste/values into the row I want and delete all rows below it. This enables me to save the file as a Text tab deliminated file and do the import into MYOB.
It works very quickly and is set up to be able to process several cards at once, meaning I can import into MYOB numerous name/address details and they go into separate cards in MYOB.
Thanks again guys. I appreciate your quick responses. This thread can be closed now.
Regards
Mal
 
Ok, i see what i did wrong :)
there is an absolute reference that needs to be turned to relative :) and a little adjustment on the offset

=IFERROR(OFFSET(Sheet1!$A$1;ROW()-1;MATCH(Sheet2!A$1;Sheet1!A$1:J$1;0)-1;1;1);"")
 
Hi Mal

As an alternative you could put the data in a template (different sheet) and run the following. Simple copy and paste then. I realise you said this is what you are doing but this will give you a look at the same thing probably done a bit differently.

Code:
Option Explicit

Sub MoveData()
Dim ar As Variant
Dim i As Integer

ar = [{"C1:D1","F1:H1","L1","O1:P1","R1"}]

    For i = 1 To UBound(ar)
        Range(ar(i)).EntireColumn.Insert
    Next i
End Sub

Take care

Smallman
 
Appreciate your followup Iferror and I will give your macro a shot Smallman. I enjoy looking at new options that could speed me up. That's why this site is so interesting. You think you have a solution and someone comes up with a simpler method! I will get back to you but it will be later on when I get time to work on it again
Thanks for the suggestion
Mal
 
Back
Top