How to Repeat Macro for all rows with data

mrmattllewellyn

New Member
Joined
Nov 26, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

Please can you help! I'm new to Macro/VBA I have made the below and I would like to have this repeat on each row that has data in the spreadsheet.

VBA Code:
Range("F2").Select
    Selection.Copy
    Windows("Storage bin type calculator -Matt Copy.xlsm").Activate
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("17.11.2021 Matt - Do Not Delete.xlsm").Activate
    Range("G2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Storage bin type calculator -Matt Copy.xlsm").Activate
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("17.11.2021 Matt - Do Not Delete.xlsm").Activate
    Range("H2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Storage bin type calculator -Matt Copy.xlsm").Activate
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("17.11.2021 Matt - Do Not Delete.xlsm").Activate
    Range("L2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Storage bin type calculator -Matt Copy.xlsm").Activate
    Range("P7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("17.11.2021 Matt - Do Not Delete.xlsm").Activate
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L3").Select
End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try being more specific. What exactly do you want to happen? Be specific about which sheets/rows/etc.
 
Upvote 0
I like users telling me what their ultimate goal is with specific details and then I will write the script.

Showing me a script that you wrote but does not work is not much help.
 
Upvote 0
Hi, my apologies, I would to follow same movements but for it to go down a row in the "17.11.2021 Matt - Do Not Delete.xlsm" sheet. So in 17.11.2021 Matt - Do Not Delete.xlsm" sheet it copies from Cells F2, G2 & H2 and pastes into L2 & M2, I want this to repeat down my sheet so F3, G3 , H3 then L3 & M3 and keep going down. I want the cells I'm using in the "Storage bin type calculator -Matt Copy.xlsm" to remain the same though. Its basically a size calculator so it is copying measurement data from the "17.11.2021 Matt - Do Not Delete.xlsm" sheet and pasting it into the Storage bin type calculator -Matt Copy.xlsm" which gives me where it can fit and how many and I'm copying and pasting that data over to "17.11.2021 Matt - Do Not Delete.xlsm" sheet. i want it to repeat down the "17.11.2021 Matt - Do Not Delete.xlsm" sheet until all have size and how many next to them. Hope this makes sense, I really appreciate the reply. The script above works but I want it to repeat down each row in "17.11.2021 Matt - Do Not Delete.xlsm" sheet.
 
Last edited by a moderator:
Upvote 0
I asked for a ultimate goal. You showed a lot of selecting and activating and copying and pasting.

And you said:
copies from Cells F2, G2 & H2 and pastes into L2 & M2 so we are copying three ranges and pasting them into two ranges?? Is that what you really want?
 
Upvote 0
@My Aswer Is This, I think the OP got confused as to the objective. LOL

The following are the notes I made when looking at the code from the 1st post a few days ago:
' Step 1) Copy F2:H2 From "17.11.2021 Matt - Do Not Delete.xlsm" to C2:C4 "Storage bin type calculator -Matt Copy.xlsm"
' Step 2) Copy H7 From Storage bin type calculator -Matt Copy.xlsm to L2 "17.11.2021 Matt - Do Not Delete.xlsm"
' Step 3) Copy P7 From Storage bin type calculator -Matt Copy.xlsm to M2 "17.11.2021 Matt - Do Not Delete.xlsm"

So it sounds like Step 1 is desired to repeat down till last row of F2:H & Last row is copied from "17.11.2021 Matt - Do Not Delete.xlsm" to "Storage bin type calculator -Matt Copy.xlsm"

What is desired with Steps 2 & 3, I still am confused. Is that a one and done, or should that be continued somehow?
 
Upvote 0
Well I'm confused also.
Like the script discusses two different workbooks but does not mention either sheet name.

Just to say. When writing scripts you do not need to do a lot of selecting and activating.
 
Upvote 0
The lack of sheet names necessitates some of the 'activate'. I couldn't get around the 'activate' when dealing with a workbook without a sheet name.
 
Upvote 0
If you only want to copy the values a simple script like this would work.

Specify in your script exact workbook names and sheet names.
No need to activate or select.
Both Workbooks must be Open.

Now this is just a simple example of not needing to activate or using select.

VBA Code:
Sub Copy_Without_Selecting()
'Modified 11/30/2021  8:37:49 PM  EST
Workbooks("Book2").Sheets(1).Range("A1").Value = Workbooks("Book1").Sheets(2).Range("A6").Value
End Sub
 
Upvote 0
That code requires guessing the sheets when no sheets have been mentioned. I am aware how to do it when sheet names are known.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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