sheet to sheet macro

[John Caines]

Hello All.
I recently posted on the board asking for help with a export data macro from 1 sheet to another,,
my original post was here;
http://www.mrexcel.com/forum/showthread.php?p=2652665#post2652665
Called "1 Export /Import VBA Macro-Cannot Do"

No one has answered (Accept me once ,,I think maybe it seemed a bit longwinded in how I explained it all, I have a habit of doing this sometimes, or maybe it was a bit confusing,, not sure really,, I did try to be thorough.

So now, i have an idea to try and make the macro evolve,, IE just post for a part of it,, and see if I can add to it.
So here goes.
Only 2 things;
==========
1. I am looking to export 1 cell of data from a sheet called "Data Import"
Cell is F27 (Merged & Centered to G29),,
This has a £ amount and I would like this to go into a sheet on the same workbook called "Daily Targets",, Cell J23

2. In the sheet "Data Import" I have a range of cells from K36:K275.
These have £ amounts. I would like this also to be exported to the sheet called "Daily Targets"
From Cells J24:J274.
Here's the twist though,,,,Some cells in the J24:J274 range have to be blank,
these cells are;
J44,J65,J86,J107,J128,J149,J170,J191,J212,J233,J254
Then I can assign this VBA code to a macro button and export it from the "Data Import Sheet" to the "Daily Targets sheet"

That's it,, if anybody can help me with the above,, so I can get this macro up and running,, I would be very very grateful.
I would then try to add the other criteria in my original post later down the line.
Here's a download link for the original sheet,,,
http://dl.dropbox.com/u/16052166/MrExcel-Target-Sheet.zip
or sheet only
http://dl.dropbox.com/u/16052166/Daily Targets-MrExcel.xlsm
(So please ignore Steps 2a/2b & 2c,,, unless you think you can incorporate these also into the macro ,, worth asking,,,my mum told me always to ask,,, if you don't ask,, maybe you don't ,,,,

many thanks for all your time
Best regards
John Caines

 

[bertie]

By far the simplest way to do this would be to link the two worksheet using formula. And your problem begs the question; why do you need the exact same information in different worksheets of the same workbook?

Aside from that, a simple solution to your problem would be to copy and paste the full range into the Daily Targets worksheet. Then loop through the paste range and insert a blank cell every 21st row. But, I don't know how this would affect the rest of your worksheet.

So here is my attempt at a solution. There is an IF statement within a loop to check if we are dealing witht he 21st entry, if so then leave the cell blank. The else part of the if statement introduces a recursive element into the loop, i = i -1, so we maintain the correct output.

Make a copy of your workbook before testing this code.

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=darkblue]Dim[/COLOR] wsDaily [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsData [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsDaily = Worksheets("Daily Targets")
   [COLOR=darkblue]Set[/COLOR] wsData = Worksheets("Data Import")
 
   [COLOR=green]'copy merged cell[/COLOR]
   wsDaily.Range("J23").Value = wsData.Range("F27").Value
 
   [COLOR=green]'loop and leave every 21st row blank[/COLOR]
   rw = 23
   [COLOR=darkblue]For[/COLOR] i = 36 [COLOR=darkblue]To[/COLOR] 275
      rw = rw + 1
      counter = counter + 1
      [COLOR=darkblue]If[/COLOR] counter Mod 21 <> 0 [COLOR=darkblue]Then[/COLOR]
         wsDaily.Range("J" & rw).Value = wsData.Range("K" & i).Value
      [COLOR=darkblue]Else[/COLOR]
         wsDaily.Range("J" & rw).Value = ""
        [COLOR=red] i = i - 1[/COLOR]   [COLOR=green]'recursive loop every 21st iteration[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
 
   [COLOR=darkblue]Set[/COLOR] wsData = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsDaily = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[John Caines]

Bertie!!!!!!!!!!!!
It works!!!!

Great stuff,, excellent,, really great,,,
Many thanks for this,,,, just tried it,,, really really brilliant.

Regarding your question,, about why have the same data on 2 sheets,,,
here's my explanation as to why Bertie,,,,,

What the "Data Import" sheet is for: if someone has created 240 £ amount targets they can paste them straight into this "Data Import" Sheet (as all the rows are together,, so they might copy it out of a text file,, or continuous excel column,,,then they would just click Your macro Bertie,,, and it will be exported into the "Daily Targets" sheet.
Once this is done,,, This "Data Import" Sheet can could now be cleared.
See Bertie,,, the problem I foresaw was from how I formatted the cells,, IE having Blank rows in Column J of the "Daily Targets" sheet,, so no one could just copy & paste straight into it.
Through my formatting,, I've created a nice looking spreadsheet,, but a pig for importing data easily

Again,, many thanks for this Bertie,,,,
It works great,,,
Whilst I don't fully understand your formula as VBA is still quite alien to me,,I can kind of get what's happening.

Again Many Thanks Bertie,,
Maybe I can get Step 2a "2b & 2c now working somehow within your formula.
What these are,, are choices for data to be exported.
Someone Can choose only 1. It was all explained in my original post here;
http://www.mrexcel.com/forum/showthread.php?p=2652665#post2652665

Still,, This is Great stuff Bertie.
2dWorking
1 down,,, 3 to go!!!!!

All the best Bertie.
A very very grateful
John Caines

 

[John Caines]

Sorry Bertie, to add,
Is there a way to include a "Fail Safe" within your formula.
IE,, Maybe someone forgot to enter an amount in the range K36 To K275,, IE they might have missed 1 cell,, or even forgot to enter the starting equity in cell F27.

Could some pop up appear and say something like,,,
"Hey you haven't entered your Starting Equity In Cell F27"
or/all
"Hey, You've not entered 3 Days of your Daily Gain £ Amounts, Day 56 Day 89 & Day 147 are missing! try again please"

Just a thought,, but someone could copy and past it wrong,, it does happen.
Only an idea Bertie,, not sure how complex it is to do.
If it's too difficult,, no worries at all.

many thanks again Bertie.
John Caines

 

[bertie]

I have put a test routine in place, see "Test Data is Complete". I hope this suffices.

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=darkblue]Dim[/COLOR] wsDaily [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsData [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] dataTest [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
[COLOR=red]  '======================[/COLOR]
[COLOR=red]  'test data is complete[/COLOR]
[COLOR=red]  '======================[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Data Import")
      dataTest = Application.WorksheetFunction.CountBlank(Range("K36:K275"))
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=darkblue]If[/COLOR] Sheets("Data Import").Range("F27") = "" [COLOR=darkblue]Then[/COLOR]
      MsgBox "Enter Principle Amount in F27"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]ElseIf[/COLOR] dataTest > 0 [COLOR=darkblue]Then[/COLOR]
      MsgBox dataTest & ": Data Entry(s) missing!"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
   [COLOR=green]'=======================[/COLOR]
   [COLOR=green]'export to other sheet[/COLOR]
   [COLOR=green]'=======================[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsDaily = Worksheets("Daily Targets")
   [COLOR=darkblue]Set[/COLOR] wsData = Worksheets("Data Import")
 
   [COLOR=green]'copy merged cell[/COLOR]
   wsDaily.Range("J23").Value = wsData.Range("F27").Value
 
   [COLOR=green]'loop and leave every 21st row blank[/COLOR]
   rw = 23
   [COLOR=darkblue]For[/COLOR] i = 36 [COLOR=darkblue]To[/COLOR] 275
      rw = rw + 1
      counter = counter + 1
      [COLOR=darkblue]If[/COLOR] counter Mod 21 <> 0 [COLOR=darkblue]Then[/COLOR]
         wsDaily.Range("J" & rw).Value = wsData.Range("K" & i).Value
      [COLOR=darkblue]Else[/COLOR]
         wsDaily.Range("J" & rw).Value = ""
         i = i - 1   [COLOR=green]'recursive loop every 21st item[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
 
   [COLOR=darkblue]Set[/COLOR] wsData = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsDaily = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I also had a look at your earlier thread. try these.

[COLOR=darkblue]Sub[/COLOR] PercentIncrease()
   [COLOR=green]'======================[/COLOR]
   [COLOR=green]'2a Percentage Increase[/COLOR]
   [COLOR=green]'======================[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] X [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] Principle [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
 
   [COLOR=darkblue]With[/COLOR] Sheets("Data Import")
      X = .Range("K15").Value
      Principle = .Range("F27").Value
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] i = 36 [COLOR=darkblue]To[/COLOR] 275
      Principle = Principle + Principle * X
      Sheets("Data Import").Range("K" & i).Value = Principle
   [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Sub[/COLOR] FixedIncrease()
   [COLOR=green]'====================[/COLOR]
   [COLOR=green]'2b Fixed Increase[/COLOR]
   [COLOR=green]'====================[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] Principle [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] X [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
 
   [COLOR=darkblue]With[/COLOR] Sheets("Data Import")
      X = .Range("K20").Value
      Principle = .Range("F27").Value
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] i = 36 [COLOR=darkblue]To[/COLOR] 275
      Principle = Principle + X
      Sheets("Data Import").Range("K" & i).Value = Principle
   [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Sub YearEndIncrease()
   [COLOR=green]'===============================[/COLOR]
   [COLOR=green]'2c End of Year Increase[/COLOR]
   [COLOR=green]'================================[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] Principle [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] X [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
 
   [COLOR=darkblue]With[/COLOR] Sheets("Data Import")
      Principle = .Range("F27").Value
 
      [COLOR=green]'this will give 50k, 40k profit + 10k Principle[/COLOR]
      X = (.Range("K25").Value - Principle) / 240
 
      [COLOR=green]'this will give 60k = 50k profit + 10k Principle[/COLOR]
      [COLOR=green]'X = .Range("K25").Value / 240[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] i = 36 [COLOR=darkblue]To[/COLOR] 275
      Principle = Principle + X
      Sheets("Data Import").Range("K" & i).Value = Principle
   [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] Sub

So that's me for the night. Australia has been phoned and I'm off to bed.

 

[John Caines]

Hello All.
Many thanks again Bertie for helping me out here,, I want to add a reply, but I just hope I don't confuse anything.
Here goes.
1st,, here's my formula now (Your formula Bertie, slightly amended).

Sub Import()
   Dim wsDaily As Worksheet
   Dim wsData As Worksheet
   Dim rw As Long
   Dim i As Long
   Dim counter As Long
   Dim dataTest As Long
 
  '======================
  'test data is complete
  '======================
   With Sheets("Data Import")
      dataTest = Application.WorksheetFunction.CountBlank(Range("K36:K275"))
   End With
 
   If Sheets("Data Import").Range("F27") = "" Then
      MsgBox "Enter Principle Amount in F27"
      Exit Sub
   ElseIf dataTest > 0 Then
      MsgBox dataTest & ": Data Entry(s) missing!"
      Exit Sub
   End If
 
   '=======================
   'export to other sheet
   '=======================
   Set wsDaily = Worksheets("Daily Targets")
   Set wsData = Worksheets("Data Import")
 
   'copy merged cell
   wsDaily.Range("J23").Value = wsData.Range("F27").Value
 
   'loop and leave every 21st row blank
   rw = 23
   For i = 36 To 275
      rw = rw + 1
      counter = counter + 1
      If counter Mod 21 <> 0 Then
         wsDaily.Range("K" & rw).Value = wsData.Range("K" & i).Value
      Else
         wsDaily.Range("K" & rw).Value = ""
         i = i - 1   'recursive loop every 21st item
      End If
   Next i
 
   Set wsData = Nothing
   Set wsDaily = Nothing
End Sub

Basically now Data is imported in the sheet "Daily Targets" Column K & not J as before.
But The starting amount is still exported to cell J23 of the daily targets sheet.

As to the other 3 macros Bertie,,, many thanks for these,,, but can they be worked into the formula above? as they need to export the data straight into the same column range K of the "Daily Targets" sheet,, and not into the Daily Gain Amount column K of the "Data Import" sheet.
1st,, regarding the 3 formulas
Sub PercentIncrease()
Sub FixedIncrease()
Sub YearEndIncrease()

All these 3 export data into the same sheet ("Data import" sheet K36:K275),,, when what they really need to be doing is exporting to the sheet "Daily Targets" K24:K274,,, just like the "Sub Import" code above does.

Regarding the Sub PercentIncrease()
If the start equity was £10,000 & the increase entered into cell K15 was 0.5%,,, the 1st data returned shows an amount of £10,050.00,,, could the macro be written so it returns £50,, So day 2's amount shows £10,100.25,, could it be re written so it shows £100.25
Then,, all these 240 daily gains would go straight into the sheet "Daily Targets" Column K (K24:K274), as the above Sub Import code does.

Regarding the Sub FixedIncrease()
This returns results again in the "Data Import" sheet,, but could it send the data as above,, to the "Daily Targets" sheet Column K23:K274. Also,,, at the moment if the start equity was £10,000 (In F27) day 1 shows £10,100 Day 2 shows £10,200.
Could it just show in EVERY cell the £100 and insert these into the range K23:K274 of the "Daily Targets" sheet,,, as these are the fixed daily gains,, and now (because I've reformulated my "Daily Targets" sheet) Column J has formulas inserted to calculate the increases.
So,,, £100 entered 240 times (1 for each daily gain)

Regarding the Sub YearEndIncrease()
Again,, if the returned data can go straight to the range K23:K274 of the "Daily Targets" sheet please as the formula is now written, If you inserted an amount of £50,000 into cell K25 of the "Data Import" sheet, Day 1 shows £10,166.67 & Day 2 shows £10,333.33 (This is with a starting equity of £10,000 in Cell F27)
Could it be rewritten so only the £166.67 shows for day 1 & £333.33 shows for day 2... then all these can be exported straight to the "daily Targets Sheet again, Column range K23:K274.

& as if that isn't enough ,, a possible fail safe in the code,,, so if someone has entered data in more than 1 step,,
an error message would appear,, saying something like, "Sorry, you must only use 1 step for data import!"

I hope all the above makes sense.
I've created a test sheet here;
http://dl.dropbox.com/u/16052166/Daily_Targets_Mr_Excel_v2.xlsm

I hope I haven't made this too confusing.
Hopefully the test sheet will make it all understandable.

Many thanks for all your time,,
and thanks again Bertie,,
Greatly appreciated, it really is great stuff
All the best
John Caines