PDA

View Full Version : Problem programming routine for conditional copy/paste



CydMM
01-31-2012, 06:41 AM
Hi,

I have created a file with several sheets, one for each month of the year.
Each sheet has a database table with several records....what I am trying to do is to find a routine able to check, for each filled up record, if the cell related to one specific Column (the P one) is either empty or not. In fact, the absence of data in that specific cell of each row, has an important meaning. In case that cell of the row, corresponding to that specific column is empty, the routine itself should copy the whole record (from column A to AC) and paste it on the first empty record row of the next month sheet, keeping each formatting setup.

How can I manage that?

I am really looking forward to receiving your help, thanks

Mark

omp001
01-31-2012, 06:41 PM
Hi Mark.
try and see if I followed you

Sub test()
Dim LR1 As Long, LR2 As Long
Dim m As Long, w As Long
Application.ScreenUpdating = False
For w = 1 To ThisWorkbook.Sheets.Count - 1
With Sheets(w)
LR1 = .Cells(Rows.Count, 1).End(xlUp).Row
For m = 6 To LR1
If .Cells(m, 16) = "" Then
.Cells(m, 1).Resize(, 29).Copy
With Sheets(w + 1)
LR2 = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(LR2 + 1, 1).PasteSpecial xlPasteValues
End With
End If
Next m
End With
Next w
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

CydMM
01-31-2012, 11:32 PM
Hi Osvaldo :hi:

The routine seems to perfectly run...but there is one problem.
Since I will use the macro more times than just one per month, each time the routine will run, it will copy "all" the record with the empty P cells, with the result to have several copies of the same record. I don't know if I clearly explained.

In other words the routine should also check if that record has been already pasted in the following sheet, maybe checking the content of the first column's cell (the A one). In case the routine finds out that the A1, A2, .. An or whatever has the same value of the one it is going to copy and paste, the macro stops itself.

What do you think about that?

omp001
02-01-2012, 03:26 AM
In other words the routine should also check if that record has been already pasted in the following sheet, maybe checking the content of the first column's cell (the A one). In case the routine finds out that the A1, A2, .. An or whatever has the same value of the one it is going to copy and paste, the macro stops itself.What do you think about that?
Ciao, Mark.
Thatīs the way...
So, please try this:

Sub test()
Dim LR1 As Long, LR2 As Long
Dim m As Long, w As Long
Application.ScreenUpdating = False
For w = 1 To ThisWorkbook.Sheets.Count - 1
With Sheets(w)
LR1 = .Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Sheets(w + 1).Cells(Rows.Count, 1).End(xlUp).Row
For m = 6 To LR1
If .Cells(m, 16) = "" _
And Application.CountIf(Sheets(w + 1). _
Range("A6:A" & LR2), .Cells(m, 1).Value) = 0 Then
.Cells(m, 1).Resize(, 29).Copy
With Sheets(w + 1)
LR2 = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(LR2 + 1, 1).PasteSpecial xlPasteValues
End With
End If
Next m
End With
Next w
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub