YellowLabPro
06-28-2006, 06:09 PM
I have a worksheet that I require to be populated from two different worksheets which the two source worksheets are of identical structure.
The source workbook is MasterImport.xls and the first source worksheet to be copied is PCCombined_FF and the second worksheet to follow is PCCombined_VB to the target workbook of Upload_Fileworksheet and the worksheet is EC Products.
Along w/ that, I need a control value to be placed in column A of the target workbook from each sheet to indicate where each record's origin was derived, in the case of PCCombined_FF, the term "Fairfax" will be used starting in A4 to the last record from this worksheet and for PCCombined_VB, Va Beach will be used immediately following the last record to its last record commencing immediately after the last record from PCCombined_FF.
This code is as follows:
Option Explicit
Sub CopyColumn()
Dim LastRow As Long
Dim RowNum As Integer
Dim FairfaxRow As Integer
Dim Va_BeachRow As Integer
Application.ScreenUpdating = True
RowNum = Range("B65536").End(xlUp).Row
'Range("A4:A" & RowNum).Value = "Fairfax"
Range("Z4:Z" & RowNum).Value = "Active"
Range("AA4:AA" & RowNum).Value = "EOREOR"
'FairfaxRow = Range("A65536").End(xlUp).Row
'Range("A4:A" & FairfaxRow).Value = "Fairfax"
With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B65536").End(xlUp).Row + 1
.Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4") 'Item Record#
.Range("D4:D" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4") 'Item Description
'.Range("J4:J" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4") '.PasteSpecial(xlPasteValues)
.Range("J4:J" & LastRow).Copy
ActiveSheet.Range("D4").PasteSpecial Paste:=xlValues
.Range("H4:H" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("H4") 'Price
.Range("Q4:Q" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'PARENT COLOR
.Range("T4:T" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'CHILD COLOR
.Range("V4:V" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("K4") 'SIZE
'.Range("AE4:AE" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("O4") '.PasteSpecial(xlPasteValues) 'S/H Weight
.Range("AE4:AE" & LastRow).Copy
ActiveSheet.Range("O4").PasteSpecial Paste:=xlValues
'.Range("AD4:AD" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("P4") 'Actual Weight
.Range("AD4:AD" & LastRow).Copy
ActiveSheet.Range("P4").PasteSpecial Paste:=xlValues
.Range("E4:E" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("S4") 'Quantity
.Range("Y4:Y" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("T4") 'Manufacturer
'.Range("AA4:AA" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("U4") 'Deptartment
.Range("AA4:AA" & LastRow).Copy
ActiveSheet.Range("U4").PasteSpecial Paste:=xlValues
'.Range("AB4:AB" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("V4") 'Category
.Range("AB4:AB" & LastRow).Copy
ActiveSheet.Range("V4").PasteSpecial Paste:=xlValues
'.Range("AC4:AC" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("W4") 'SubCategory
.Range("AC4:AC" & LastRow).Copy
ActiveSheet.Range("W4").PasteSpecial Paste:=xlValues
End With
'------------------------------------------VB------------------------------------------------------------------------
'With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_VB")
'
''Va_BeachRow = Range("A65536").End(xlUp).Row
''Range("A" & Va_BeachRow, "A" & Va_BeachRow).Value = "Va Beach"
'
' LastRow = .Range("B65536").End(xlUp).Row + 1
' .Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4") 'Item Record#
' .Range("D4:D" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4") 'Item Description
' '.Range("J4:J" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4") '.PasteSpecial(xlPasteValues)
' .Range("J4:J" & LastRow).Copy
' ActiveSheet.Range("D4").PasteSpecial Paste:=xlValues
' .Range("H4:H" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("H4") 'Price
' .Range("Q4:Q" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'PARENT COLOR
' .Range("T4:T" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'CHILD COLOR
' .Range("V4:V" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("K4") 'SIZE
' '.Range("AE4:AE" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("O4") '.PasteSpecial(xlPasteValues) 'S/H Weight
' .Range("AE4:AE" & LastRow).Copy
' ActiveSheet.Range("O4").PasteSpecial Paste:=xlValues
' '.Range("AD4:AD" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("P4") 'Actual Weight
' .Range("AD4:AD" & LastRow).Copy
' ActiveSheet.Range("P4").PasteSpecial Paste:=xlValues
' .Range("E4:E" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("S4") 'Quantity
' .Range("Y4:Y" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("T4") 'Manufacturer
' '.Range("AA4:AA" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("U4") 'Deptartment
' .Range("AA4:AA" & LastRow).Copy
' ActiveSheet.Range("U4").PasteSpecial Paste:=xlValues
' '.Range("AB4:AB" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("V4") 'Category
' .Range("AB4:AB" & LastRow).Copy
' ActiveSheet.Range("V4").PasteSpecial Paste:=xlValues
' '.Range("AC4:AC" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("W4") 'SubCategory
' .Range("AC4:AC" & LastRow).Copy
' ActiveSheet.Range("W4").PasteSpecial Paste:=xlValues
'End With
Cells.Columns.AutoFit
Range("A4:BB" & RowNum).Interior.ColorIndex = xlNone
Rows("4:" & RowNum).Interior.ColorIndex = xlNone
[A4].Activate
End Sub
Previously another boardmember provided the code that performed almost the identical procedure, but slightly different than my current scenario. The following code works great. However, it is beyond my scope and I am unable to adapt it to my current project.
The code for this procedure immediately follows:
Sub A_DataEdited()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws As Worksheet
Dim arrWS
Dim Cell
Application.ScreenUpdating = True
arrWS = Array("TGFF", "Fairfax", "TGVB", "Va Beach")
LastRowDst = 2
For i = LBound(arrWS) To UBound(arrWS) Step 2
Set ws = Worksheets(arrWS(i))
LastRowSrc = ws.Range("A65536").End(xlUp).Row
ws.Range("A4:A" & LastRowSrc).Copy Sheets("DataEdited").Range("B" & LastRowDst)
ws.Range("D4:D" & LastRowSrc).Copy Sheets("DataEdited").Range("C" & LastRowDst & ":E" & LastRowDst)
Sheets("DataEdited").Range("A" & LastRowDst).Resize(LastRowSrc - 3) = arrWS(i + 1)
LastRowDst = LastRowDst + LastRowSrc - 3
Next i
LastRow = Range("F" & Rows.Count).End(xlUp).Row
For Each Cell In Range("G2:I" & LastRow)
Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
Next
End Sub
Much thanks,
YLP
The source workbook is MasterImport.xls and the first source worksheet to be copied is PCCombined_FF and the second worksheet to follow is PCCombined_VB to the target workbook of Upload_Fileworksheet and the worksheet is EC Products.
Along w/ that, I need a control value to be placed in column A of the target workbook from each sheet to indicate where each record's origin was derived, in the case of PCCombined_FF, the term "Fairfax" will be used starting in A4 to the last record from this worksheet and for PCCombined_VB, Va Beach will be used immediately following the last record to its last record commencing immediately after the last record from PCCombined_FF.
This code is as follows:
Option Explicit
Sub CopyColumn()
Dim LastRow As Long
Dim RowNum As Integer
Dim FairfaxRow As Integer
Dim Va_BeachRow As Integer
Application.ScreenUpdating = True
RowNum = Range("B65536").End(xlUp).Row
'Range("A4:A" & RowNum).Value = "Fairfax"
Range("Z4:Z" & RowNum).Value = "Active"
Range("AA4:AA" & RowNum).Value = "EOREOR"
'FairfaxRow = Range("A65536").End(xlUp).Row
'Range("A4:A" & FairfaxRow).Value = "Fairfax"
With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B65536").End(xlUp).Row + 1
.Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4") 'Item Record#
.Range("D4:D" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4") 'Item Description
'.Range("J4:J" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4") '.PasteSpecial(xlPasteValues)
.Range("J4:J" & LastRow).Copy
ActiveSheet.Range("D4").PasteSpecial Paste:=xlValues
.Range("H4:H" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("H4") 'Price
.Range("Q4:Q" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'PARENT COLOR
.Range("T4:T" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'CHILD COLOR
.Range("V4:V" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("K4") 'SIZE
'.Range("AE4:AE" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("O4") '.PasteSpecial(xlPasteValues) 'S/H Weight
.Range("AE4:AE" & LastRow).Copy
ActiveSheet.Range("O4").PasteSpecial Paste:=xlValues
'.Range("AD4:AD" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("P4") 'Actual Weight
.Range("AD4:AD" & LastRow).Copy
ActiveSheet.Range("P4").PasteSpecial Paste:=xlValues
.Range("E4:E" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("S4") 'Quantity
.Range("Y4:Y" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("T4") 'Manufacturer
'.Range("AA4:AA" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("U4") 'Deptartment
.Range("AA4:AA" & LastRow).Copy
ActiveSheet.Range("U4").PasteSpecial Paste:=xlValues
'.Range("AB4:AB" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("V4") 'Category
.Range("AB4:AB" & LastRow).Copy
ActiveSheet.Range("V4").PasteSpecial Paste:=xlValues
'.Range("AC4:AC" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("W4") 'SubCategory
.Range("AC4:AC" & LastRow).Copy
ActiveSheet.Range("W4").PasteSpecial Paste:=xlValues
End With
'------------------------------------------VB------------------------------------------------------------------------
'With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_VB")
'
''Va_BeachRow = Range("A65536").End(xlUp).Row
''Range("A" & Va_BeachRow, "A" & Va_BeachRow).Value = "Va Beach"
'
' LastRow = .Range("B65536").End(xlUp).Row + 1
' .Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4") 'Item Record#
' .Range("D4:D" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4") 'Item Description
' '.Range("J4:J" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4") '.PasteSpecial(xlPasteValues)
' .Range("J4:J" & LastRow).Copy
' ActiveSheet.Range("D4").PasteSpecial Paste:=xlValues
' .Range("H4:H" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("H4") 'Price
' .Range("Q4:Q" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'PARENT COLOR
' .Range("T4:T" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'CHILD COLOR
' .Range("V4:V" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("K4") 'SIZE
' '.Range("AE4:AE" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("O4") '.PasteSpecial(xlPasteValues) 'S/H Weight
' .Range("AE4:AE" & LastRow).Copy
' ActiveSheet.Range("O4").PasteSpecial Paste:=xlValues
' '.Range("AD4:AD" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("P4") 'Actual Weight
' .Range("AD4:AD" & LastRow).Copy
' ActiveSheet.Range("P4").PasteSpecial Paste:=xlValues
' .Range("E4:E" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("S4") 'Quantity
' .Range("Y4:Y" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("T4") 'Manufacturer
' '.Range("AA4:AA" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("U4") 'Deptartment
' .Range("AA4:AA" & LastRow).Copy
' ActiveSheet.Range("U4").PasteSpecial Paste:=xlValues
' '.Range("AB4:AB" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("V4") 'Category
' .Range("AB4:AB" & LastRow).Copy
' ActiveSheet.Range("V4").PasteSpecial Paste:=xlValues
' '.Range("AC4:AC" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("W4") 'SubCategory
' .Range("AC4:AC" & LastRow).Copy
' ActiveSheet.Range("W4").PasteSpecial Paste:=xlValues
'End With
Cells.Columns.AutoFit
Range("A4:BB" & RowNum).Interior.ColorIndex = xlNone
Rows("4:" & RowNum).Interior.ColorIndex = xlNone
[A4].Activate
End Sub
Previously another boardmember provided the code that performed almost the identical procedure, but slightly different than my current scenario. The following code works great. However, it is beyond my scope and I am unable to adapt it to my current project.
The code for this procedure immediately follows:
Sub A_DataEdited()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws As Worksheet
Dim arrWS
Dim Cell
Application.ScreenUpdating = True
arrWS = Array("TGFF", "Fairfax", "TGVB", "Va Beach")
LastRowDst = 2
For i = LBound(arrWS) To UBound(arrWS) Step 2
Set ws = Worksheets(arrWS(i))
LastRowSrc = ws.Range("A65536").End(xlUp).Row
ws.Range("A4:A" & LastRowSrc).Copy Sheets("DataEdited").Range("B" & LastRowDst)
ws.Range("D4:D" & LastRowSrc).Copy Sheets("DataEdited").Range("C" & LastRowDst & ":E" & LastRowDst)
Sheets("DataEdited").Range("A" & LastRowDst).Resize(LastRowSrc - 3) = arrWS(i + 1)
LastRowDst = LastRowDst + LastRowSrc - 3
Next i
LastRow = Range("F" & Rows.Count).End(xlUp).Row
For Each Cell In Range("G2:I" & LastRow)
Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
Next
End Sub
Much thanks,
YLP