• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to copy range based on Month (--VBA noob) [SOLVED]

J

jason

Member
First off, I am very new; a 'newborn/infant' if you must, when it comes to VBA. I can accomplish the simplest of things to existing code, but do not know enough to write my own lines. so your help is greatly appreciated!!


so heres what I'm doing/needing:

i have a report that is ran each month. the data is extracted and pasted into a sheet where we track the data from all months. I recorded a macro that will copy a range of cells from the specific sheet and paste them into the desired location (i technically have a sheet for each department and have multiple departments). here is the first part my code: again, this will copy the data from the specified departments sheet and paste it in the needed location on our summary sheet... these lines are repeated for each department.


Sub pastepvc()

'

' pastepvc Macro

'


'

ActiveWindow.ScrollWorkbookTabs Position:=xlLast

ActiveWindow.ScrollWorkbookTabs Position:=xlLast

Sheets("PVC").Select

Range("C5:N21").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

Sheets("OPS").Select

Range("P197").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub


----------------------------------------------------------------------------------


Now what i'm trying to do:

Our main report that is used to extract the raw data is ran on a montly basis and because of this when Feb. report is generated we will see the Feb. numbers, but when March is generated, the PivotTable being used will no longer contain Feb info (we cant run this particular report Year-to-Date due to the size; we'd only fit a couple of months on a sheet.). as you can see from the code, that will copy a set range of cells.

HOWEVER, it would be better if I could have it copy only the current months information and paste it in the current month column on our summary sheet.


sorry for the long read!!! hope that makes sense! ask any questions you have and please, please, please be gentle on me! hahah


THANK YOU TO EVERYONE FOR READING AND ESPECIALLY FOR HELPING!!
 
Hi jason!


I think we can do what you want, but we're going to need some more info to write the code exactly. First, just as a reference, here's your above code, simplified down:

[pre]
Code: 
Sub pastepvc()

Sheets("PVC").Range("C5:N21").Copy
Sheets("OPS").Range("P197").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
[/pre]
We can clearly see that we're just copying one range and pasting to another. Now, several questions we need answers to:

1. Are we always dealing with these 2 worksheets?

2. How do we know where the current month's data is on source sheet? Are there column/row headers we can use?

3. Similar to #2, how do we know where to paste the data in the destination sheet?

4. You stated "paste in the current month column" but your code is copying more than a single column's worth of data. Are we just doing col C (or some other col), or was this just a typo?


Other option is to load a sample workbook. Benefit is that it would help someone here be able to answer these questions quickly and write a perfectly customized solution. Negative is that I can't access files from my location, so I wouldn't be able to help. But maybe you want that. =P
 
i figured it would look a lot like that cleaned up!! thanks, Luke!


1. no, i'm copying from 5+ sheets. so right now i have the code i posted above repeating 5 times, each time with a diff sheet referenced in the first line.

2. Jan has "01" above it, Feb has "02" and so on

3. same format is setup on destination sheet (if that work at all)

4. ideally.... the macro would only need to copy the particular column (for said month) from each of the sheet and paste in the current locaiton.

for example: it would go to C5:c25 and copy the range because that is where the march data is located (d5:d25 would contain April's). then it would paste this info in a specified location on my OPS (summary)sheet.


-- due to work restrictions i cant access sites where i can upload a sample. :( you are 'down the road', do you make service calls!? hahahahaha (does Chandoo.org have an excel ninja delivery number i can call?! HAHAHAHAHA) maybe, i can get ya lunch?
 
Not sure this gets you all the way, but might give you some insight as to how to get there.

[pre]
Code: 
MyMonth = Month(Date)

'Magic happens here!
'go to fifth row, in the column representing our month
'We then resize the selection we're looking at to be 21 rows high, 1 cell wide
'so, if today is April, we start at D5 and then grab d5:d21
'If 1st month is not in 1st column, we could add a constant to the
'value to get correct offset
Sheets("PVC").Cells(5, MyMonth).Resize(21, 1).Copy

'I don't have enough info to know exactly where to paste to...
'perhaps something like this?
Sheets("OPS").Cells(197, 16 + MyMonth).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
[/pre]
 
Luke i understand the difficulty...


For march i would want the following to be done.

-go to 'PVC' Sheet copy E5:E21 (because E4 contains "03" for March)

-go to 'OPS' sheet and paste in R197:R213 (because R7 contains "03")

...

this would happen for each "department": go to ____ sheet, copy E5:E21, paste on OPS sheet but the location would change obviously


so for April:

-go to 'PVC' Sheet copy F5:F21 (because F4 contains "04" for April)

-go to 'OPS' sheet and paste in S197:S213 (because S7 contains "04" for April)


does that help!?
 
does that help at all?!


not to make this harder.... but it may


instead of doing an automatic 'current month' how hard would it be to let the user enter the 2 digit month they want to copy from? 0:)
 
How do you know where on the OPS sheet each individual dept's data should go? We know currently how to find the correct column, but there's got to be some difference in row...

Just for reference later, this is what I've got so far:

[pre]
Code: 
Sub CopyData()
Dim MyMonth As Integer

'Automatically find month
MyMonth = Month(Date)

'Alternatively, user can input
'MyMonth = InputBox("What month do you want (1-12)?")
If MyMonth < 1 Or MyMonth > 12 Then
MsgBox "You didn't follow instructions"
Exit Sub
End If

Sheets("PVC").Cells(5, MyMonth + 2).Resize(17, 1).Copy
Sheets("OPS").Cells(197, 15 + MyMonth).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub
[/pre]
 
i may not be answering your question:


but just like the sheets that contain the data im copying have the 01,02,03,...12 a couple rows above the actual data, so does the destination sheet... the main difference is the source sheets all share the same layout/cell range;just on different sheets - however, on the destination (OPS Sheet) the rows for each department will vary. (if that makes sense lol)


Example:


goto PVC sheet 04 (for April) is located in F3, the data that i need copied is in F5:F21. ((This crange will be the same throughout the other sheets as well when copying)).

goto OPS sheet. 04 is in S7, however the cells that the copied data needs to go in vary based on department: PVC=S198:S214, Print=S382:s398, and so on.
 
PVC=S198:S214, Print=S382:s398, and so on.
Click to expand...

This part is what we need to know. How will the macro know what row to paste to? Is the name of the departments listed somewhere, or do you want to hardcode all of them?
 
i dont expect the location (rows) to change, only the cols (as months change)... hardcoding would suffice...i think


but to answer your question, yes, there are 'department names' listed at the beginning of each departments data on the OPS sheet.
 
holy moly!! i think we have something!!!!!!! only testing on the pvc sheet at the moment, but it appears to be only copying the month that i specify!!!!!!!!


eeeeeekk!


but first... can you explain these lines/numbers (mainly the Cells( ) & Resize( )parts. not 100% sure i understand what/how to change them for the other sheets.

Sheets("PVC").Cells(5, MyMonth + 2).Resize(17, 1).Copy

Sheets("OPS").Cells(198, 15 + MyMonth)
 
Sure thing.

The cells method takes 2 arguements, a row and a column count. In the first line, I'm telling it to look at the 5th row, and starting in the column equal to month specified + 2 (the +2 is dependent on your workbook layout). If January was in col B, it would be +1, if it was col C, it's +2, and so on.

A similar thing is used in 2nd line for Ops sheet. We look at row 198, and then look in a variable column.


Going back to first line, once we find the specific cell of interest, we need to actually grab 21 cells (the range you originally gave me). So, the resize method also has 2 arguments, row size and column size. Since they're both positive, we're starting with current cell (found with the Cells method) and then changing the size of our select to be 21 cells down and 1 cell wide. E.g., if we did

Code: 
Range("A2").Resize(2,3).Select

This will grab the range A2:C3 (2 rows down, 3 columns wide).


Does that help?
 
LUKE!!! I finally had a few minutes to sit down with this and guess what!?! Thanks to your breakdown I was able to adapt it to work for the rest of the departments!! (It helped greatly once I remembered that I deleted a col. on some of the sheets but not all of them.... onnce that was fixed they all worked!!!)


thanks a ton!!!


I may definately owe you a lunch or something!
 
You must log in or register to reply here.
Back
Top