How to copy multiple lines with a Macro Closed

Question

Hello!

I have never worked with Macros but I strongly believe they could save my current data crunching work (that I need to finish by tomorrow or maybe by sunday if I manage to postpone :( )

Here is what I am trying to do:
I would like to be able to add a country and/or a year in a "button" in sheet 1, and that the relevant lines for the data base in sheet 2 will be copied for this extra year and/or country. 

Here below is an simplified example of my database structure

Type of X, Type of Y, Country, Year, Data
X1, Y1, CountryA, YearP, data(formula that uses data from previous columns)
X1, Y2, CountryA, YearP, data(formula that uses data from previous columns)
X1, Y3, CountryA, YearP, data(formula that uses data from previous columns)
X1, Y4, CountryA, YearP, data(formula that uses data from previous columns)
X2, Y1, CountryA, YearP, data(formula that uses data from previous columns)
X2, Y2, CountryA, YearP, data(formula that uses data from previous columns)
etc. 

If I add a country B, I would like all previous lines copied, and the formulas in cells "data" will reference to the data in the line itself. 

Any help will be strongly appreciated!!! HUGE thanks in advance!

TrowaD · Answer

So now we are past Sunday, I guess this question isn't relevant anymore ...

TrowaD · Answer

Hi Em,

Hopefully you made a mistake in your sample data: On sheet2 the year is adjusted for country A and B but not for C. In the code provided the year is also adjusted for country C. 

Here are the codes:
Private Sub CommandButton1_Click()
Dim mCountry As String
Dim x, y As Long

mCountry = InputBox("Please input the Country you wish to add:")
If mCountry = vbNullString Then Exit Sub

Range("A1").End(xlDown).Offset(1, 0).Insert
Range("A1").End(xlDown).Offset(1, 0) = mCountry

Sheets("Sheet2").Range("A2:E21").Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

x = Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row

For y = x To x - 19 Step -1
    Sheets("Sheet2").Cells(y, "C") = mCountry
Next y
    
End Sub

And

Private Sub CommandButton2_Click()
Dim mYear, cRows As Integer
Dim x, y As Long

mYear = InputBox("Please input the Year you wish to add:")
If mYear = vbNullString Then Exit Sub

Range("A1").End(xlDown).End(xlDown).End(xlDown).Offset(1, 0).Insert
Range("A1").End(xlDown).End(xlDown).End(xlDown).Offset(1, 0) = mYear

cRows = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet2").Range("A2:E" & Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

x = Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row

For y = x To cRows Step -1
    Sheets("Sheet2").Cells(y, "D") = mYear
Next y

End Sub
 

And here is your workbook with codes:
http://speedy.sh/Sw5GU/Em-Hot-to-copy.xlsm

Did this help you out?

Best regards,
Trowa

How to copy multiple lines with a Macro

2 responses

Similar discussions

Subscribe To Our Newsletter!