Copy paste in VBA only copying last set of data

D

DylanMa

New Member
Joined
Jul 28, 2016
Messages
7
Here is my code I am simply trying to copy data from worksheets whose page name must initially be looked up (VLOOKUP).
I have a total of 42 dropdown boxes that link to sheet names that are to be added to the list.

I am fairly new to VBA and I can only get the last .copy to work.

below is my code, any help is greatly appreciated I've been watching endless videos but as the pages names have to be looked up I am having trouble finding anything that helps.

Sub ButtonSL_Click()


Dim WksSrc As Worksheet, WksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngLastRow As Long, lngDstLastRow As Long
Dim MealPlan As Worksheet

'Meal plan dims

Dim BFM As Range, BFTU As Range, BFW As Range, BFTH As Range, BFF As Range, BFSA As Range, BFSU As Range
Dim MTM As Range, MTTU As Range, MTW As Range, MTTH As Range, MTF As Range, MTSA As Range, MTSU As Range
Dim LM As Range, LTU As Range, LW As Range, LF As Range, LSA As Range, LSU As Range
Dim ATM As Range, ATTU As Range, ATW As Range, ATTH As Range, ATF As Range, ATSA As Range, ATSU As Range
Dim DM As Range, DTU As Range, DW As Range, DTH As Range, DF As Range, DSA As Range, DSU As Range
Dim SM As Range, STU As Range, SW As Range, STH As Range, SF As Range, SSA As Range, SSU As Range

' SETTING LOCATIONS OF MEAL PLAN

Set BFM = ThisWorkbook.Sheets("Menu Wk1").Range("C3")
Set BFTU = ThisWorkbook.Sheets("Menu Wk1").Range("D3")
Set BFW = ThisWorkbook.Sheets("Menu Wk1").Range("E3")
Set BFTH = ThisWorkbook.Sheets("Menu Wk1").Range("F3")
Set BFF = ThisWorkbook.Sheets("Menu Wk1").Range("G3")
Set BFSA = ThisWorkbook.Sheets("Menu Wk1").Range("H3")
Set BFSU = ThisWorkbook.Sheets("Menu Wk1").Range("I3")

Set MTM = ThisWorkbook.Sheets("Menu Wk1").Range("C10")
Set MTTU = ThisWorkbook.Sheets("Menu Wk1").Range("D10")
Set MTW = ThisWorkbook.Sheets("Menu Wk1").Range("E10")
Set MTTH = ThisWorkbook.Sheets("Menu Wk1").Range("F10")
Set MTF = ThisWorkbook.Sheets("Menu Wk1").Range("G10")
Set MTSA = ThisWorkbook.Sheets("Menu Wk1").Range("H10")
Set MTSU = ThisWorkbook.Sheets("Menu Wk1").Range("I10")

Set LM = ThisWorkbook.Sheets("Menu Wk1").Range("C14")
Set LTU = ThisWorkbook.Sheets("Menu Wk1").Range("D14")
Set LW = ThisWorkbook.Sheets("Menu Wk1").Range("E14")
Set LTH = ThisWorkbook.Sheets("Menu Wk1").Range("F14")
Set LF = ThisWorkbook.Sheets("Menu Wk1").Range("G14")
Set LSA = ThisWorkbook.Sheets("Menu Wk1").Range("H14")
Set LMSU = ThisWorkbook.Sheets("Menu Wk1").Range("I14")

Set ATM = ThisWorkbook.Sheets("Menu Wk1").Range("C24")
Set ATTU = ThisWorkbook.Sheets("Menu Wk1").Range("D24")
Set ATW = ThisWorkbook.Sheets("Menu Wk1").Range("E24")
Set ATTH = ThisWorkbook.Sheets("Menu Wk1").Range("F24")
Set ATF = ThisWorkbook.Sheets("Menu Wk1").Range("G24")
Set ATSA = ThisWorkbook.Sheets("Menu Wk1").Range("H24")
Set ATMSU = ThisWorkbook.Sheets("Menu Wk1").Range("I24")

Set DM = ThisWorkbook.Sheets("Menu Wk1").Range("C27")
Set DTU = ThisWorkbook.Sheets("Menu Wk1").Range("D27")
Set DW = ThisWorkbook.Sheets("Menu Wk1").Range("E27")
Set DTH = ThisWorkbook.Sheets("Menu Wk1").Range("F27")
Set DF = ThisWorkbook.Sheets("Menu Wk1").Range("G27")
Set DSA = ThisWorkbook.Sheets("Menu Wk1").Range("H27")
Set DSU = ThisWorkbook.Sheets("Menu Wk1").Range("I27")

Set SM = ThisWorkbook.Sheets("Menu Wk1").Range("C37")
Set STU = ThisWorkbook.Sheets("Menu Wk1").Range("D37")
Set SW = ThisWorkbook.Sheets("Menu Wk1").Range("E37")
Set STH = ThisWorkbook.Sheets("Menu Wk1").Range("F37")
Set SF = ThisWorkbook.Sheets("Menu Wk1").Range("G37")
Set SSA = ThisWorkbook.Sheets("Menu Wk1").Range("H37")
Set SSU = ThisWorkbook.Sheets("Menu Wk1").Range("I37")


'setting locations of recipe sheet names and ranges

Dim BFShtName As Range, MTShtName As Range, LShtName As Range, ATShtName As Range, DShtName As Range, SShtName As Range

Set BFShtName = ThisWorkbook.Sheets("Breakfast").Range("A2:BC200")
Set MTShtName = ThisWorkbook.Sheets("Morning_Tea").Range("A2:BC200")
Set LShtName = ThisWorkbook.Sheets("Lunch").Range("A2:BC200")
Set ATShtName = ThisWorkbook.Sheets("Afternoon_tea").Range("A2:BC200")
Set DShtName = ThisWorkbook.Sheets("Dinner").Range("A2:BC200")
Set SShtName = ThisWorkbook.Sheets("Supper").Range("A2:BC200")

'Naming VLookup sheetname

Dim BFMShtName As String

Dim BFMRangeIngred As Range, BFMRangeUnit As Range, BFMRangeTotal As Range
Dim BFTURangeIngred As Range, BFTURangeUnit As Range, BFTURangeTotal As Range
Dim BFWRangeIngred As Range, BFWRangeUnit As Range, BFWRangeTotal As Range
Dim BFTHRangeIngred As Range, BFTHRangeUnit As Range, BFTHRangeTotal As Range
Dim BFFRangeIngred As Range, BFFRangeUnit As Range, BFFRangeTotal As Range
Dim BFSARangeIngred As Range, BFSAURangeUnit As Range, BFSARangeTotal As Range
Dim BFSURangeIngred As Range, BFSURangeUnit As Range, BFSURangeTotal As Range

Dim MTMRangeIngred As Range, MTMRangeUnit As Range, MTMRangeTotal As Range
Dim MTTURangeIngred As Range, MTTURangeUnit As Range, MTTURangeTotal As Range
Dim MTWRangeIngred As Range, MTWRangeUnit As Range, MTWRangeTotal As Range
Dim MTTHRangeIngred As Range, MTTHRangeUnit As Range, MTTHRangeTotal As Range
Dim MTFRangeIngred As Range, MTFRangeUnit As Range, MTFRangeTotal As Range
Dim MTSARangeIngred As Range, MTSAURangeUnit As Range, MTSARangeTotal As Range
Dim MTSURangeIngred As Range, MTSURangeUnit As Range, MTSURangeTotal As Range


'Src short for short, dst short for destination

'Set Reference up-front

Dim rngDstIngred As Range, rngDstUnit As Range, rngDstTotal As Range
Dim LastRow As Integer, LastRow1 As Integer
Dim WksDstIngred As Integer, WksDstUnit As Integer, WksDstTotal As Integer
Dim MultipleRangeIngred As Integer


Set WksDst = Worksheets("Shopping List")
LastRow = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
LastRow1 = BFShtName.Cells(BFShtName.Rows.Count, "A").End(xlUp).Row
WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "B").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "C").End(xlUp).Row


'Vlookup and copy paste

'BREAKFAST'


BFMShtName = Application.WorksheetFunction.VLookup(BFM, BFShtName, 55, 0)
BFTUShtName = Application.WorksheetFunction.VLookup(BFTU, BFShtName, 55, 0)
BFWShtName = Application.WorksheetFunction.VLookup(BFW, BFShtName, 55, 0)
BFTHShtName = Application.WorksheetFunction.VLookup(BFTH, BFShtName, 55, 0)
BFFShtName = Application.WorksheetFunction.VLookup(BFF, BFShtName, 55, 0)
BFSAShtName = Application.WorksheetFunction.VLookup(BFSA, BFShtName, 55, 0)
BFSUShtName = Application.WorksheetFunction.VLookup(BFSU, BFShtName, 55, 0)

'MORNING TEA'

MTMShtName = Application.WorksheetFunction.VLookup(MTM, MTShtName, 55, 0)
MTTUShtName = Application.WorksheetFunction.VLookup(MTTU, MTShtName, 55, 0)
MTWShtName = Application.WorksheetFunction.VLookup(MTW, MTShtName, 55, 0)
MTTHShtName = Application.WorksheetFunction.VLookup(MTTH, MTShtName, 55, 0)
MTFShtName = Application.WorksheetFunction.VLookup(MTF, MTShtName, 55, 0)
MTSAShtName = Application.WorksheetFunction.VLookup(MTSA, MTShtName, 55, 0)
MTSUShtName = Application.WorksheetFunction.VLookup(MTSU, MTShtName, 55, 0)


'SET RANGES ON RECIPES FROM MENU PLAN

'BREAKFAST'

Set BFMRangeIngred = Sheets(BFMShtName).Range("A11:A35")
Set BFMRangeUnit = Sheets(BFMShtName).Range("F11:F35")
Set BFMRangeTotal = Sheets(BFMShtName).Range("G11:G35")

Set BFTURangeIngred = Sheets(BFTUShtName).Range("A11:A35")
Set BFTURangeUnit = Sheets(BFTUShtName).Range("F11:F35")
Set BFTURangeTotal = Sheets(BFTUShtName).Range("G11:G35")


Set BFWRangeIngred = Sheets(BFWShtName).Range("A11:A35")
Set BFWRangeUnit = Sheets(BFWShtName).Range("F11:F35")
Set BFWRangeTotal = Sheets(BFWShtName).Range("G11:G35")


Set BFTHRangeIngred = Sheets(BFTHShtName).Range("A11:A35")
Set BFTHRangeUnit = Sheets(BFTHShtName).Range("F11:F35")
Set BFTHRangeTotal = Sheets(BFTHShtName).Range("G11:G35")

Set BFFRangeIngred = Sheets(BFFShtName).Range("A11:A35")
Set BFFRangeUnit = Sheets(BFFShtName).Range("F11:F35")
Set BFFRangeTotal = Sheets(BFFShtName).Range("G11:G35")


Set BFSARangeIngred = Sheets(BFSAShtName).Range("A11:A35")
Set BFSARangeUnit = Sheets(BFSAShtName).Range("F11:F35")
Set BFSARangeTotal = Sheets(BFSAShtName).Range("G11:G35")


Set BFSURangeIngred = Sheets(BFSUShtName).Range("A11:A35")
Set BFSURangeUnit = Sheets(BFSUShtName).Range("F11:F35")
Set BFSURangeTotal = Sheets(BFSUShtName).Range("G11:G35")


'TUESDAY"

Set MTMRangeIngred = Sheets(MTMShtName).Range("A11:A35")
Set MTMRangeUnit = Sheets(MTMShtName).Range("F11:F35")
Set MTMRangeTotal = Sheets(MTMShtName).Range("G11:G35")

Set MTTURangeIngred = Sheets(MTTUShtName).Range("A11:A35")
Set MTTURangeUnit = Sheets(MTTUShtName).Range("F11:F35")
Set MTTURangeTotal = Sheets(MTTUShtName).Range("G11:G35")


Set MTWRangeIngred = Sheets(MTWShtName).Range("A11:A35")
Set MTWRangeUnit = Sheets(MTWShtName).Range("F11:F35")
Set MTWRangeTotal = Sheets(MTWShtName).Range("G11:G35")


Set MTTHRangeIngred = Sheets(MTTHShtName).Range("A11:A35")
Set MTTHRangeUnit = Sheets(MTTHShtName).Range("F11:F35")
Set MTTHRangeTotal = Sheets(MTTHShtName).Range("G11:G35")

Set MTFRangeIngred = Sheets(MTFShtName).Range("A11:A35")
Set MTFRangeUnit = Sheets(MTFShtName).Range("F11:F35")
Set MTFRangeTotal = Sheets(MTFShtName).Range("G11:G35")


Set MTSARangeIngred = Sheets(MTSAShtName).Range("A11:A35")
Set MTSARangeUnit = Sheets(MTSAShtName).Range("F11:F35")
Set MTSARangeTotal = Sheets(MTSAShtName).Range("G11:G35")


Set MTSURangeIngred = Sheets(MTSUShtName).Range("A11:A35")
Set MTSURangeUnit = Sheets(MTSUShtName).Range("F11:F35")
Set MTSURangeTotal = Sheets(MTSUShtName).Range("G11:G35")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''COPY PASTING RECIPE INGREDIENTS'''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Application.ScreenUpdating = False



'BreakFast'

BFMRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
BFMRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
BFMRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''


WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row



BFTURangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
BFTURangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
BFTURangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row


BFWRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
BFWRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
BFWRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

BFTHRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
BFTHRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
BFTHRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

BFFRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
BFFRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
BFFRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

BFSARangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
BFSARangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
BFSARangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

BFSURangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
BFSURangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
BFSURangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row


'Morning Tea'




MTMRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
MTMRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
MTMRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row



MTTURangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
MTTURangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
MTTURangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

MTWRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
MTWRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
MTWRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

MTTHRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
MTTHRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
MTTHRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

MTFRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
MTFRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
MTFRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

MTSARangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
MTSARangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
MTSARangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''SET NEW BOTTOM ROW ''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

MTSURangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
MTSURangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
MTSURangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues

WksDstIngred = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstUnit = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
WksDstTotal = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = True








End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Code: 
LastRow = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
BFMRangeIngred.Copy (WksDst.Cells(LastRow + 1, 1))
LastRow = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
BFMRangeUnit.Copy (WksDst.Cells(LastRow + 1, 2))
LastRow = WksDst.Cells(WksDst.Rows.Count, "A").End(xlUp).Row
BFMRangeTotal.Copy
WksDst.Cells(LastRow + 1, 3).PasteSpecial Paste:=xlPasteValues
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Move Completed cells auto using VBA when they have been changed to completed
Replies
5
Views
395
dmt32
D
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
263
rlv01
R
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
182
NdNoviceHlp
NdNoviceHlp
O
  • Solved
Detecting if the cell contains strikethrough is not working
Replies
2
Views
373
Fuji
F
Ottsel
  • Question Question
Create & Secure PDFS with Excel
Replies
3
Views
185
John_w
J

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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