+ Reply to Thread
Results 1 to 6 of 6

Cut and paste cells from M1, M2 ,M3 in the empty cells in the range A8:A48

  1. #1
    Registered User
    Join Date
    11-18-2021
    Location
    Slovenia
    MS-Off Ver
    365
    Posts
    4

    Cut and paste cells from M1, M2 ,M3 in the empty cells in the range A8:A48

    In the attachments I have provided what my output is (OUTPUT.xlsx) and what I want my finished file to be (FINISHED.xlsx).
    You can Ignore everything above the 8th row except the thigns that need to be cut and paste from M1, M2, M3, M5 and M6.

    M1 Needs to be pasted in to the row after the word "Fina spodreza" is found in column B, M2 needs to be pasted in to the row before the word
    "Fina nalega", M3 needs to be pasted in the row after "Fina nalega". The words will always be in different cells though in the same column.

    M5 and M6 can always be cut and paste in the 49th and 50th row.

    Collumn A needs to be colored grey until the pasted word "FINA" (M2) after that it need to be orange.

    If it would be easier to program you can remove the empty rows.
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,820

    Re: Cut and paste cells from M1, M2 ,M3 in the empty cells in the range A8:A48

    Welcome to the Forum. There are a couple of problems with your file. First of all, you have many merged cells. You should avoid using merged cells at all cost because they almost always create problems for Excel macros. Re-design your file to remove all the merged cells and then attach the revised copy of your files. Also, you said:
    M2 needs to be pasted in to the row before the word "Fina nalega", M3 needs to be pasted in the row after "Fina nalega".
    However, your "Finished" file shows M2 before "Fina obdelava" and M3 after "Fina obdelava". Please clarify.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    11-18-2021
    Location
    Slovenia
    MS-Off Ver
    365
    Posts
    4

    Re: Cut and paste cells from M1, M2 ,M3 in the empty cells in the range A8:A48

    I have added my actual excel file with the vba code you can ignore all tabs except "Spremni list SPODAJ".
    You can press the button "Generiraj" the button makes a copy of the tab and converts its formulas in to text
    and adds blank rows after the word "Fina spodrez" and before and after the word "Fina nalega"

    I have unmergeged everything and used center along selection instead except for the cells in collumn M
    I only umerged and unformated them ( no center acros selection).

    And sorry for the mistake I've sent the wrong file it should say "Fina nalega" instead of "Fina obdelave"
    in that cell inbetwen.

    Also i noticed here that the generated file with the button press "Generiraj"
    if i save it, an unnesecary link stays in it , When I reopen it . I am using power queery, that might be causing it?
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,820

    Re: Cut and paste cells from M1, M2 ,M3 in the empty cells in the range A8:A48

    Your code is separated into several macros. I have placed all the code all in one shortened macro. Try:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-18-2021
    Location
    Slovenia
    MS-Off Ver
    365
    Posts
    4

    Re: Cut and paste cells from M1, M2 ,M3 in the empty cells in the range A8:A48

    Hello again I've improved on my design of this workbook, Though I am having difficulty running macros based on Checkboxes seems every commmand I try it doesn't work, under the worksheet
    "Spremni listi SPODAJ" or "Spremni listi ZGORAJ" I got rows which need to be inserted but not always and sometimes they have to be inserted. I'd like for the user to insert the words themselvses
    which was predifened before with "Fina nalega" and "Fina spodreza", but now I got named cells which can be easily edited. Mumps1 already helped me a lot. But I would need to figure out how checking the
    first checkbos on the right to add the left word above the found word the second check box would add the words after and the third checkbox would add the words above and bellow.

    Here is the currecnt VBA code for "Spremni listi ZGORAJ" and a new attachment was added. I would be greatful for any feedback. Also I have difficulties If I leave the named cells empty then the macro
    Adds several rows which does make sense...
    Sub ZGORAJ()
    Application.ScreenUpdating = False
    Dim lRow As Long, r As Long
    Sheets("Spremni list ZGORAJ").Copy
    With ActiveSheet
    .UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
    lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = lRow To 10 Step -1
    If .Cells(r, 2) = .Range("BESEDA_TRI_ZG") Then
    .Cells(r + 1, 2).EntireRow.Insert Shift:=xlDown
    .Cells(r, 2).EntireRow.Insert Shift:=xlDown
    .Cells(r, 1) = Sheets("Spremni list ZGORAJ").Range("PRED_BESEDO_TRI_ZG")
    .Range("A" & r).Resize(, 7).HorizontalAlignment = xlCenterAcrossSelection
    .Range("A" & r).Font.Color = vbRed
    .Range("A" & r).Font.Bold = True
    .Cells(r + 2, 1) = Sheets("Spremni list ZGORAJ").Range("ZA_BESEDO_TRI_ZG")
    .Range("A" & r + 2).Resize(, 7).HorizontalAlignment = xlCenterAcrossSelection
    .Range("A" & r + 2).Font.Color = vbRed
    .Range("A" & r + 2).Font.Bold = True
    End If
    If .Cells(r, 2) = .Range("BESEDA_DVA_ZG") Then
    .Cells(r + 1, 2).EntireRow.Insert Shift:=xlDown
    .Cells(r + 1, 1) = Sheets("Spremni list ZGORAJ").Range("ZA_BESEDO_DVA_ZG")
    .Range("A" & r + 1).Resize(, 7).HorizontalAlignment = xlCenterAcrossSelection
    .Range("A" & r + 1).Font.Color = vbRed
    .Range("A" & r + 1).Font.Bold = True
    End If
    If .Cells(r, 2) = .Range("BESEDA_ENA_ZG") Then
    .Cells(r, 2).EntireRow.Insert Shift:=xlDown
    .Cells(r, 1) = Sheets("Spremni list ZGORAJ").Range("PRED_BESEDO_ENA_ZG")
    .Range("A" & r).Resize(, 7).HorizontalAlignment = xlCenterAcrossSelection
    End If
    Next r
    End With
    Range("D7,D6,D5,A10:A49").Select
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.Replace What:="/", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.Replace What:="spod..", Replacement:="spod.,", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Application.Goto Reference:="ZG_NOGA"
    Selection.Cut
    Range("A50").Select
    ActiveSheet.Paste
    ActiveSheet.Shapes.Range(Array("Button 2")).Select
    Selection.Delete
    End Sub
    Attached Files Attached Files

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,820

    Re: Cut and paste cells from M1, M2 ,M3 in the empty cells in the range A8:A48

    I would suggest that you start a new thread with a link to this thread. This will very likely result in getting a possible solution more quickly.

+ 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. Copy and paste to destination range of cells only if empty
    By RonnG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2020, 10:35 AM
  2. [SOLVED] Copy and Paste only filled cells from a dynamic range to another sheet in empty cells
    By seanpcorbett1 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-12-2016, 03:23 PM
  3. [SOLVED] Can't paste a range of cells into only empty cells of another column
    By mstew in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-30-2014, 03:51 PM
  4. [SOLVED] Copy/paste range of cells to first empty row of active list in another worksheet
    By wildecat666 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-06-2013, 03:45 PM
  5. how to copy a range of cells, paste them and then find next empty cell
    By comp23 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2013, 03:34 PM
  6. [SOLVED] Copy and Paste Cells to Next empty cell in range
    By oddinho2 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-20-2013, 12:51 PM
  7. looking for an empty row to paste a range of copied cells
    By jayers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2009, 10:12 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