Help transferring and combining only relevant data from multiple lists into one list.


New Member
Feb 2, 2022
Office Version
  1. 365
  1. Windows
Please forgive me, my coding skills are very novice.

I have a workbook where the first two sheets are where a user will record data. The data they record, while important, is not all necessary for a summary sheet.

The user inputs 8 pieces of data for every round: Round TC Number RN Number Money Decisions Power Plays Total cards played Comments
The summary sheet records only 4 pieces of data: RN Number Money Power Plays Total cards played

I am trying to create a macro that will take only the relevant data from the first list, and put it into the summary sheet. It will find the first blank row and put the data there, then add the next row of data to the next blank sheet. It will continue like this until all of the information from Game 1 sheet is entered and then will continue with the information from Game 2.

I only have a limited number of rows on my summary sheet, so after so many rows of data are entered, it will start again from the top of the next column.

I don't know all of the correct coding terms and syntax, so I thought I could make my macro do what I wanted by using "baby language" coding. Pretty much just "If this then do this, if not do that" language.

I thought my coding would be an acceptable work around for my limited knowledge but I started receiving errors that the routine was too large. I'm hoping someone smarter than me can help me write this program more efficiently.
I have also added a copy of a sample project.

Here is a sample of my coding
VBA Code:
Sub MakeSummaryForm()

'The first thing I want to do is clear the summary form, just in case there is any data left inside
Sheets("Summary Sheet").Range("A5:D13, F5:13").ClearContents

'In order to play a round, there must be a money wager.
'Therefor I use the Money column to determine if there was a hand to be recorded.
'If there is money in the first round, D5, then check to see if the summary sheet's
'first spot is available.

If Sheets("Data Sheet 1").Range("D5").Value > 0 Then
    If Sheets("Summary Sheet").Range("B5").Value = "" Then
'If its blank, record the first round of data.

    'Data Sheet 1 - Hand 1
    Sheets("Summary Sheet").Range("A5") = Sheets("Data Sheet 1").Range("C5").Value
    Sheets("Summary Sheet").Range("B5") = Sheets("Data Sheet 1").Range("D5").Value
    Sheets("Summary Sheet").Range("C5") = Sheets("Data Sheet 1").Range("F5").Value
    Sheets("Summary Sheet").Range("D5") = Sheets("Data Sheet 1").Range("G5").Value

'If Row 1 is not available I then check to see if row 2 is available.
    ElseIf Sheets("Summary Sheet").Range("B5").Value <> "" Then
        If Sheets("Summary Sheet").Range("B6").Value = "" Then
    Sheets("Summary Sheet").Range("A6") = Sheets("Data Sheet 1").Range("C5").Value
    Sheets("Summary Sheet").Range("B6") = Sheets("Data Sheet 1").Range("D5").Value
    Sheets("Summary Sheet").Range("C6") = Sheets("Data Sheet 1").Range("F5").Value
    Sheets("Summary Sheet").Range("D6") = Sheets("Data Sheet 1").Range("G5").Value
'If Row 2 is not available I then check to see if row 3 is available.
    ElseIf Sheets("Summary Sheet").Range("B6").Value <> "" Then
        If Sheets("Summary Sheet").Range("B7").Value = "" Then
    Sheets("Summary Sheet").Range("A7") = Sheets("Data Sheet 1").Range("C5").Value
    Sheets("Summary Sheet").Range("B7") = Sheets("Data Sheet 1").Range("D5").Value
    Sheets("Summary Sheet").Range("C7") = Sheets("Data Sheet 1").Range("F5").Value
    Sheets("Summary Sheet").Range("D7") = Sheets("Data Sheet 1").Range("G5").Value
        End If
'I will continue this until it checks every row, until it finds a blank row
'to record the data.
'The form I am populating only has 9 rows, so after the 9th row, I have it start over from
'the top of the next available area on the form...
'so in my sample form, I go from A5:D13 to F5:I13
'If there are no open slots, I will display a message box saying "No More Spots" and stop the script.

'Afterwards, I will repeat all of the same code, but for the second line of data.
    End Sub

Wasn't sure how to upload the actual project here. So here are a few screenshots. There are currently no working formulas in this sample

Any help is greatly appreciated.
sample 3.JPG

sample 1.JPG
Sample 2.JPG

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Marc L

Banned User
Apr 5, 2021
Office Version
  1. 2010
  1. Windows
Yet solved within another forum …​
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back