-
.copy .paste prolem when pasting to tables
Self taught... Needing some help pasting information from "DW" to tables... Code pastes to the tables correctly for "Range A" of target worksheet, but the table expands to the bottom of "Range A" when the information is pastes and "Range B,C,D,E, ETC" offsets below the previous range in the table of concern. Pic at bottom of post. Thanks!
Dim DW As Worksheet
Dim WFIT As Worksheet
Dim EIT As Worksheet
Dim PIT As Worksheet
Dim BIT As Worksheet
Dim MIT As Worksheet
Dim N As Long
Dim M As Long
Dim O As Long
Dim P As Long
Set DW = Worksheets("DailyWorksheet")
Set WFIT = Worksheets("WorkForceInformationTable")
Set EIT = Worksheets("EquipmentInformationTable")
Set PIT = Worksheets("ProjectInformationTable")
Set BIT = Worksheets("BidItemInformationTable")
Set MIT = Worksheets("MaterialInformationTable")
Application.ScreenUpdating = False
'Selects Cell C8 or Down 8 and Over 3
N = Cells(8, 3).End(xlDown).Row
'Contractor's WorkForce Section
DW.Range("C8:C" & N).Copy
WFIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("B8:B" & N).Copy
WFIT.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("A8:A" & N).Copy
WFIT.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("V8:V" & N).Copy
WFIT.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("W8:W" & N).Copy
WFIT.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("X8:X" & N).Copy
WFIT.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Contractor's Equipment Section
'Selects Cell G8 or Down 8 and Over 7
M = Cells(8, 7).End(xlDown).Row
DW.Range("G8:G" & M).Copy
EIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("F8:F" & M).Copy
EIT.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("Y8:Y" & M).Copy
EIT.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("Z8:Z" & M).Copy
EIT.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("AA8:AA" & M).Copy
EIT.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Bid Items Installed Section
'Selects Cell G19 or Down 19 and Over 7
O = Cells(19, 2).End(xlDown).Row
DW.Range("A19:A" & O).Copy
BIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("B19:B" & O).Copy
BIT.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("D19" & O).Copy
BIT.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("E19:E" & O).Copy
BIT.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("V19:V" & O).Copy
BIT.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("W19:W" & O).Copy
BIT.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("X19:X" & O).Copy
BIT.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Materials Used Section
'Selects Cell G19 or Down 19 and Over 7
P = Cells(19, 7).End(xlDown).Row
DW.Range("G19:G" & P).Copy
MIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("F19:F" & P).Copy
MIT.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("J19:J" & P).Copy
MIT.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("Y19:Y" & P).Copy
MIT.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("Z19:Z" & P).Copy
MIT.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
DW.Range("AA19:AA" & P).Copy
MIT.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub
HELP1.JPG
Picture is of WFIT worksheet after information is copied from DW worksheet.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules