Macro pulling multiple lines

[drowningman]

Hi guys. In a work spreadsheet, I have worksheet that I dump tons of information into and another worksheet that I pull select information into a rebate template I've already set up. I've created a macro to pull that information with a click of a button and while it works great, it can only pull one line at a time. What do I need to add to this script to have it pull all the rows that have information on it? Here's what I currently have ... thanks in advance. Please let me know if you need any additional information (note that var12 is a +2 on purpose and is the only var that needs to jump two columns over).

Sub Upload()

Sheets("Dump Sheet").Select
var1 = Range("B5").Value
var2 = Range("C5").Value
var3 = Range("D5").Value
var4 = Range("E5").Value
var5 = Range("F5").Value
var6 = Range("G5").Value
var7 = Range("H5").Value
var8 = Range("I5").Value
var9 = Range("J5").Value
var10 = Range("K5").Value
var11 = Range("L5").Value
var12 = Range("M5").Value
var13 = Range("N5").Value
var14 = Range("O5").Value
var15 = Range("P5").Value
var16 = Range("Q5").Value
var17 = Range("R5").Value
var18 = Range("S5").Value

Sheets("Rebate Formulas").Select
Range("B5").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 1).Select
Loop

ActiveCell.Offset(0, x) = var1
x = x + 1
ActiveCell.Offset(0, x).Value = var2
x = x + 1
ActiveCell.Offset(0, x).Value = var3
x = x + 1
ActiveCell.Offset(0, x).Value = var4
x = x + 1
ActiveCell.Offset(0, x).Value = var5
x = x + 1
ActiveCell.Offset(0, x).Value = var6
x = x + 1
ActiveCell.Offset(0, x).Value = var7
x = x + 1
ActiveCell.Offset(0, x).Value = var8
x = x + 1
ActiveCell.Offset(0, x).Value = var9
x = x + 1
ActiveCell.Offset(0, x).Value = var10
x = x + 1
ActiveCell.Offset(0, x).Value = var11
x = x + 1
ActiveCell.Offset(0, x).Value = var12
x = x + 2
ActiveCell.Offset(0, x).Value = var13
x = x + 1
ActiveCell.Offset(0, x).Value = var14
x = x + 1
ActiveCell.Offset(0, x).Value = var15
x = x + 1
ActiveCell.Offset(0, x).Value = var16
x = x + 1
ActiveCell.Offset(0, x).Value = var17
x = x + 1
ActiveCell.Offset(0, x).Value = var18
x = x + 1
End Sub

 

[mvptomlinson]

Let's see if I understand the question:
1. You have a 'Dump Sheet', in which columns B:S have data.
2. You want to move every row from 'Dump Sheet' that has data into sheet 'Rebate Formulas'
3. You want to move all rows of data at once, not click a button and do one row at a time.

When you move the data into the new sheet, do you simply want to put it into the first empty row going down column B?

As for the +2 bit, are you saying you want 'Dump Sheet'!B5:M5 to go into 'Rebate Formulas'!Bx:Mx, and 'Dump Sheet'!N5:S5 to go into 'Rebate Formulas'!Ox:Tx? (where x represents the first empty row)

If I'm way off, please provide an example. I'm sure we can shorten your code by well over 50% and do all that you're trying to accomplish.

 

[jindon]

What do I need to add to this script to have it pull all the rows that have information on it?

What do you mean ?

 

[drowningman]

Thanks for your quick responses.

MVP: yes to 1, 2 and 3. There is no way to know how many rows of data I will have, but I want all rows with data moved at the click of a button (and clicked once).

When the data is moved to the new worksheet, it drops it in the first empty row. So hypothetically, if I had 30 rows of data, I'd like the macro to grab all thirty rows of data and move it to the "rebate formula" worksheet, one row after the other. The +2 on variable 13 is because there is a small blank column that's greyed out (for some of the people to who will use this to notice the important sections of the sheet). Its not any more complicated than that.

Jindon, I hope that answers your question too.

 

[jindon]

Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 1).Select
Loop

Which cell do you want to paste the first item ?
Your code moves diagonally from B5, C6, D7....

 

[mvptomlinson]

This may work for you:

Sub MoveData()
    Dim nextRow As Long, lastRow As Long
    lastRow = Sheets("Dump Sheet").Range("B65536").End(xlUp).Row
    nextRow = Sheets("Rebate Formulas").Range("B65536").End(xlUp).Row + 1
    Sheets("Dump Sheet").Range("B5:M" & lastRow).Copy _
        Sheets("Rebate Formulas").Range("B" & nextRow)
    Sheets("Dump Sheet").Range("N5:S" & lastRow).Copy _
        Sheets("Rebate Formulas").Range("O" & nextRow)
End Sub

 

[jindon]

Sub test()
Dim rng As Range, LastR As Range
Set LastR = Sheets("Rebate Formulas").Range("b" & Rows.Count).End(xlUp)(2)
With Sheets("Dump Sheet")
    Set rng = .Range("b5",.Range("b" & Rows.Count).End(xlUp))
End With
LastR.Resize(rng.Rows.Count, 12).Value = rng.Resize(,12).Value
LastR.Offset(,13).Resize(rng.Rows.Count, 6).Value = rng.Offset(,12).Resize(,6).Value
Set rng = Nothing : Set LastR = Nothing
End Sub

 

[drowningman]

Thanks so much to both of you! Rock on!