Assistance in transposing multiple sets of data
Hi Claus
Again, fairly close but not quite.
I just ran this and it did the whole first week perfectly.
Now just need to loop it through all 53 weeks and multiple workbooks..:)
Thank you again.
It's not pretty, but it worked.
With Sheets("Sheet1").Range("B7")
.Copy
Sheets("Sheet2").Range("A2:A15").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A9:A22")
.Copy
Sheets("Sheet2").Range("B2").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Monday
With Sheets("Sheet1").Range("B9:F22")
.Copy
Sheets("Sheet2").Range("C2").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Tuesday
With Sheets("Sheet1").Range("G7")
.Copy
Sheets("Sheet2").Range("A16:A29").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A9:A22")
.Copy
Sheets("Sheet2").Range("B16").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Tuesday
With Sheets("Sheet1").Range("G9:K22")
.Copy
Sheets("Sheet2").Range("C16").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Wednesday
With Sheets("Sheet1").Range("L7")
.Copy
Sheets("Sheet2").Range("A30:A43").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A9:A22")
.Copy
Sheets("Sheet2").Range("B30").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Wednesday
With Sheets("Sheet1").Range("L9:P22")
.Copy
Sheets("Sheet2").Range("C30").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Thursday
With Sheets("Sheet1").Range("Q7")
.Copy
Sheets("Sheet2").Range("A44:A57").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A9:A22")
.Copy
Sheets("Sheet2").Range("B44").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Thursday
With Sheets("Sheet1").Range("Q9:U22")
.Copy
Sheets("Sheet2").Range("C44").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Friday
With Sheets("Sheet1").Range("V7")
.Copy
Sheets("Sheet2").Range("A58:A71").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A9:A22")
.Copy
Sheets("Sheet2").Range("B58").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Friday
With Sheets("Sheet1").Range("V9:Z22")
.Copy
Sheets("Sheet2").Range("C58").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Saturday
With Sheets("Sheet1").Range("AA7")
.Copy
Sheets("Sheet2").Range("A72:A85").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A9:A22")
.Copy
Sheets("Sheet2").Range("B72").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Saturday
With Sheets("Sheet1").Range("AA9:AE22")
.Copy
Sheets("Sheet2").Range("C72").PasteSpecial Paste:=xlValues
End With
'_________________________________________________ _________________________________________
'Sub-contractor Trucks
'_________________________________________________ ______________________________
'Copy Date Week .1 - Monday
With Sheets("Sheet1").Range("B7")
.Copy
Sheets("Sheet2").Range("A86:A105").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A27:A46")
.Copy
Sheets("Sheet2").Range("B86").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Monday
With Sheets("Sheet1").Range("B27:F46")
.Copy
Sheets("Sheet2").Range("C86").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Tuesday
With Sheets("Sheet1").Range("G7")
.Copy
Sheets("Sheet2").Range("A106:A125").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A27:A46")
.Copy
Sheets("Sheet2").Range("B106").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Tuesday
With Sheets("Sheet1").Range("G27:K46")
.Copy
Sheets("Sheet2").Range("C106").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Wednesday
With Sheets("Sheet1").Range("L7")
.Copy
Sheets("Sheet2").Range("A126:A145").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A27:A46")
.Copy
Sheets("Sheet2").Range("B126").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Wednesday
With Sheets("Sheet1").Range("L27:P46")
.Copy
Sheets("Sheet2").Range("C126").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Thursday
With Sheets("Sheet1").Range("Q7")
.Copy
Sheets("Sheet2").Range("A146:A165").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A27:A46")
.Copy
Sheets("Sheet2").Range("B146").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Thursday
With Sheets("Sheet1").Range("Q27:P46")
.Copy
Sheets("Sheet2").Range("C146").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Friday
With Sheets("Sheet1").Range("V7")
.Copy
Sheets("Sheet2").Range("A166:A185").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A27:A46")
.Copy
Sheets("Sheet2").Range("B166").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Friday
With Sheets("Sheet1").Range("V27:Z46")
.Copy
Sheets("Sheet2").Range("C166").PasteSpecial Paste:=xlValues
End With
'Copy Date Week .1 - Saturday
With Sheets("Sheet1").Range("AA7")
.Copy
Sheets("Sheet2").Range("A186:A205").PasteSpecial Paste:=xlValues
End With
'Copy Units
With Sheets("Sheet1").Range("A27:A46")
.Copy
Sheets("Sheet2").Range("B186").PasteSpecial Paste:=xlValues
End With
'Copy Data Week .1 - Saturday
With Sheets("Sheet1").Range("AA27:AE46")
.Copy
Sheets("Sheet2").Range("C186").PasteSpecial Paste:=xlValues
End With
|