Moving data betwen worksheets

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi All,


    I'm very new to using VBA within Excel (have only done a bit of VB5 a couple of years ago), but have a project I'm working on that requires me to use some VBA.


    The basics of what I want to do are as follows:
    - I have a worksheet with multiple rows of data that I want to process and insert into spreadsheet 1 of a new workbook.
    - The processing (much of which seems to be working OK already individually) involves pulling out certain text and numbers from a text string in a cell, checking for < signs for values in other cells and assigning true/false, etc.
    - The source spreadsheet has 14 columns while the target spreadsheet has to have 26 columns populated from data in the source spreadsheet (for instance, there are 9 columns that I have to determine whether the < sign exists and assign true/false [goes into 1 column as true or false] and then I insert the number value without the < sign into a second column)
    - So, if the source spreadsheet has 15 rows, I need to loop through the cells in each row, perform actions on each cell, then add to the new spreadsheet, until each row has been processed and I end up with 15 rows of updated values in the new spreadsheet. I know which column each piece of data needs to be inserted into, but the row number will change.


    So, some of the things I'm not sure how to do are:
    1. how to reference the worksheets in different workbooks, so that the correct source data are used and the target destination is correct. To ensure that the correct worksheet is used, it also might be useful to use a function that returns the workbook and spreadsheet name, that can be referenced throughout the rest of the procedures.
    2. I can't use specific references to individual cells, but need to loop through each cell in each row (or should I do it column by column?)
    3. For the used cell range I am currently using a function that returns the number of rows and columns in the source spreadsheet, which I want to then use to ensure that the correct number of rows and colums are referenced from the source spreadsheet, and inserted into the target spreadsheet.


    I am currently using Excel 2002, but the code needs to be compatible with Excel 2000, and possibly Excel 97 also. From what I've read, most VBA code written in 2002 is compatable with version 2000, and much will also work in 97.


    Anyway, if anyone has any pointers on some or any of the above, that would be great.


    Thanks :D

    PCFISH
    :fish:

    Please take a look at
    http://ozgrid.com/forum/viewthread.php?tid=1361


    Which deals with similar issue.


    Also, it was nice of you to give so much information, but it is a bit confusion.


    Is if feasible for you to provide a sample sheet (both input and output??) so that you will need to make minimum changes to the suggestion that we will give.


    In the mean while, i am looking into some generic solution as well.
    :cheers:

    Thanks: ~Yogendra

    Thanks Yogendra


    I've attached examples of the source and target spreadsheets (I have put them both in the same workbook for now. but I actually want them to be in separate workbooks). You'll note that not all data in the source spreadsheet goes into the target and also that there are some blank columns in the source, which are ignored.


    I intend to populate the first target column with a value entered by the user (preferably from a list box with year values 2002 to 2010 for eg), and the second column will probably just need to be populated automatically, as this will always be the same text. Columns E & H will also have to be populated manually by the user (probably using an input box) entering a valid date on both occasions - 1 value for all cells in each column.


    Hopefully the spreadsheets and additional information are enough to understand what I'm trying to achieve.


    I will also have a look at the other post you mentioned.


    Thanks,


    Ian :tumble:

    PCFISH
    :fish:

    I can assure that you made me work hard.
    But no problem. It was interesting.


    All the work is completed except:
    1. Inputbox: for dates ( Surely this is feasible, but is it really required?? Cant it be included in the source itself? If yes, no probs. I will do it for you, or you can also amend the code
    2. Different workbooks: First see if the things you want are satisfied, doing this is not a big deal.
    3. False / True in the target Sheet: not filled as do not know the criterias.


    this is the code, also the file attached:


    ****************************************************************************
    Private Sub Worksheet_Change(ByVal Target As Range)
    'This code will check the value in Post? Column, based on Yes
    'will paste the data in the sheet
    Dim sht As String
    Dim s As Worksheet
    Dim date_Dec As Boolean
    date_Dec = True



    '========= Part I : checking range and data entered =========
    'This will check if the value changed is in D column
    If Target.Count <> 1 Then Exit Sub


    If Intersect(Target, Range(ActiveWorkbook.Names("Posting"))) Is Nothing _
    And Target.Value <> "Yes" Or Target.Value = "" Then Exit Sub


    ''========= Part III : Transfer of data to appropriate places =========
    Application.ScreenUpdating = False


    'this selects the next available row
    With Sheets("Target")
    .Activate
    .Range("A65536").End(xlUp).Offset(1, 0).Select
    End With


    With Sheets("source").Range(Target.Address)
    Selection.Value = Year(Now())
    Selection.Offset(0, 1).Value = "AGAL"
    Selection.Offset(0, 2).Value = .Offset(0, -13).Value
    Selection.Offset(0, 3).Value = .Offset(0, -12).Value
    Selection.Offset(0, 5).Value = Mid(Trim(.Offset(0, -11).Value), 17, 3)
    Selection.Offset(0, 6).Value = Right(.Offset(0, -11).Value, 1)
    Selection.Offset(0, 9).Value = Replace(.Offset(0, -9).Value, "<", "")
    Selection.Offset(0, 11).Value = Replace(.Offset(0, -8).Value, "<", "")
    Selection.Offset(0, 13).Value = Replace(.Offset(0, -7).Value, "<", "")
    Selection.Offset(0, 15).Value = Replace(.Offset(0, -6).Value, "<", "")
    Selection.Offset(0, 17).Value = Replace(.Offset(0, -5).Value, "<", "")
    Selection.Offset(0, 19).Value = Replace(.Offset(0, -4).Value, "<", "")
    Selection.Offset(0, 21).Value = Replace(.Offset(0, -3).Value, "<", "")
    Selection.Offset(0, 23).Value = Replace(.Offset(0, -2).Value, "<", "")
    Selection.Offset(0, 25).Value = Replace(.Offset(0, -1).Value, "<", "")
    End With
    With Sheets("source")
    .Activate
    .Range(Target.Address).Select
    .Range(Target.Address).Offset(0, 1).Value = Now()
    End With
    Application.ScreenUpdating = True


    End Sub


    ****************************************************************************


    Best of Luck......

    Thanks: ~Yogendra

    :(Wow! This is a work of art. It seems to be closer to what I need (thread 1361). Of course, It's beyond my grasp how to apply it., but I'm trying. I printed everything and am attempting to revise my own workbook.


    I admire your gift.


    Laura

    Yogendra,


    Many thanks for your suggested solution :thumbcoo:. It looks really great, and is very compact compared to what I have been trying so far. Unfortunately though, I have been unable to get it to work. :( When I try and run the code from within the VBA code window, I just get the pop-up Macro box, with no Macros recognised for the workbook. It will try to run without the (ByVal Target As Range) declaration in the sub, but there is obviously an immediate error generated. Am I missing something here?


    There are probably a couple of other things I should mention that may influence the approach to take.


    1) The aim of this code is simplify the formatting of data, by allowing users to click a single button on the toolbar that will result in all required formatting being carried out, without them having to do anything, apart from maybe fill in a couple of input boxes, and/or possibly make a selection from a list box. The procedure needs to be as simple as possible for the users. Once the whole process is working, I want to save it as a stand-alone Add-in that can be installed on their copy of Excel.


    2) The source spreadsheet is generated by another organisation and is sent to the user organisation directly, so the code needs to work with the data contained within the spreadsheet, exactly as it appears - therefore the addition of the extra columns (Post? and Posted?) may not be possible, unless that is automated too.


    3) I want the code to be run with the source workbook open, and the reformatted data put into a brand new workbook on sheet 1.


    If I can get Yogendra's code working that would be really great, but if not, I might have to post what I've done so far and see if my approach can be modified.


    Any help greatly appreciated.


    Cheers,


    Ian :)

    PCFISH
    :fish:

    Hi All.


    Looks like I've drawn a blank for the time-being on the problem noted in the last post :(, so will also put up my own version to see if that can be made to work OK.


    The issues are still the same as noted in the earlier posts.


    I have got most of the data re-formatting requirements sorted out, but as I'm not sure how to insert the re-formatted data into a new workbook, I just have it going into a row lower in the source spreadsheet so I could test what I was doing. So, problem 1 is how to get the new data inserted into a brand new workbook.


    2. I'm getting the number of rows and columns in the source sheet returned, but am not clear how to convert this to a valid range to allow the code to loop through each column & row of data.


    3. I also need a bit of help about how to reference the ranges and to get a loop working successfully. Currently I have the cell ranges hard coded in, which I obviously don't want.


    4. There are probably lots of other better ways to achieve what I'm after, but without the experience, this is the best I can manage at the moment.


    If anyone has any suggestions on getting my code up and running, or how to solve the problem I seem to be having with Yogendra's (much classier) version, your assistance will be greatly appreciated.


    I'll be logging off very shortly, but will be back to check tomorrow morning.


    Cheers,


    Ian. :cool:

    PCFISH
    :fish:

    I have just returned from vacation,
    Give me some time (3-4 Days).
    In the mean while you can mail me if you want anything urgent.


    Regards

    Thanks: ~Yogendra

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login