YellowLabPro
08-24-2006, 12:18 PM
Could someone assist me w/ multi part issue please?
There are two parts to this. Request 1 has one part and Request 2 has two parts.
1) I am attempting to fill the cells w/ an interior color.
For the this section of "UsedRange", how to fill it w/ a certain interior color, lets say the soft yellow color?
Sub Importer()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("MasterImportSheetWebstore.xls").Sheets("TGFF")
Set ws3 = Workbooks("TGSImporter.xls").Sheets("Update")
LastRowSrc = ws1.Cells(Rows.Count, 1).End(xlUp).Row
'Set ws3 = Workbooks("Complete_Upload_File.xls").Sheets("EC Products")
'LastRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws3.Range("A1:H1") = Array("Origin", "Item#", "Record Description", "Dept", _
"Cat", "Qty", "Cost", "Price")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Rows("1:1").HorizontalAlignment = xlLeft
Rows("1:1").Font.Bold = True
ws3.UsedRange.Offset(1, 0).ClearContents
ws1.Range("u5:u" & LastRowSrc).Copy
ws3.Range("b2").PasteSpecial Paste:=xlPasteValues
ws1.Range("w5:w" & LastRowSrc).Copy
ws3.Range("c2").PasteSpecial Paste:=xlPasteValues
ws1.Range("ab5:ab" & LastRowSrc).Copy
ws3.Range("d2").PasteSpecial Paste:=xlPasteValues
ws1.Range("ac5:ac" & LastRowSrc).Copy
ws3.Range("e2").PasteSpecial Paste:=xlPasteValues
ws1.Range("Aa5:Aa" & LastRowSrc).Copy
ws3.Range("f2").PasteSpecial Paste:=xlPasteValues
ws1.Range("x5:x" & LastRowSrc).Copy
ws3.Range("g2").PasteSpecial Paste:=xlPasteValues
ws1.Range("z5:z" & LastRowSrc).Copy
ws3.Range("h2").PasteSpecial Paste:=xlPasteValues
ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "New Record"
Second Question:
A)Sort the entire range based on column B:
If after the sort takes place then look to see if the value in the immediate cell matches, if so place the term "Dupe" in the corresponding cell in Column I and fill that w/ Red.
B) Sort option #2-
Sort on column C
There are two parts to this. Request 1 has one part and Request 2 has two parts.
1) I am attempting to fill the cells w/ an interior color.
For the this section of "UsedRange", how to fill it w/ a certain interior color, lets say the soft yellow color?
Sub Importer()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("MasterImportSheetWebstore.xls").Sheets("TGFF")
Set ws3 = Workbooks("TGSImporter.xls").Sheets("Update")
LastRowSrc = ws1.Cells(Rows.Count, 1).End(xlUp).Row
'Set ws3 = Workbooks("Complete_Upload_File.xls").Sheets("EC Products")
'LastRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws3.Range("A1:H1") = Array("Origin", "Item#", "Record Description", "Dept", _
"Cat", "Qty", "Cost", "Price")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Rows("1:1").HorizontalAlignment = xlLeft
Rows("1:1").Font.Bold = True
ws3.UsedRange.Offset(1, 0).ClearContents
ws1.Range("u5:u" & LastRowSrc).Copy
ws3.Range("b2").PasteSpecial Paste:=xlPasteValues
ws1.Range("w5:w" & LastRowSrc).Copy
ws3.Range("c2").PasteSpecial Paste:=xlPasteValues
ws1.Range("ab5:ab" & LastRowSrc).Copy
ws3.Range("d2").PasteSpecial Paste:=xlPasteValues
ws1.Range("ac5:ac" & LastRowSrc).Copy
ws3.Range("e2").PasteSpecial Paste:=xlPasteValues
ws1.Range("Aa5:Aa" & LastRowSrc).Copy
ws3.Range("f2").PasteSpecial Paste:=xlPasteValues
ws1.Range("x5:x" & LastRowSrc).Copy
ws3.Range("g2").PasteSpecial Paste:=xlPasteValues
ws1.Range("z5:z" & LastRowSrc).Copy
ws3.Range("h2").PasteSpecial Paste:=xlPasteValues
ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "New Record"
Second Question:
A)Sort the entire range based on column B:
If after the sort takes place then look to see if the value in the immediate cell matches, if so place the term "Dupe" in the corresponding cell in Column I and fill that w/ Red.
B) Sort option #2-
Sort on column C