Previous Topic Next Topic

How to copy multiple lines with a Macro

Closed
Em - Nov 27, 2015 at 06:33 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 3, 2015 at 11:35 AM
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!

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 30, 2015 at 11:07 AM
So now we are past Sunday, I guess this question isn't relevant anymore ...
0
Em
Nov 30, 2015 at 01:25 PM
No it is! I manage to postpone for a week because other things are unfinished. Do you have any recommandations? It would be great! :)
Thanks in advance!
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 1, 2015 at 11:04 AM
Well, hopefully you can explain clear enough for me to help you in time. ;)

So you have a database in Sheet 2. Does Sheet 1 look the same?
Do you enter a country/year in Sheet 1 and then you want to look up all the matching data from Sheet 2?

I'm actually completely lost as to what you are trying to achieve.

Consider the option to upload your workbook to a free file sharing site like www.speedyshare.com or ge.tt and post back the download link.

Make sure you show how it is now and how you want it to be.
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 3, 2015 at 11:35 AM
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
0

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses