+ Reply to Thread
Results 1 to 37 of 37

Paste in next row/ column if

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Paste in next row/ column if

    I have a worksheet that I need to copy the values from sheet 2 columns B & C into sheet 1 columns B & C to the next available row. The issue is that the data is not always available in sheet 2, to determine this sheet 2 has an I in column A to show the data is associated with this and I need to insert a 0 in columns B & C if data is unavailable. Any help would be most appreciated.

  2. #2
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Could you please post the sample file........ I will provide you the VBA code......

  3. #3
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    I have attached an example file
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Any help would be appreciated on this

  5. #5
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Hey
    I am attaching the same sheet with the code.. there is some data already added in column b sheet 1 so when I tried to find out the last row it goes to row 33 or some other row not to the row 7. In attached sheet I remove that....try following code.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by LokeshKumar; 04-03-2015 at 02:59 PM.

  6. #6
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    LokeshKumar , Thanks for your help so far however I need the data in those rows as they show non working days, is it possible to skip a row if the B column is populated? Also I have found that the code only works if an "R" is located in the sheet2 column A rather than an I?

  7. #7
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Can someone take a look at this for me please?

  8. #8
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Try this if it works........

    Please Login or Register  to view this content.
    Lokesh Kumar
    Stay Hungry.. Stay Foolish..
    _________________________________________________________
    Please Click STAR to Add Reputation if my/someone's answer helped!

  9. #9
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Lokesh Kumar,
    I have tried the new code but it doesn't seam to do anything? Also how does it calculate if the data in column A in sheet 2 has an I ? I have just found an error in my sample in that column A has an R in both A1 & A2 whereas one of these should be an I.
    Thanks
    Johnny

  10. #10
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Ok I guess the sheets work fine but there is something missing in my understanding....
    I just want to know what is the use of sheet 2 and how do you know which row has data and when you insert 0 (when do you know data is unavailable) all the things about sheet 2.
    Pls explain a more...I just read your first post and then created in a hustle......
    dont worry I will do....

  11. #11
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Sorry maybe I didn't explain clearly enough, The 0 is in certain cells as it shows non-working days and I want the data input to skip these rows. I have attached a slightly better workbook as an example for your reference, All I really need it to do is move the data which has an I in column A sheet 2 into the next available cells B & C in sheet 1. Hope this clarifies everything ok.
    Attached Files Attached Files

  12. #12
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Hey
    I tried to understand your issue and just did some modification in the code.......

    Please Login or Register  to view this content.
    Here is attached sheet with the code.......

    sample (1).xlsm
    Attached Files Attached Files
    Last edited by LokeshKumar; 04-10-2015 at 04:44 AM.

  13. #13
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    It copies and pastes fine now however is still overwriting the cells which I have put a 0 into and wish to skip???

  14. #14
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    I have checked that if Sheet 1 and column b has non blank cell then it will go to the next row.... but it should not be blank or you can put 0 into it..

    just do one thing.....run the code at your end.....and highlight the cell where you get the issue and insert comment explicitly.....
    Last edited by LokeshKumar; 04-10-2015 at 12:59 PM.

  15. #15
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    OK, I have gotten to the bottom of the issue, When the macro is run it is starting at cell B7 and overwriting all data thereafter instead of looking for the next available cell in that row??

  16. #16
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Hey
    Can you just attached the sheet after running.... However I will try to run at my end as well...

  17. #17
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Do me a favor, can you run the code and save that file (which has incorrect syntax as per you) and attach it here...that would clarify everything for me...

  18. #18
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    I have attached a file that I have added additional data in and run 4 times, the data was pasted into the cells not for the first 2 times and after that it runs but doesn't execute any code.

    I used the code;

    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    I have made some changes in the code:
    Please try this:-
    Please Login or Register  to view this content.
    Here is your attachment with the code...

    sample (3) (1).xlsm

  20. #20
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    This works fine but doesnt really work for what I need it for as it depends on the user entering the correct date which is error prone, I have used the code,
    Please Login or Register  to view this content.
    to get it to select the correct next blank row but cannot get it to copy the values based on if I is present in column A of sheet 2, could this be modified in any way?
    Thanks
    Johnny

  21. #21
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    ok...
    I am not running the entire code but for your concern here is the code
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    LokeshKumar,
    I am totally confused now, When I enter the code it is giving the error as per pic attached?
    Untitled.png

  23. #23
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Here columns means in which column you want to run the code like:

    column "A" =1
    column "B" =2
    column "C"= 3
    Last edited by LokeshKumar; 04-15-2015 at 06:06 AM.

  24. #24
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    So why is it giving an error?

  25. #25
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    try this:
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    LokeshKumar ,
    Sorry to be such a pain but the code still isnt working it is now skipping row 16 and keeps inputting in over contents in row 17 instead of skipping it and 18?
    The code now looks like;
    [CODE]Option Explicit
    Sub Burt_100()



    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim i As Integer
    Dim Col_B As String
    Dim Col_C As String
    Dim column As String
    Dim Lastrow As Integer
    Dim value As Integer


    Application.ScreenUpdating = False

    Set Sh1 = ThisWorkbook.Sheets(1)
    Set Sh2 = ThisWorkbook.Sheets(2)

    Sh1.Activate

    Lastrow = Range("B6").End(xlDown)(2, 1).Select


    Sh2.Activate
    For i = 1 To Range("A:A").End(xlDown).Offset(1, 0).Row

    If Trim(Cells(i, 1)) = "I" Then

    Col_B = Cells(i, 2)
    Col_C = Cells(i, 3)

    Sh1.Activate

    Cells(Lastrow) = Col_B
    Cells(Lastrow) = Col_C

    If Cells(Lastrow + 1, 2) = "" Then
    Lastrow = Lastrow + 1
    Else
    Lastrow = Lastrow + 2
    End If


    Col_B = vbNullString
    Col_C = vbNullString

    End If

    Next i

    Sh1.Activate
    Application.ScreenUpdating = True

    End Sub


    [CODE]
    Attached Files Attached Files

  27. #27
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Paste in next row/ column if

    i had a few errors with that code but some pretty simple tweaks and it looks like it's working for me:

    Please Login or Register  to view this content.
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito

  28. #28
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Works perfect thanks so much just another quick question is it possible to add a 0 in cells b& c within range if no data is available?

  29. #29
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Paste in next row/ column if

    what do you mean "if no data is available..?"

    Will there be instances in which this code is run but there are no I's in Sh2? or just for each row that has something other than an I?

    i just read through this whole thread hoping to find some clarity there - no such luck... but i did notice your request indicates the days you're trying to skip are non-working days. one word of caution - this code will only skip one non-working day... it will not look for the next blank value, so if you have consecutive non-working days you will end up overwriting the second (and every even numbered non-working day after that until you get to blanks again)

    that is very fixable if you need it, just let us know.
    Last edited by simarui; 04-15-2015 at 02:44 PM. Reason: adtl information after reading whole thread

  30. #30
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Yes your correct, i need to skip consecutive days i.e Saturadays & Sundays, holidays etc., so it may be up to 5 days, secondly u r correct regarding the paste i just need the code to put a 0 in both cells b,c in range in sheet1 when no information is available in sheet 2, i.e I is not evident in column a, is this somtjing you can help me with?

  31. #31
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Paste in next row/ column if

    Hey guys,

    For me this thread was solved when I replied first as this is something very basic I and I understand the meaning....
    "If no data is available"
    .......But here in this thread I do not understand.....
    @simarui can you see my code.....I know it is not perfect or professional but it works as per query raised....
    The best thing if you could save and attached the file after running the macro.......

  32. #32
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Lokesh Kumar,
    Sorry for the additional query I just realised that I required it, Would it be possible to modify the code that it inserts a 0 in the cells if I is not found in column A sheet 2?

  33. #33
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Paste in next row/ column if

    @Lokesh Burt PM'd me as i'd answered a similar question on another thread. i didn't really look closely at your code, just took the code from his last reply and fixed it. i'm inclined to believe you though if you say it works, just trying to help out with his next request.

    @Burt how many rows would get a 0 if no information is available?

    try this assuming the answer is 1:
    Please Login or Register  to view this content.
    seems like it should work (untested)
    Last edited by simarui; 04-16-2015 at 01:58 PM.

  34. #34
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    simarui ,
    Works but pastes it into the range for sheet 1 but in sheet2 for some reason?

  35. #35
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Paste in next row/ column if

    sorry 'bout that:

    Please Login or Register  to view this content.

  36. #36
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    It is starting at cell B12 and not moving on now after each new data input for some reason

  37. #37
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Paste in next row/ column if

    Can anyone help with this? I would really appreciate it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 08-01-2014, 04:30 PM
  2. Replies: 3
    Last Post: 05-22-2013, 01:08 AM
  3. Vba - find next empty column, insert column, copy paste values & copy paste formulas
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 12:43 PM
  4. -- Selective Cut and Paste -- Cut and Paste based off of B Column's Content --
    By brad70989 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-09-2011, 03:29 PM
  5. Replies: 4
    Last Post: 08-31-2010, 10:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1