VBA data transfer to continue on next empty row (Excel 2013)

NoSparks · #3 04-18-2019, 06:14 AM

If you're going to do a separate macro for each specific sheet all that needs to change from macro to macro is the specific sheet name and the starting point for j.
So they could all be like your first macro, changing just the sheet name and calculating j instead of hard coding it.

Code:

    j = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

You could do everything within a single macro by doing something along the lines of this

Code:

Sub CombineSheets()
    Dim sourcesheet As Worksheet
    Dim i As Long, j As Long
    
    j = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    For Each sourcesheet In ThisWorkbook.Sheets
        If sourcesheet.Name <> ActiveSheet.Name Then
            With sourcesheet
                For i = 6 To .Cells(Rows.Count, 3).End(xlUp).Row
                    If .Cells(i, 4) <> "" Then
                        ActiveSheet.Cells(j, 1).Value = .Cells(i, 1).Value & " " & .Cells(i, 2).Value
                        ActiveSheet.Cells(j, 2).Resize(, 6).Value = .Cells(i, 2).Resize(, 6).Value
                        j = j + 1
                    End If
                Next i
            End With
        End If
    Next sourcesheet
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Transfer data from list with multiple headings from word to excel	psohms	Word	1	06-11-2015 04:39 PM
Data Transfer From Excel	spc94	Word VBA	1	06-03-2015 09:56 PM
Transfer Data between Excel and Word	s_samira_21	Excel	0	01-19-2015 05:21 AM
Data transfer from Word tables to Excel	shoro	Word	4	10-01-2013 05:08 AM
Is it possilble to transfer data from Excel to Publisher?	markhuges	Publisher	8	10-23-2011 05:55 AM