adamsm
04-14-2010, 09:31 PM
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.
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.