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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

S
  • Question Question
Combo box help
Replies
7
Views
244
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
568
mumps
mumps
A
  • Solved
VBA to copy a template from one sheet to another based on dropdown
2
Replies
10
Views
686
altizerc2196
A
F
  • Question Question
Formula Help
Replies
3
Views
205
Formulahelp
F
A
  • Question Question
Changing row number dynamically in link to another file
Replies
1
Views
201
AlphaFrog
AlphaFrog

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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