copy cells automatically to next blank cells in another sheet

Re: copy cells automatically to next blank cells in another sheet

I was with ya until this:

Quote

and continue into next empty cell until each cell down to W38 is filled

What does that mean? It's a fairly simple thing to copy an entire column to the next available column on another sheet....or to copy rows 19 through 138 into the next available column on another sheet..........but ya kinda lost me with the C19 through W38 thing.

Re: copy cells automatically to next blank cells in another sheet

I'm sorry...maybe I'm dense, but I'm still not following. You have 120 cells in sheet 1 and you want to copy that into 21 cells (C19:W19) in sheet 2? Or, you want to copy the 120 cells in sheet 1 to 420 cells in sheet 2, which will leave empty cells, needless to say....?

I looked at your example, but sheet 2 is blank. Maybe if you put the desired results into sheet 2, even if it's manually this time.

Re: copy cells automatically to next blank cells in another sheet

Slohman

Welcome to Ozgrid!!! When posting a workbook please give an example of what your data set looks like and what your desired result should look like after any procedure. This stops confustion on the forum. What you have said to date is very difficult for anyone to follow. Look forward to seeing your update. :wink:

Take care

Smallman

Re: copy cells automatically to next blank cells in another sheet

forum.ozgrid.com/index.php?attachment/44909/

Ok, try this out. It's made to stop if it runs out of room and warn you that all data was not copied.

Sub MoveSh1_to_Sh2()
Dim i As Long
Dim LR As Long
Dim MyCol As Integer
Dim MyRow As Integer
 
LR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
MyCol = 3
MyRow = 19
    For i = 1 To LR
        If Sheets("Sheet1").Range("A" & i).Value <> "" Then
            Sheets("Sheet2").cells(MyRow, MyCol).Value = Sheets("Sheet1").Range("A" & i).Value
            MyCol = MyCol + 5
                If MyCol = 23 Then
                    MyCol = 3
                    MyRow = MyRow + 1
                End If
            If MyRow = 39 Then
                MsgBox "You have ran out of room.  Some entries were not copied"
                Exit For
            End If
        End If
    Next i
End Sub

Re: copy cells automatically to next blank cells in another sheet

Hi Slohman

Consider using Centre Across Selection instead of merged cells. Same look and feel without the loss of fidelity. Here is my take on the problem.

Option Explicit
Sub PopTemp()
    Dim arr As Variant
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    j = 3
    k = 19


    Range("A1:A100").AutoFilter 1, "<>"
    Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy Range("B1")
    Range("A1:A100").AutoFilter
    arr = Range("B1", Range("B" & Rows.Count).End(xlUp))
    
    For i = LBound(arr) To UBound(arr)
        Sheet2.Cells(k, j).Value = arr(i, 1)
            j = j + 5
                If j = 23 Then
                    j = 3
                    k = k + 1
                        If k > 39 Then
                            MsgBox "Oh No!"
                            Exit Sub
                        End If
                End If
    Next i
Columns(2).ClearContents 'Del helper Col
End Sub

JProffer - Most of the users of forums are not members, as such posting the code helps people into perpetuity. Please post your coding with your workbook.:smile:

Take care

Smallman

Re: copy cells automatically to next blank cells in another sheet

I didnt realize that a copy event on filtered data would return unique data! Thats an awsome tid bit. Thanks Smallman

Re: copy cells automatically to next blank cells in another sheet

Hi Again Bryce

Thanks for the compliment. It is yours now to run with. :spin:

Take it easy

Smallman

Re: copy cells automatically to next blank cells in another sheet

Hi Slohman

Ironically Bryce was the only one involved in this thread who did not provide you with any coding. Once again your description could use a bit of work. Where are the Dollar values in the Estimate tab? One would make the bold assumption there are Dollars attached as the sheet titled SheetD has $ signs for what looks like inputs should be going but no corresponding values on the Estimate sheet. Can you clarify?

Take care

Smallman

Re: copy cells automatically to next blank cells in another sheet

Hi Slohman

Assuming you want to just populate the data with your unique identifyers from your Estimate Tab then the slight modification to the original coding is all that should be required. File attached to show workings.

It is not very difficult to modify this code if you take a small amount of time to get your head round it you will be right across it in no time.

Take care

Smallman

Sub PopTemp2()
    Dim arr As Variant
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    j = 3
    k = 24
    Range("A1:A100").AutoFilter 1, "<>"
    Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy Range("B1")
    Range("A1:A100").AutoFilter
    arr = Range("B1", Range("B" & Rows.Count).End(xlUp))
    
    For i = LBound(arr) To UBound(arr)
        Sheet4.Cells(k, j).Value = arr(i, 1)
            j = j + 6
                If j = 15 Then
                    j = 3
                    k = k + 1
                        If k > 44 Then
                            MsgBox "Oh No!"
                                 Exit Sub
                        End If
                End If
    Next i
Columns(2).ClearContents 'Del helper Col
End Sub

Re: copy cells automatically to next blank cells in another sheet

Hi Slohman

The value you spoke of is what I was alluding to 2 posts ago. Your SHeetD tab was ambiguous as your estimates sheet had unique identifyers but no values attached. I can happily had random values if you like. But I think it would be better for you to post a workbook with the data as you are expecting to see it.

Take care

Smallman

Re: copy cells automatically to next blank cells in another sheet

Hi Slohman

Please find the attached with data flowing through relating to your UIs. I decided the easiest way to have the information flow through is with a simple vlookup with some error trapping for presentation purposes.

=IFERROR(VLOOKUP(C24,Estimate!A:B,2,0),0)

Also, the loss of fidelity I spoke of earlier in the thread regarding Merging cells really came to pass when I went to drag the above formula. You should think about never using them again. Keep saying to yourself - Centre Across Selection. As your XL skills improve, it will be a God send. Oh, I put dummy figures in rather than your repetitive $5000 . That way you can actually see the formula is working as expected.

Take care

Smallman