PDA

View Full Version : VBA Excel Macro Copy/Paste Rows



dweeballina
11-23-2010, 02:10 PM
I have a series of spreadsheets, driven off of one data sheet (variable number of rows) that I need to populate using specific formulas. I've attached a very simplified version of what I'm looking to do. I've had success with a single line creator, but this is for multiple lines of data, and I've had no luck even getting started what I need.

The worksheet "MAIN DATA" contains the data that drives most of my formulas on the next two sheets, and it will have varying numbers of rows.

The worksheet "FORMULA SHEET 1" contains the basic formula's that I'm working with. I need to replicate the formula in row 2 for each row on "MAIN DATA" and paste as values below (rows 3 to where ever it ends) on the same "FORMULA SHEET 1". Same holds true for "FORMULA SHEET 2.

The final sheet I'm using, "LOOKUP DATA", is just a basic data table, where I hold some lookup data that fills in extra columns that I have to populate.

I'd ultimately like to have a macro that works to create all necessary rows on both "FORMULA SHEET"s.

Can anyone help, or maybe point me towards a thread that can?

dweeballina
12-02-2010, 11:50 AM
bump?

Sean.DiSanti
12-02-2010, 12:51 PM
you don't need to copy and paste things, that'd actually end up being more code than just grabbing the data and putting it where you want. just do a for loop to grab the data; and put it where you want... something like...
f1 = 2;iterator to represent the first blank row on the formula 1 sheet
f2 = 2;same thing for formula 2 sheet
For x = 2 to Worksheets("MAIN DATA").UsedRange.Rows.Count
Worksheets("FORMULA SHEET 1").Range("A" & f1).formula = Worksheets("MAIN DATA").range("a" & x).Formula & Worksheets("MAIN DATA").range("b" & x).Formula
Worksheets("FORMULA SHEET 2").Range("A" & f2).formula = Worksheets("MAIN DATA").range("a" & x).Formula & Worksheets("MAIN DATA").range("b" & x).Formula
f1 = f1+1
f2 = f2+2
NEXT
you'd of course want to write the code to fill out the other fields, just wanted to give you enough to get started, not do the work for you