PDA

View Full Version : [SOLVED] Copying data with changing rows.



headsniper
08-27-2018, 06:13 PM
Hi all,

I am a newbie to VBA. I've been searching online how to do what I want to do, but I cannot get a definitive answer.

The worksheet I have has changing data every hour, which is also sent out on an email every hour as well.

I cannot send a template for my worksheet due to my company policy(my home pc also does not have excel). I will try to explain what I am trying to do with the best of my ability.

My template consists of user auxiliary data. The data needed highlights in red. What I do is I hide a cell, filter one auxiliary column data to red, set order from largest to smallest, then I copy that column and the previous data columns on to an existing blank sheet. I then clear the original sheet. I then hide the previously used auxiliary, so that I can rinse and repeat with the other auxiliary data, adding and identifier name two cells below the previous data, then I paste again. I do this until i have pasted all the auxiliary data.

The problem I am encountering is that since the data changes in rows at least adding one cell, the copy of the next data overlaps the previous ones. I am trying to set the code so that I can add an identifier/name two cells below the most recently copied data, and copying the new data right below the identifier cell.

My company is so stingy with the internet policies, that I cannot even read vba forums at the office. I have to copy the codes to my email, send them to my company email, and read it from there. Without excel on my own pc, this has definitely been a challenge to learn vba.

Sorry for the long text everyone. I am appreciative on any help given.

jolivanes
08-27-2018, 09:32 PM
I don't know if I understood things right but this will paste in the first empty cell in Column A in Sheet2.

Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
sh1.Range("A1:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Copy sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub

headsniper
08-30-2018, 06:55 AM
Thanks jolivanes.

This will definitely go in the code.

I am still trying to figure out how to copy 2 cells below data that was also copied. The data range keeps changing, so the copy overlaps other data in the code.

jolivanes
08-31-2018, 09:20 PM
Re: I am still trying to figure out how to copy 2 cells below data that was also copied. The data range keeps changing, so the copy overlaps other data in the code.
You need to explain that in detail.

headsniper
09-04-2018, 10:03 AM
I am using the macro recorder feature in excel.

When I did the initial recording, it would copy everything perfectly.

After the data changed, the number of rows changed as well, with the data being copied over other data.

I think since the code only copies to a specific row, the new data that is placed below gets deleted by the newly copied data.

jolivanes
09-04-2018, 12:08 PM
Play around with the attached.