Copy static location, paste dynamic location

[bencmu]

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

 

[Smitty]

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]

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