Copy a column and paste to another tab to save the data for each m
 

How can I copy a column B10:B70 a spread sheet have 60 rows of calculation
and I want to I click save button it will copy and paste just a value to
separate tab name Estimate for each month . It need to check on cell B6 if it
a month 1 or 2 or 3....to 12 so it will copy and paste data to a correct
column for each month.

thanks
Liem Pho

Copy a column and paste to another tab to save the data for each m
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"liem" wrote in message
...
How can I copy a column B10:B70 a spread sheet have 60 rows of calculation
and I want to I click save button it will copy and paste just a value to
separate tab name Estimate for each month . It need to check on cell B6 if
it
a month 1 or 2 or 3....to 12 so it will copy and paste data to a correct
column for each month.

thanks
Liem Pho

Copy a column and paste to another tab to save the data for each m
 

Option Explicit

Sub CopyColHtoEstimatesSAS()
Dim ms As String
Dim dc As Long

'line below ONLY needed if firing from another sheet
'Sheets("Merchandise Store Plan").Select

ms = Format(Range("sourcemonth"), "mmm")
'MsgBox ms
With Sheets("Estimates")
'finds appropriate column
dc = .Rows(5).Find(What:=ms, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
'MsgBox dc
Range("h13:h70").Copy
Sheets("Estimates").Cells(6, dc).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False
MsgBox "You just copied " & ms & " to " & ms & " of the Estimates Page"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"liem" wrote in message
...
How can I copy a column B10:B70 a spread sheet have 60 rows of calculation
and I want to I click save button it will copy and paste just a value to
separate tab name Estimate for each month . It need to check on cell B6 if
it
a month 1 or 2 or 3....to 12 so it will copy and paste data to a correct
column for each month.

thanks
Liem Pho

Copy a column and paste to another tab to save the data for ea
 

Thank you very much for your help and quick responsed. It work base on the
month change .
--
thanks

Liem Pho


"Don Guillett" wrote:

Option Explicit

Sub CopyColHtoEstimatesSAS()
Dim ms As String
Dim dc As Long

'line below ONLY needed if firing from another sheet
'Sheets("Merchandise Store Plan").Select

ms = Format(Range("sourcemonth"), "mmm")
'MsgBox ms
With Sheets("Estimates")
'finds appropriate column
dc = .Rows(5).Find(What:=ms, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
'MsgBox dc
Range("h13:h70").Copy
Sheets("Estimates").Cells(6, dc).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False
MsgBox "You just copied " & ms & " to " & ms & " of the Estimates Page"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"liem" wrote in message
...
How can I copy a column B10:B70 a spread sheet have 60 rows of calculation
and I want to I click save button it will copy and paste just a value to
separate tab name Estimate for each month . It need to check on cell B6 if
it
a month 1 or 2 or 3....to 12 so it will copy and paste data to a correct
column for each month.

thanks
Liem Pho

.