Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...repeat till table complete

Question

Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...repeat till table complete

Christine Potter 0

Hey smart people! I'm a novice at macros and have found myself in a little too deep to handle this task with a macro and don't know VBA language to get this done.

I have a spreadsheet with multiple sheets. My macro needs to:

Select the main sheet.
Make task relative.
drop down 1 line
Insert 4 lines
Copy the data from one cell above to the same cells in those 4 new lines
Copy images (https://...) from Other Sheet over to the main sheet to those new lines into the appropriate cell of the newly inserted lines. Those images are on the one row in 4 cells (4 columns) in the Other Sheet and need to be transposed to the one cell (1 column) in the next 4 lines down and just as values (not formulas).
then go down to the next line item to set up for the next repetitive motion till all items in the Main Sheet table are completed.

As data is input differently from the month before, the Main Sheet and Other Sheet will be different in number of filled rows each time so I need to make the macro stop after it can't find any more data to alter.

Can anyone help steer me to the best way to conquer this, please?

2 answers

Your answer

Answer 1

Barry Schwarz 3,746

Turn on Macro recording. Perform tasks 1-6 manually. Turn off macro recording.

Study the resulting macro to learn at least some VBA basics,

Post the macro and tell us specifically what data task 7 should work on and how it can determine all items are complete. It wouldn't hurt to show us before and after sample data either.

Christine Potter 0 Reputation points

2024-09-18T15:43:11.3633333+00:00

Thank you so much for taking the time to help me problem solve my macros. The first 5 steps seem to work fine until (in the macro) Step 6 will not allow new copied data (it holds the previous copied data in memory and pastes into next run of the macro).
Barry Schwarz 3,746 Reputation points

2024-09-18T20:59:44.21+00:00

Is the problem on line 46 of the macro? Seriously, if you need help with a macro you need to show it to us. It would also help if you told us what you did to record this part of the macro.
Christine Potter 0 Reputation points

2024-09-19T19:33:31.55+00:00

okay...sorry...I'm new to this communication...and, of course, I realize you need to see the macro. I didn't know you could attach a file (although it is a customer file that I don't really want to put out on an open forum).
Barry Schwarz 3,746 Reputation points

2024-09-19T22:47:06.31+00:00

You can copy and paste the macro into your message.

Christine Potter 0

Sub lines()

'

' lines Macro

'

ActiveCell.Offset(-1, 0).Range("Table4[[#Headers],[Handle]]").Select

Sheets("Shopify HARD INPUT").Select

ActiveCell.Offset(1, 0).Rows("1:4").EntireRow.Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

ActiveCell.Offset(-1, 0).Range("Table4[[#Headers],[Handle]]").Select

Selection.Copy

ActiveCell.Offset(1, 0).Range("A1:A4").Select

ActiveSheet.Paste

ActiveCell.Offset(0, 1).Range("Table4[[#Headers],[Handle]]").Select

Sheets("VHC Brands altered").Select

ActiveCell.Offset(-25, 4).Range("A1").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Shopify HARD INPUT").Select

ActiveSheet.Paste

ActiveCell.Offset(1, 0).Range("Table4[[#Headers],[Handle]]").Select

Sheets("VHC Brands altered").Select

ActiveCell.Offset(0, 1).Range("A1").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Shopify HARD INPUT").Select

ActiveSheet.Paste

ActiveCell.Offset(1, 0).Range("Table4[[#Headers],[Handle]]").Select

Sheets("VHC Brands altered").Select

ActiveCell.Offset(0, 1).Range("A1").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Shopify HARD INPUT").Select

ActiveSheet.Paste

ActiveCell.Offset(1, 0).Range("Table4[[#Headers],[Handle]]").Select

Sheets("VHC Brands altered").Select

ActiveCell.Offset(0, 1).Range("A1").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Shopify HARD INPUT").Select

ActiveSheet.Paste

ActiveCell.Offset(2, -1).Range("Table4[[#Headers],[Handle]]").Select

End Sub

Barry Schwarz 3,746 Reputation points

2024-09-23T19:15:20.3433333+00:00

We don't need to see the customer's data but we do need some information. We can see the two worksheet names. We can also see you recorded the macro with relative references.

But we don't know which sheet is active and which cells were active on each sheet when you started the macro. At one point you have an offset of -25 rows which tells us that there is a lot of significant data above the starting point on the VHC sheet. We don't need the actual data but its dimensions would help.

We also know nothing about Table 4. Which sheet it is on, the number of rows and columns, and which column contains the header Handle would help.

What sequence of steps did you take to record the first line of the macro?

Answer 2

Christine Potter 0

I was able to build 2 semi-simple macros to get the job done. I've never built macros before and always relied on formulas...until I realized that you can't make functions work in formulas. The need to assist an acquaintance helped to inspire me to figure it out on my own and finding a cool tutorial online. https://youtu.be/33Ej8DPsdvA?si=ykFX9GW2mI6nzIMQ

Share via

Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...repeat till table complete

2 answers

Your answer