Copy static location, paste dynamic location

bencmu

New Member
Joined
Oct 6, 2006
Messages
2
This probably has been asked before, but everything I search for gives me a ton of results.

I have a lot of experience with excel, but I'm just starting to play with macros. So, I don’t really know how a lot of the functions work yet.

I have a spreadsheet with a bunch of information that is automatically updated daily. I have a worksheet set up to summarize all of that information. What I'm trying to do is take the totals from the summary worksheet (that will always be in the same location) and copy that information to another worksheet that tracks the daily totals.

So, the problem that I'm running into is making my macro paste the new totals below the previous day's totals. I understand how to use the offset function for relative references. I just can't figure out how to get back to a different row every time it runs.

Is there a way to make it come back to the row that the cursor is starting at after I make my copy? Because I could easily just make it move to the correct starting cell for the next time at the end of the macro.

Right now, I have it set up to copy the static cells that I want, and I used the relative reference to get back to the columns that I want. But, it is just going to paste over the same row every time. Hopefully, I am missing a way to make it do a relative reference based on the starting location instead of the current location, because I have a lot of other similar uses for macros like this.

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

This is relatively easy & painless. Here's a broad-based example of copying Row 1, Sheet1 to a dynamic range on Sheet2:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> CopyData()
    Sheets("Sheet1").Rows("1:1").Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End <SPAN style="color:#00007F">Sub</SPAN></FONT>

Just change the sheet and range references to suit.

Hope that helps,

Smitty
 

bencmu

New Member
Joined
Oct 6, 2006
Messages
2
Thanks. That is a lot more compact of a formula than what I have been trying to do. It worked, but it actually made me more confused. Looking at that compared to what I've been working on, I can't follow how that would even work.

I've been trying to tweak it so that it will do things a little differently. I need to select and copy several different cell ranges instead of just a whole row. I can just set up my sheet to paste a link to everything on one row and copy the row to make it work, but that doesn't seem like the best way. I also want to just paste the value so that it doesn't mess up my formatting.

I can't see how to make these changes in the 1 line formula. And, I haven't had any luck splitting your formula into multiple lines and keeping it working. I've been playing around with trying to use an "If, Then" statement to drop down a row if there is already something in the top row. I've tried several different ways to word it, but so far I either get an error or it just pastes in the same spot as the last time. Here is my test macro:

Range("S12:AI12").Select
Selection.Copy
ActiveCell.Offset(-7, 0).Range("A1").Select
If ActiveCell.Value > 0 Then
RowOffset = (1)
End If
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I assume that either (or both) of "ActiveCell.Value" or "RowOffset" are not the correct terms. But, nothing I've tried has worked.

Also, can anyone recommend a good book to use as a reference for excel macros? I really hate bugging people with stuff like this instead of learning it on my own.

Thanks
 

Forum statistics

Threads
1,136,430
Messages
5,675,803
Members
419,586
Latest member
RoteichA

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
Top