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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

K
  • Solved
Macro for making one cell uppercase
Replies
4
Views
68
katieACI
K
I
  • Question
New Sheet from Template, then fill in specific cells with data from a separate sheet
Replies
1
Views
175
IrishPebbles
I
L
  • Solved
VBA to Copy Column based on Header Name from cell Value on another Sheet
Replies
2
Views
357
lamarh755
L
E
  • Solved
Large Spreadsheet Help!
Replies
5
Views
206
awoohaw
awoohaw
E
  • Question
VLOOKUP or similar to cut/paste data
Replies
3
Views
137
kvsrinivasamurthy
K

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

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