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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
No one has answered this, I think I made my ask too confusing. I apologize for that. I am not sure how to delete the question, so I am going to try and close it out as answered.
 
Upvote 0
lgrande said:
No one has answered this, I think I made my ask too confusing. I apologize for that. I am not sure how to delete the question, so I am going to try and close it out as answered.
Click to expand...
Is the sample above the sheet you are copying the data from? (And macros are still VBA, just because you didn't write it, doesn't make it not VBA)

And can you post the macro you recorded? Use the VBA code tags at the top of your reply please.
1706558419140.png
 
Upvote 0
Thank you, I did not realize a macro was still considered a VBA. I actually resolved #s 2 and 3, its is just number 1 that is still not working. When recording the macro to paste in that column, I take the step of going shift+control+ down arrow then I go to the end of the entire sheet, then while keeping Shift+Control pressed, I click on the up arrow that causes it to go up to the last populated cell. It is not populating all the ser#s though. There are more line items with ser#s than what is showing on my screenshot. Does that make sense?

No that is not where I am copying the data from. I will paste that in.

I appreciate your response.
Linda
 

Attachments

  • Capture 2.JPG
    Capture 2.JPG
    47.8 KB · Views: 4
Last edited:
Upvote 0
Can you show where the data comes from then, and the macro you are currently using?
 
Upvote 0
It's all coming from Column J. I am trying to get the code into a jpg file, and I'm having an issue. It saving as 5 separate files. ugh
 
Upvote 0
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").Select

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

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

Range(Selection, Selection.End(xlDown)).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("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").Select

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

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

Range(Selection, Selection.End(xlDown)).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("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
 
Last edited:
Upvote 0
Are you wanting to copy all of the data in column J, or specific sections?

According to the picture, there are blank cells in column J. Is there a column that has data in all the cells in the range you want to select?
 
Upvote 0
Yes there is. I thought the proper way to do it was to go to cell J2 and highlight the entire# of rows.

I take the step of going shift+control+ down arrow then I go to the end of the entire sheet, then while keeping Shift+Control pressed, I click on the up arrow that causes it to go up to the last populated cell (ser#). It is not populating all the ser#s though. It works for part of the template (where there are spaces before hitting the next ser#), then it stops working.
 
Upvote 0
lgrande said:
Yes there is. I thought the proper way to do it was to go to cell J2 and highlight the entire# of rows.

I take the step of going shift+control+ down arrow then I go to the end of the entire sheet, then while keeping Shift+Control pressed, I click on the up arrow that causes it to go up to the last populated cell (ser#). It is not populating all the ser#s though. It works for part of the template (where there are spaces before hitting the next ser#), then it stops working.
Click to expand...
But you do want to copy all the data in column J including the spaces?
 
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