Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

**judeprem** · 06-21-2013, 01:39 PM

Hi All,
I am new to this forum and this is my first question. I am facing a unique problem in ledger listing, While ledger listing, some of the account codes are missing, i have listed in the q r column respectively.
I would like this missing values to be copied and paste it to U12 and V12 cells and copy rows u12 to af14 and paste it into the report in proper sequence which is displayed left side one by one till all the misssing values copied.

I have attached the sample file for reference

Any help appreciated Thanks jude

**p24leclerc** · 06-21-2013, 02:58 PM

Hi Jude,
try the following macro:

Sub Add_Missing_Ledger()
'
Dim c_ell As Range, C_ell1 As Range
'
Application.ScreenUpdating = False
'
For Each c_ell In Range("Q14", Cells(Rows.Count, 17).End(xlUp))
  Range("U12") = c_ell
  Range("V12") = c_ell.Offset(0, 1)
  Range("U12:AF15").Select
  Selection.Copy

  '
  For Each C_ell1 In Range("C11", Cells(Rows.Count, 3).End(xlUp))
    If (C_ell1 > c_ell And C_ell1.Font.Bold = True And InStr(1, C_ell1, "TOTAL", vbTextCompare) = 0) _
      Or C_ell1 = "GRAND TOTAL" Then
      C_ell1.Select
      Selection.Insert shift:=xlDown
      Exit For
    End If
  Next
Next
'
Application.ScreenUpdating = True
End Sub

Hope this helps

**judeprem** · 06-21-2013, 03:14 PM

Thanks a Lot, Pierre, it works perfectly. You have done its fast. It was quiet challenging one.
Great.

**judeprem** · 06-28-2013, 04:22 AM

Hi Pierre,

I am facing another kind of challenge in the code, apparently its copying the blank cells along with the format when the missing values are nil.

Any thoughts how i can overcome this issue?

Kind Regards
Jude

**p24leclerc** · 06-28-2013, 01:11 PM

Sorry, but I don't see it. Are you talking about my code to insert missing ledger?
It would be a pleasure to look at your issue but please be more specific.

**judeprem** · 06-30-2013, 01:46 PM

Thanks Pierre its fine now, I have addded the if condition along with your code (missing ledger) to find if its zero, it works now.

Kind Regards
Jude

Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

Re: Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

Re: Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

Re: Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

Re: Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

Re: Copy Paste the Missing Values in a Specified Cells and Insert the Rows into a Report

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions