Copy and Paste Problem

[maninjapan]

I am running a macro that copy and pastes data from a sheet ("Alert") that is updated daily, onto a separate sheet to keep record of the data. This was working fine until I needed to add a second type of data. I know have 2 types of data updated to the sheet daily and I need to paste them onto their respective sheets. However I am having a problem telling the macro where Level2 data stops and Level 1 data begins. Below is the following macro that I was using to date and I have attached a sample of the data in the hope that someone could look at it and give me an idea of how I might solve this.

Thanks in advance


Sub AlertPaste()


Dim LR1 As Long

LR1 = ThisWorkbook.Worksheets("Alert").Range("C" & Rows.Count).End(xlUp).Row

ThisWorkbook.Sheets("Alert").Range("A5").Copy
ThisWorkbook.Sheets("Level2").Range("D" & Rows.Count).End(xlUp).Offset(1, -3).PasteSpecial Paste:=xlPasteValues
ThisWorkbook.Sheets("Alert").Range("A7:Q" & LR1).Copy
ThisWorkbook.Sheets("Level2").Range("D" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial Paste:=xlPasteValues


End Sub

[Teeroy]

The following will extend your code to handle the data break. It uses the "LEVEL 1" header to define the dividing row.

Sub AlertPaste()

Dim LR1 As Long
Dim LR2 As Long


With ThisWorkbook.Sheets("Alert")
LR1 = .Range("C" & Rows.Count).End(xlUp).Row
LR2 = .Range("A1:R" & LR1).Find("Level 1", LookIn:=xlValues).Row

.Range("A5").Copy
Sheets("Level2").Range("D" & Rows.Count).End(xlUp).Offset(1, -3).PasteSpecial Paste:=xlPasteValues
Sheets("Level1").Range("D" & Rows.Count).End(xlUp).Offset(1, -3).PasteSpecial Paste:=xlPasteValues
.Range("A7:Q" & LR2 - 1).Copy
ThisWorkbook.Sheets("Level2").Range("D" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial Paste:=xlPasteValues
.Range("A" & LR2 + 3 & ":Q" & LR1).Copy
ThisWorkbook.Sheets("Level1").Range("D" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial Paste:=xlPasteValues
End With
End Sub

[maninjapan]

Teeroy. Thank you very much. ".Find" was a new one for me.
One more small question. I am using xlPasteValues as to only copy the values, not the formulas. But I would like to keep the formatting as well. How would I paste the values and keep the format?

Thanks again!!

[Teeroy]

You're welcome. The easiest way to keep the formatting is to change "Paste:=xlPasteValues" to "Paste:=xlPasteValuesAndNumberFormats" in all 4 locations.

There are other options available; have a look at the built in help for PasteSpecial Method to see all of them.