Paste certain cells to the first blank row of another worksheet

Lipunator

New Member
Joined
Oct 11, 2017
Messages
2
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.

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,434
Office Version
365
Platform
Windows
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:

Code:
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

New Member
Joined
Oct 11, 2017
Messages
2
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)

Code:
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
 

Forum statistics

Threads
1,081,702
Messages
5,360,738
Members
400,594
Latest member
Frothingslosh

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top