Generic VBA code Pasting data to multiple sheets if condition met... smoothly

[Sjw185]

Hello,

I have literally started learning basic VBA this week. So far all my codes are immensely long lists of copy and pasting and I'm more than sure it can be condensed down

Literally my code is:

Private Sub KEY_OUT_Click()


Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet


Set copySheet = Worksheets("Dashboard")
Set pasteSheet = Worksheets("Order History")


'---------- Key Out Data - Midas Code and Week Day Order Gen --------------------


'--------- Todays Date When Generated -------------------------------------------


copySheet.Range("B5").Copy
pasteSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'---------- Midas Code ----------------------------------------------------------
copySheet.Range("c5").Copy
'Mon
pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Tues
pasteSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Weds
pasteSheet.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Thurs
pasteSheet.Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Fri
pasteSheet.Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Sat
pasteSheet.Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Sun
pasteSheet.Cells(Rows.Count, 20).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


Application.CutCopyMode = False
Application.ScreenUpdating = True


'---------- Week Day Order Gens ------------------------------------------------


'Mondays order gen
copySheet.Range("c11").Copy
pasteSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'Tuesdays order gen
copySheet.Range("d11").Copy
pasteSheet.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'Wednesdays order gen
copySheet.Range("e11").Copy
pasteSheet.Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'Thursdays order gen
copySheet.Range("f11").Copy
pasteSheet.Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'Fridays order gen
copySheet.Range("g11").Copy
pasteSheet.Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'Saturdays order gen
copySheet.Range("h11").Copy
pasteSheet.Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'Sundays order gen
copySheet.Range("i11").Copy
pasteSheet.Cells(Rows.Count, 21).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True




End Sub

... for every cell I want to copy for every day. It's a slow learning process here. I found a generic example and just played about till something happened.

I'm trying to copy values from a sheet named 'Dashboard' to two other sheets, 'Key Out' and 'Order History'.

The 'Key Out' I've managed... albeit it with a billion lines of repetitive code, as above.

I'm trying to now do the same again from 'Dash Board' to 'Order History' but- only if row 11 has a value equal to or higher than 0.

So far it is exactly the same as above and I cannot get my head around the condition part (and I'd like to really cut it down).

At the moment it makes sense to me with the notes and the format, which is fine whilst I'm learning but it is soooooooooo long.

Is it possible to get a generic basic code I could use, that is pretty simplified for my little brain to play about with that will help me with my project whilst I get to grips with this, I've found quite a few codes that would do what I'm asking but I can't for the life of me get it to do a thing.


Thank you oodles in advance!

 

[My Aswer Is This]

If your only wanting to copy values you can do things like this:
You do not need to copy and paste.

I wrote one line of code for you. You should be able to do the rest.

Sub copy_Values()
Set CopySheet = Worksheets("Dashboard")
Set pasteSheet = Worksheets("Order History")
pasteSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = CopySheet.Range("B5").Value
End Sub

 

[Sjw185]

Hello,

thank you for your reply. I've been playing with that code your kindly provided. I've ended up with a massive list of ranges to copy again.

Because the values I want are in differing cells on the copy sheet and need pasting to different cells all over the paste sheet, it is so slow to perform the routine.

Is there a way to consolidate the code down in to few commands.

I have a row of headers Mon thru Sun, under which are 7 rows of values for varying things. I need to copy these to another sheet into the correct cells, not just a direct copy and past of a single row to a single row.

So far I have a line for each and every separate single cell range to copy over. Exactly the same as my code in my original post. It takes ages to tick through each line.

Is this anything you could help with at all?

 

[My Aswer Is This]

The script I provided copies nothing. You do not see the copy or paste phrase.

Now if you could provide some logic as to why your copying this and why you paste it some place maybe.

Otherwise I know of no way to write only three or four lines of code to copy random cells and past them into random places on another sheet.