Copy Data From Orders to Database

[adamsm]

Hi friends
Once again, I’m stuck and need some expert advice.
I’m having two worksheets in my workbook with the name of "Orders" & "Database"
I’m having four columns in my worksheet "Orders" where my data starts from the 8th row.

I have written a macro code where I’m trying to copy data from the columns A, B, C, & D of the worksheet “Orders” to the columns G, H, I & J of the sheet “Database”.
My macro deletes the empty rows certain texts within a range that I have assigned to the macro; and copies the remaining data into the Sheet “Database” of my workbook.

In the column A I have the text “Product Colors” which I don’t want to get copied into column “H” also in column “B” I have the text “Status” which I don’t want to get copied into the Column “H”. And in column “C” I have “Capacity” which I don’t want to get copied into column “I” And in column “D”, I have the text “Range” which I don’t want to get copied into the column “J” of the worksheet “Database”

I don’t want the repeated column headers to be copied columns of the worksheet "Database".

For example if the save button is clicked the data that is copied from the column A of the worksheet "Orders" I don't want the "Product Colors” to appear as “Product Colors” gets doubled.

Instead I want it to be omitted when the column is copied.

When the columns are copied; the bar below the excel application asks me to press enter to paste the data that has been selected or copied. How this could be prevented?

In short, I want the text that is in bold to be copied as columns to the sheet "Database" Also I don't want empty rows when the data is copied.
What I’m stuck is how to get the macro to copy the rows headings into columns.
I have attached my workbook for your reference with the format how I want the two sheets to be when the macros run.
Here’s the code I’m using so far.

Sub CopyDataToDatabase()
  Dim m As Long
  Dim rng As Range
  Dim r As Long
      For r = 40 To 5 Step -1
        ' Check for empty row
        If WorksheetFunction.CountA(Rows(r)) = 0 Then
          Range("A" & r).EntireRow.Delete
        ' Check for "Product Colors"
        ElseIf Range("A" & r) = "Product Colors" Then
          Range("A" & (r - 1) & ":A" & r).EntireRow.Delete
          ' Decrease r because we deleted the row above
          r = r - 1
        ' Check for "End of Report"
        ElseIf Range("B" & r) = "End of Report" Then
          Range("B" & r).EntireRow.Delete
        End If
      Next r

  ' Determine the cell below the last used cell
  ' in column G on the Database sheet
  With Worksheets("Database")
    Set rng = .Cells(.Rows.Count, 7).End(xlUp).Offset(1, 0)
  End With

  With Worksheets("Orders")
    ' Determine last used row in column A
    ' on the Orders sheet
    m = .Cells(.Rows.Count, 1).End(xlUp).Row
    ' Copy starting at row 5
    .Range("A5:D" & m).Copy
    ' Paste special, values only
    rng.PasteSpecial Paste:=xlPasteValues
  End With
End Sub

Any help or suggestion would be kindly appreciated.

[adamsm]

Its sad to say that even though my thread has been in this forum for more than one day, nobody had bothered to give me a single reply.

I would be happy if someone had replied by even just saying....... no.. no that cannot be possible.

Anyways, God had helped me and I've solved my problem.

[1234567890]

hi i have the same problem can u help me plz

[adamsm]

could you upload your work book please so that I could help you

[Aussiebear]

Its sad to say that even though my thread has been in this forum for more than one day, nobody had bothered to give me a single reply.

I would be happy if someone had replied by even just saying....... no.. no that cannot be possible.

Anyways, God had helped me and I've solved my problem.

It is indeed unfortunate that in this instance, you were unable to be assisted. However you need to realise that those people whor are active in this forum, at any time, are here on a voluntary basis, exploring their interests, and sharing their knowlege where they think its appropriate or they are able to.

To improve any chance of getting a responce to a thread, you need to make the request for assistance clear as possible. When I read your initial post, my first impression was that you are chasing a project rather than a single issue. Then when I read your third paragraph.... well I just skipped over your thread. The multiple "I don't want's" diminished my interest.

Often, a better method is to upload a workbook with a before and after outcome, as this then cuts through any clutter inadvertantly created by the preamble. Whilst the issue raised is highly important to you, it may not necessarily be so to anyone else.

It is also,for the interests of all forum users, preferred where if you take the time to request some asistance, that a solution when achieved be also posted. Would you please do so on this occassion?

[adamsm]

When I read your initial post, my first impression was that you are chasing a project rather than a single issue. Then when I read your third paragraph.... well I just skipped over your thread. The multiple "I don't want's" diminished my interest.

If I upload the solution I had raised will it be interesting as my thread like you assumed is boring to look after.

[mdmackillop]

Unfortunately if there are a lot of posts, a post can be moved off the "front page" quite quickly, and as there are many new posts, older ones may be missed. If this happens a simple "Bump" to get you to the top again is not an issue, (as long as one doesn't bump too frequently). I admit I never saw your post.
Regards
MD

BTW , posting your solution is not for the benefit of the "answerers", but for those who visit looking for answers, like yourself.

[Aussiebear]

If I upload the solution I had raised will it be interesting as my thread like you assumed is boring to look after.

Firstly, I asked you to upload the solution for the benefit of the members here. If you don't feel that this would be beneficial for other people participating in the forum, then that's your choice.

Secondly, I have never assumed your thread is boring to look after. At the time, it didn't raise sufficient interest to warrant my participating in the thread. I had more important matters to attend to. The advice offered to you in my earlier post is for your benefit. Take it on board or leave it as you wish.

My experience here indicates those members with a good attitude get great responces. Those who like to be flippant more often than not run the risk of having their threads ignored.