Macro Help. Pasting information from another spreadsheet into a Macro Template

lgrande

Board Regular
Joined
Nov 2, 2012
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Good evening,
I created a Macro and I have 3 issues (I am still looking for the best VBA training for newbies, so I am not using a VBA yet)

1) The number of rows being pasted into the template will vary..so I need it to go to the last filled cell. So A13 to the last populated cell. Although one Column may have a populated cell with a ser# in E13, but may have blanks for items with no ser# and then possibly another ser# in another cell but not consecutive.

2) Although I have the template to left justify in all columns except Column A (I have that one Top and centered,) it centers all of them B-E.

3) I have all columns to Wrap Text at the top of the cell (formatted in the xlsm template)and it is not working (I manually did this in the example shown) - Columns A-E
example.JPG


Banging my head for a while and have recorded the macro several times, with no luck. Any help would be so appreciated.:)

Linda
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
VBA Code:
'

' Keyboard Shortcut: Ctrl+Shift+G Sub PACKSLIP()

' PACKSLIP Macro

'

' Keyboard Shortcut: Ctrl+Shift+G

'

Windows("Salesorderdetaillist.xlsx").Activate

Range("K2").Select

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("C10").Select

ActiveSheet.Paste

Range("C10").Select

Application.CutCopyMode = False

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = -0.149998474074526

.PatternTintAndShade = 0

End With

Range("A13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("AC2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("B13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("C2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("C13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("D2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("C13").Select

Range("D13").Select

ActiveSheet.Paste

Range("C13").Select

Windows("Salesorderdetaillist.xlsx").Activate

ActiveWindow.SmallScroll Down:=-39

Range("E2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Application.CutCopyMode = False

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

ActiveSheet.Paste

Windows("Salesorderdetaillist.xlsx").Activate

Range("J2:J" & Range("J" & Rows.Count).End(xlUp).Row).Select
Selection.Copy

Application.CutCopyMode = False

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("E13").Select

ActiveSheet.Paste

Windows("Salesorderdetaillist.xlsx").Activate

Range("AC2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Application.CutCopyMode = False

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("A13").Select

ActiveSheet.Paste

Application.CutCopyMode = False

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Range("B13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("C2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

ActiveSheet.Paste

Range("D12").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Application.CutCopyMode = False

Rows("13:13").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

With Selection

.VerticalAlignment = xlTop

.WrapText = True

.Orientation = 0

.AddIndent = False

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

ActiveWindow.SmallScroll Down:=13

End Sub



'

Windows("Salesorderdetaillist.xlsx").Activate

Range("K2").Select

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("C10").Select

ActiveSheet.Paste

Range("C10").Select

Application.CutCopyMode = False

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = -0.149998474074526

.PatternTintAndShade = 0

End With

Range("A13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("AC2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("B13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("C2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("C13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("D2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("C13").Select

Range("D13").Select

ActiveSheet.Paste

Range("C13").Select

Windows("Salesorderdetaillist.xlsx").Activate

ActiveWindow.SmallScroll Down:=-39

Range("E2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Application.CutCopyMode = False

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

ActiveSheet.Paste

Windows("Salesorderdetaillist.xlsx").Activate

Range("J2:J" & Range("J" & Rows.Count).End(xlUp).Row).Select
Selection.Copy

Application.CutCopyMode = False

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("E13").Select

ActiveSheet.Paste

Windows("Salesorderdetaillist.xlsx").Activate

Range("AC2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Application.CutCopyMode = False

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

Range("A13").Select

ActiveSheet.Paste

Application.CutCopyMode = False

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Range("B13").Select

Windows("Salesorderdetaillist.xlsx").Activate

Range("C2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Selection.Copy

Windows("lastadour - Packing Slip Template Test 6.xlsm").Activate

ActiveSheet.Paste

Range("D12").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

Application.CutCopyMode = False

Rows("13:13").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlUp)).Select

With Selection

.VerticalAlignment = xlTop

.WrapText = True

.Orientation = 0

.AddIndent = False

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

ActiveWindow.SmallScroll Down:=13

End Sub
See if this edited code does what you want.
 
Upvote 0
See if this edited code does what you want.
Oh my goodness Thank you so much. It worked! I have one last issue. When I am copying each column, I am not including the headers, but the macro brings them in anyway. Any idea why? You have really gone above and beyond, so if this is too much, I will keep trying:)
 

Attachments

  • Duplicate header.JPG
    Duplicate header.JPG
    76.2 KB · Views: 4
Upvote 0
Oh my goodness Thank you so much. It worked! I have one last issue. When I am copying each column, I am not including the headers, but the macro brings them in anyway. Any idea why? You have really gone above and beyond, so if this is too much, I will keep trying:)
The headers in row 12? Or are the headers in row 1 in Salesorderdetaillist.xlsx?
 
Last edited:
Upvote 0
The headers in row 12?
The headers in row 12 is on the template. The macro is pulling the header from the second spreadsheet Row 1 and pasting it into row 13, although I did not copy Row 1 when creating the macro. Does that make sense?
 
Upvote 0
The headers in row 12 is on the template. The macro is pulling the header from the second spreadsheet Row 1 and pasting it into row 13, although I did not copy Row 1 when creating the macro. Does that make sense?
Yes, sorry. I realized that a moment ago.
 
Upvote 0
Yes, sorry. I realized that a moment ago.
Also, since the number of rows will vary for each packing list, should I change the other columns like you did for column J? Range("J2:J" & Range("J" & Rows.Count).End(xlUp).Row).Select
 
Upvote 0
Also, since the number of rows will vary for each packing list, should I change the other columns like you did for column J? Range("J2:J" & Range("J" & Rows.Count).End(xlUp).Row).Select
That may help. I was actually going to suggest that.
 
Upvote 0
Do you think that will help the duplicate header?
It's absolutely worth a shot. Otherwise, I don't know for sure because I don't have your data to work on, and all tests I've done only copy the selection I chose.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top