Paste certain cells to the first blank row of another worksheet

[Lipunator]

Hey Excel community,

I have tried different solution approaches from this thread but they don't seem to fit my specific situation (or I'm making a fundamental mistake, who knows haha).

I'm working on a document for meeting minutes and I want to paste the topics of the agenda to the worksheet of the minutes, but only specific cells.

This is the code I'm currently using, but it pastes the cells to the same row like they are in the original list with one empty row in between them. Would be fine, but since I will add meeting minutes, every top will be take a couple of rows (but I can' estimate for sure how many it will take, so I would manually add these.

Sub UpdateMinutes()
Dim Zeile As Long
Dim ZeileMax As Long
Dim n As Long
With Tabelle1
ZeileMax = .UsedRange.Rows.Count
n = 1
For Zeile = 1 To ZeileMax
If .Cells(Zeile, 10).Value = "1" Then
 
.Cells(Zeile, 1).Copy Destination:=Tabelle3.Cells(Zeile, 1).Rows(n)
.Cells(Zeile, 2).Copy Destination:=Tabelle3.Cells(Zeile, 2).Rows(n)
.Cells(Zeile, 4).Copy Destination:=Tabelle3.Cells(Zeile, 4).Rows(n)
.Cells(Zeile, 9).Copy Destination:=Tabelle3.Cells(Zeile, 6).Rows(n)
n = n + 1
End If
Next Zeile
End With
End Sub

How do I make the makro paste it to the first empty column?
(The table iI want to paste it in starts at row 8)


I'm not sure whether it's possible to upload the excel file, I didn't find it. If there is a way, I would do it if it helps.

 

[Joe4]

Welcome to the Board!

You can find the first blank column in any row (after the last piece of data in that row) like this:

Dim myCol as Long
myCol=[COLOR=#333333]Tabelle3.Cells(Zeile, Columns.Count).End(xlToLeft).Offset(0,1).Column
[/COLOR]If myCol =2 and [COLOR=#333333]Tabelle3.Cells(Zeile, 1)="" Then myCol=1
[/COLOR]

Note that you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[Lipunator]

Hey Joe,

Thank you very much for the help! It semed to work, but then it started rearranging the rows before posting them. So Top 1, 2, 3, 4 were 4, 1, 2, 3 then.
This happened once. And now I when I add something in between the pasted rows, the newly pasted ones either get pasted like 5 rows underneath the last used row, or they overwrite what I inserted.

I thought I inserted your piece of code the right way, but I'm obviuosly starting to doubt that I did.

And I feel it doesn't work at all when I combine and center (I don't know what it's called in english. It's when you make one cell out of two)

Sub UpdateMinutes()
Dim Zeile As Long
Dim ZeileMax As Long
Dim myCol As Long

With Tabelle1
ZeileMax = .UsedRange.Rows.Count

For Zeile = 1 To ZeileMax

myCol = Tabelle3.Cells(Zeile, Columns.Count).End(xlToLeft).Offset(0, 1).Column
If myCol = 2 And Tabelle3.Cells(Zeile, 1) = "" Then myCol = 1

If .Cells(Zeile, 10).Value = "1" Then
 
.Cells(Zeile, 1).Copy Destination:=Tabelle3.Cells(Zeile, 1).Rows(myCol)
.Cells(Zeile, 2).Copy Destination:=Tabelle3.Cells(Zeile, 2).Rows(myCol)
.Cells(Zeile, 4).Copy Destination:=Tabelle3.Cells(Zeile, 4).Rows(myCol)
.Cells(Zeile, 9).Copy Destination:=Tabelle3.Cells(Zeile, 6).Rows(myCol)

End If
Next Zeile
End With
End Sub

 

[Joe4]

And I feel it doesn't work at all when I combine and center

Are you using "Merge and Center". Please do not do this!!!
Using merged cells causes all sort of problems for VBA, sorting, formatting, etc. Use the Center Across Selection formatting option instead.
See: https://excelexposure.com/alternative-to-merged-cells-quick-tip/