VBA Code Assistance

%3CLINGO-SUB%20id%3D%22lingo-sub-1730620%22%20slang%3D%22en-US%22%3EVBA%20Code%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1730620%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%20community%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20please%20require%20assistance.%3C%2FP%3E%3CP%3EPlease%20see%20my%20VBA%20code%20below%3C%2FP%3E%3CP%3EPlease%20see%20below%20what%20the%20Macro%20should%20do%3A%3C%2FP%3E%3CP%3EStep1-%20Go%20to%20the%20last%20active%20Cell%20in%20Column%20A%3C%2FP%3E%3CP%3EStep%202%20-%20Move%20to%20the%20corresponding%20cell%20in%20column%20B%3C%2FP%3E%3CP%3EStep%203%20-%20Go%20the%20last%20active%20cell%20in%20column%20B%3C%2FP%3E%3CP%3EStep%204%20-%20Copy%20the%20active%20cell%26nbsp%3B%3C%2FP%3E%3CP%3EStep%205%20-%20Paste%20the%20copied%20detail%20into%20column%20B%20until%20the%20last%20active%20cell%20in%20column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20is%20dynamic%20and%20the%20positioning%20of%20the%20active%20cells%20will%20change%20freqeuntly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20a%20copy%20of%20my%20tried%20macro%20below%2C%20however%20it%20does%20not%20provide%20me%20with%20the%20exact%20results%20as%20I%20would%20want%20it.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E1)Range(%22A1%22).End(xlDown).Select%3CBR%20%2F%3E%3CSTRONG%3E2)Code%20to%20move%20to%20right%20or%20left%3C%2FSTRONG%3E%3CBR%20%2F%3E3)Selection.End(xlUp).Select%3CBR%20%2F%3E4)Selection.Copy%3CBR%20%2F%3E5)Range(%22B1%3AB6%22).Select%3CBR%20%2F%3E6)ActiveSheet.Paste%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJody%20Barnabas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1730620%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1731066%22%20slang%3D%22de-DE%22%3ESubject%3A%20VBA%20Code%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1731066%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760420%22%20target%3D%22_blank%22%3E%40jbbarnabas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EMaybe%20I%20can%20help%20you%20with%20this%20food%20for%20thought%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20active_cell()%0ADim%20AC%2C%20AktRange%0AAC%20%3D%20ActiveSheet.Name%0AAktRange%20%3D%20ActiveCell.Address%0ASelection.EntireRow.Copy%0ASheets(%22Sheet%20B%22).Select%0ACells(1%2C%201).Activate%0AActiveSheet.Paste%0ASheets(AC).Select%0ARange(AktRange).Select%0AEnd%20Sub%0A%0A'Untested%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1731512%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1731512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760420%22%20target%3D%22_blank%22%3E%40jbbarnabas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20still%20not%20sure%20I%20understand%20what%20you're%20trying%20to%20do.%20By%20%22active%20cell%2C%22%20I%20assume%20you%20mean%20non-empty%20cell%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20way%20to%20refer%20to%20other%20cells%20relative%20to%20a%20specific%20cell's%20location%20is%20to%20use%20the%20offset%20method%20of%20the%20range%20object.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20want%20to%20find%20the%20bottom%20of%20Col%20A%2C%20then%20go%20up%20to%20find%20the%20bottom%20of%20Col%20B%20and%20if%20that%20cell%20in%20Col%20B%20is%20empty%2C%20go%20up%20and%20copy%20the%20last%20non-empty%20cell%20in%20Col%20B%20down%20to%20the%20same%20row%20as%20the%20bottom%20of%20Col%20A%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20FillColB()%0A%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20With%20Range(%22A1%22).End(xlDown).Offset(0%2C%201)%0A%20%20%20%20%20%20%20%20%20%20If%20.Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.End(xlUp).Copy%20Range(.End(xlUp)%2C%20.Offset(0%2C%200))%0A%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20End%20With%0A%20%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1731711%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1731711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760420%22%20target%3D%22_blank%22%3E%40jbbarnabas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20helps.%20Thanks%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThisWorkbook.Sheets(%22Sheet1%22).Activate%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elastrow%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%3C%2FP%3E%3CP%3EFor%20i%20%3D%201%20To%20lastrow%3C%2FP%3E%3CP%3ERange(%22A%22%20%26amp%3B%20i).Select%3CBR%20%2F%3ESelection.Copy%3C%2FP%3E%3CP%3ERange(%22B%22%20%26amp%3B%20i).Select%3CBR%20%2F%3EActiveSheet.Paste%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENext%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1733305%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1733305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%20thanks%20for%20the%20idea.%3C%2FP%3E%3CP%3EI%60ll%20check%20it%20out%20to%20see%20if%20it%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good day community

 

I please require assistance.

Please see my VBA code below

Please see below what the Macro should do:

Step1- Go to the last active Cell in Column A

Step 2 - Move to the corresponding cell in column B

Step 3 - Go the last active cell in column B

Step 4 - Copy the active cell 

Step 5 - Paste the copied detail into column B until the last active cell in column A.

 

The data is dynamic and the positioning of the active cells will change freqeuntly.

 

See a copy of my tried macro below, however it does not provide me with the exact results as I would want it.


1)Range("A1").End(xlDown).Select
2)Code to move to right or left
3)Selection.End(xlUp).Select
4)Selection.Copy
5)Range("B1:B6").Select
6)ActiveSheet.Paste
End Sub

 

Thank you.

 

Jody Barnabas

7 Replies

@jbbarnabas 

 

Maybe I can help you with this food for thought

 

Sub active_cell()
Dim AC, AktRange
AC = ActiveSheet.Name
AktRange = ActiveCell.Address
Selection.EntireRow.Copy
Sheets("Sheet B").Select
Cells(1, 1).Activate
ActiveSheet.Paste
Sheets(AC).Select
Range(AktRange).Select
End Sub

'Untested

 

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

@jbbarnabas 

 

I'm still not sure I understand what you're trying to do. By "active cell," I assume you mean non-empty cell?

 

One way to refer to other cells relative to a specific cell's location is to use the offset method of the range object.

 

I think you want to find the bottom of Col A, then go up to find the bottom of Col B and if that cell in Col B is empty, go up and copy the last non-empty cell in Col B down to the same row as the bottom of Col A?

 

Sub FillColB()
          
     With Range("A1").End(xlDown).Offset(0, 1)
          If .Value = "" Then
               .End(xlUp).Copy Range(.End(xlUp), .Offset(0, 0))
          End If
     End With
     
End Sub

@jbbarnabas 

 

I hope this helps. Thanks !

 

ThisWorkbook.Sheets("Sheet1").Activate

 

lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastrow

Range("A" & i).Select
Selection.Copy

Range("B" & i).Select
ActiveSheet.Paste


Next

@JMB17  thanks for the idea.

I`ll check it out to see if it works.

 

@lifeofgauravthank you , will check it out to see if it works as i intend it to.

 

@Nikolinothanks, will check it out to see if it works.

@jbbarnabas 

You may try something like this...

Sub CopyPaste()
Dim lr  As Long

lr = Cells(Rows.Count, "A").End(xlUp).Row

'Copy last non-empty cell in column A
'and paste it in column B
Range("A" & lr).Copy Range("B1:B" & lr)
End Sub