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

L

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
 
Sort by date Sort by votes

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
lgrande said:
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
Click to expand...
See if this edited code does what you want.
 
Upvote 0
dreid1011 said:
See if this edited code does what you want.
Click to expand...
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
lgrande said:
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:)
Click to expand...
The headers in row 12? Or are the headers in row 1 in Salesorderdetaillist.xlsx?
 
Last edited:
Upvote 0
dreid1011 said:
The headers in row 12?
Click to expand...
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
lgrande said:
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?
Click to expand...
Yes, sorry. I realized that a moment ago.
 
Upvote 0
dreid1011 said:
Yes, sorry. I realized that a moment ago.
Click to expand...
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
lgrande said:
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
Click to expand...
That may help. I was actually going to suggest that.
 
Upvote 0
lgrande said:
Do you think that will help the duplicate header?
Click to expand...
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
You must log in or register to reply here.

Similar threads

N
  • Question Question
Copy a template if Master is Yes, rename with cell data
Replies
3
Views
75
Alex Blakenburg
A
S
  • Question Question
Combo box help
Replies
7
Views
337
Micron
M
B
  • Solved
Macro to Open new tab and duplicate the data from the template, and rename new tab from cell value of template.
Replies
3
Views
619
mumps
mumps
A
  • Solved
VBA to copy a template from one sheet to another based on dropdown
2
Replies
10
Views
705
altizerc2196
A
F
  • Question Question
Formula Help
Replies
3
Views
210
Formulahelp
F

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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